Come ottimizzare slow query con join

La mia situazione:

  • la query di ricerca circa 90.000 veicoli
  • la query richiede molto tempo, ogni volta che
  • Ho già indici su tutti i campi che devono essere Uniti.

Come posso ottimizzare?

Questa è la query:

SELECT vehicles.make_id,
       vehicles.fuel_id,
       vehicles.body_id,
       vehicles.transmission_id,
       vehicles.colour_id,
       vehicles.mileage,
       vehicles.vehicle_year,
       vehicles.engine_size,
       vehicles.trade_or_private,
       vehicles.doors,
       vehicles.model_id,
       Round(3959 * Acos(Cos(Radians(51.465436)) *
                         Cos(Radians(vehicles.gps_lat)) *
                                           Cos(
                                           Radians(vehicles.gps_lon) - Radians(
                                           -0.296482)) +
                               Sin(
                                      Radians(51.465436)) * Sin(
                               Radians(vehicles.gps_lat)))) AS distance
FROM   vehicles
       INNER JOIN vehicles_makes
         ON vehicles.make_id = vehicles_makes.id
       LEFT JOIN vehicles_models
         ON vehicles.model_id = vehicles_models.id
       LEFT JOIN vehicles_fuel
         ON vehicles.fuel_id = vehicles_fuel.id
       LEFT JOIN vehicles_transmissions
         ON vehicles.transmission_id = vehicles_transmissions.id
       LEFT JOIN vehicles_axles
         ON vehicles.axle_id = vehicles_axles.id
       LEFT JOIN vehicles_sub_years
         ON vehicles.sub_year_id = vehicles_sub_years.id
       INNER JOIN members
         ON vehicles.member_id = members.id
       LEFT JOIN vehicles_categories
         ON vehicles.category_id = vehicles_categories.id
WHERE  vehicles.status = 1
       AND vehicles.date_from < 1330349235
       AND vehicles.date_to > 1330349235
       AND vehicles.type_id = 1
       AND ( vehicles.price >= 0
             AND vehicles.price <= 1000000 )  

Qui è il veicolo dello schema di tabella:

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `number_plate` varchar(100) NOT NULL,
  `type_id` int(11) NOT NULL,
  `make_id` int(11) NOT NULL,
  `model_id` int(11) NOT NULL,
  `model_sub_type` varchar(250) NOT NULL,
  `engine_size` decimal(12,1) NOT NULL,
  `vehicle_year` int(11) NOT NULL,
  `sub_year_id` int(11) NOT NULL,
  `mileage` int(11) NOT NULL,
  `fuel_id` int(11) NOT NULL,
  `transmission_id` int(11) NOT NULL,
  `price` decimal(12,2) NOT NULL,
  `trade_or_private` tinyint(4) NOT NULL,
  `postcode` varchar(25) NOT NULL,
  `gps_lat` varchar(50) NOT NULL,
  `gps_lon` varchar(50) NOT NULL,
  `img1` varchar(100) NOT NULL,
  `img2` varchar(100) NOT NULL,
  `img3` varchar(100) NOT NULL,
  `img4` varchar(100) NOT NULL,
  `img5` varchar(100) NOT NULL,
  `img6` varchar(100) NOT NULL,
  `img7` varchar(100) NOT NULL,
  `img8` varchar(100) NOT NULL,
  `img9` varchar(100) NOT NULL,
  `img10` varchar(100) NOT NULL,
  `is_featured` tinyint(4) NOT NULL,
  `body_id` int(11) NOT NULL,
  `colour_id` int(11) NOT NULL,
  `doors` tinyint(4) NOT NULL,
  `axle_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `contents` text NOT NULL,
  `date_created` int(11) NOT NULL,
  `date_edited` int(11) NOT NULL,
  `date_from` int(11) NOT NULL,
  `date_to` int(11) NOT NULL,
  `member_id` int(11) NOT NULL,
  `inactive_id` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `type_id` (`type_id`),
  KEY `make_id` (`make_id`),
  KEY `model_id` (`model_id`),
  KEY `fuel_id` (`fuel_id`),
  KEY `transmission_id` (`transmission_id`),
  KEY `body_id` (`body_id`),
  KEY `colour_id` (`colour_id`),
  KEY `axle_id` (`axle_id`),
  KEY `category_id` (`category_id`),
  KEY `vehicle_year` (`vehicle_year`),
  KEY `mileage` (`mileage`),
  KEY `status` (`status`),
  KEY `date_from` (`date_from`),
  KEY `date_to` (`date_to`),
  KEY `trade_or_private` (`trade_or_private`),
  KEY `doors` (`doors`),
  KEY `price` (`price`),
  KEY `engine_size` (`engine_size`),
  KEY `sub_year_id` (`sub_year_id`),
  KEY `member_id` (`member_id`),
  KEY `date_created` (`date_created`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=136237 ;

La SPIEGA:

1   SIMPLE  vehicles    ref     type_id,make_id,status,date_from,date_to,price,mem...   type_id     4   const   85695   Using where
1   SIMPLE  members     index   PRIMARY     PRIMARY     4   NULL    3   Using where; Using index; Using join buffer
1   SIMPLE  vehicles_makes  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.make_id    1   Using index
1   SIMPLE  vehicles_models     eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.model_id   1   Using index
1   SIMPLE  vehicles_fuel   eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.fuel_id    1   Using index
1   SIMPLE  vehicles_transmissions  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.transmission_id    1   Using index
1   SIMPLE  vehicles_axles  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.axle_id    1   Using index
1   SIMPLE  vehicles_sub_years  eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.sub_year_id    1   Using index
1   SIMPLE  vehicles_categories     eq_ref  PRIMARY     PRIMARY     4   tvs.vehicles.category_id    1   Using index
InformationsquelleAutor ChimeraTheory | 2012-02-27



5 Replies
  1. 13

    Migliorare la clausola WHERE

    Vostro SPIEGARE mostra che MySQL è solo utilizzando un indice (type_id) per selezionare le righe che soddisfano la WHERE clausola, anche se si dispone di più criteri di cui alla clausola.

    Per essere in grado di utilizzare un indice per tutti i criteri previsti nella clausola WHERE, e per ridurre le dimensioni del set di risultati il più velocemente possibile, aggiungere un indice multi-colonna le seguenti colonne di veicoli tabella:

    (status, date_from, date_to, type_id, price)

    Le colonne dovrebbero essere in ordine di cardinalità massima di almeno.

    Per esempio, vehicles.date_from è probabile avere più valori distinti di status, così si può mettere la date_from colonna prima status, come questo:

    (date_from, date_to, price, type_id, status)

    Questo dovrebbe ridurre il numero di righe restituite nella prima parte dell’esecuzione della query, e deve essere dimostrato con un minor numero di riga la prima riga di SPIEGARE il risultato.

    Si noterà, inoltre, che MySQL utilizzare il multi-indice di colonna per CUI nel SPIEGARE il risultato. Se, per caso, non si dovrebbe suggerimento o forza multi-indice di colonna.

    Rimozione di inutili Unisce

    Non sembra che si utilizza uno qualsiasi dei campi in una qualsiasi delle tabelle collegate, in modo da togliere il join. Questo rimuoverà tutti gli ulteriori lavori di query e di arrivare fino a uno, semplice piano di esecuzione di una riga nel SPIEGARE il risultato).

    Ogni tabella in join provoca un’ulteriore ricerca per riga del set di risultati. Così, se la clausola WHERE seleziona le righe di 5.000 veicoli, dal momento che si dispone di 8 join per veicoli dovrai avere 5.000 * 8 = 40,000 ricerche. Questo è un sacco di chiedere il vostro server di database.

    • Se i campi si stanno confrontando venire da tabelle in join, sarà indicizzazione essere utile?
  2. 4

    Invece di costosi calcolo della distanza precisa per tutti righe, utilizzare una casella di delimitazione e di calcolare la distanza esatta solo per le righe all’interno della scatola.

    Il più semplice possibile esempio per calcolare min/max longitudine e latitudine che ti interessa e aggiungere WHERE clausola. In questo modo la distanza sarà calcolata solo per un sottoinsieme di righe.

    WHERE
        vehicles.gps_lat > min_lat ANDd vehicles.gps_lat < max_lat AND
        vehicles.gps_lon > min_lon AND vehicles.gps_lon < max_lon

    Per soluzioni più complesse vedere:

  3. 3

    È che SQL velocemente senza per questo?

    Round(3959 * Acos(Cos(Radians(51.465436)) *
      Cos(Radians(vehicles.gps_lat)) *
      Cos(Radians(vehicles.gps_lon) - 
      Radians(-0.296482)) + 
      Sin(Radians(51.465436)) * 
      Sin(Radians(vehicles.gps_lat)))) AS distance

    esecuzione di equazione matematica è molto costoso

    Forse si dovrebbe considerare una vista materializzata che pre-calcola la distanza, e si può selezionare da quel punto di vista. A seconda della modalità dinamica è dati, non è possibile aggiornare i dati troppo spesso.

    • Leggermente sì, ma ho bisogno che ci…
  4. 1

    Di essere un po ‘ più specifico di @Randy di indici, credo che la sua intenzione era di avere un indice COMPOSTO di approfittare della vostra interrogazione criteri… Un indice che si basa su un MINIMO di …

    ( status, type_id, date_from )

    ma potrebbe essere esteso per includere il date_to e il prezzo troppo, ma non so quanto l’indice a che livello granulare potrebbe realmente aiutare

    ( status, type_id, date_from, date_to, price )

    MODIFICA per i Commenti

    Non c’è bisogno di tutti quei singoli indici… Sì, la Chiave Primaria di per sé. Tuttavia, per gli altri, si dovrebbe disporre di indici composto basa su ciò che il comune di query criteri potrebbero essere e di rimuovere gli altri… il motore potrebbe ottenere confuso su quale potrebbe essere più adatto per la query. Se sai che sono sempre alla ricerca di un certo stato, tipo e data (supponendo che il veicolo ricerche), fanno si che come un indice. Se la query è alla ricerca di tali informazioni, ma anche i prezzi all’interno di tali criteri sarà già molto vicino a pochi record indicizzati, che qualificano e volare attraverso il prezzo in quanto i criteri aggiuntivi.

    Se si offrono di query come Solo Automatico vs Manuale trasmissione a prescindere dal anno/fare, allora sì, che potrebbe essere un indice di suo. Tuttavia, se si hanno in GENERE di alcuni altri “comuni” criteri di aderenza che come secondaria che PUÒ essere utilizzato nella query. Es: se si guarda per Trasmissioni Manuali che sono 2 porte vs 4 porte, avere il vostro indice (transmission_id, category_id).

    Di nuovo, si desidera che tutto ciò che sarà aiutare a restringere il campo di criteri basati su alcuni “minimo” condizione. Se virare su un extra di colonna per l’indice che potrebbe “comunemente” essere applicate, che dovrebbe contribuire a migliorare le prestazioni.

    • Non ho familiarità con indici composti – si prega di vedere il mio post aggiornato. È il mio attuale indicizzazione inefficiente?
    • Basta aggiungere un altro indice, ma invece di una SINGOLA colonna, basta fare come sopra, più colonne, separate da colonne… in Questo modo, un indice più componenti a più corrispondenza tra i criteri di interrogazione.
    • Io ho 21 indici, che ho bisogno di gruppo e non ho quindi bisogno di rimuoverli prima?
    • +1 che è quello che ho inteso… sembra la clausola WHERE è probabilmente il colpevole, e un buon indice su quelle colonne filtro sarà di grande aiuto.
    • rivisto risposta per indice di chiarimenti
    • Per ogni risultato (a mio ciclo PHP) faccio anche io un cap di ricerca in una tabella con circa 1,6 milioni di regno UNITO codice di avviamento postale record. Sarebbe indicizzazione questa tabella aiutare? Sembra che questa cosa dovrebbe essere indicizzato: CREATE TABLE IF NOT EXISTS post_codes ( id int(11) NOT NULL AUTO_INCREMENT, postcode varchar(100) NOT NULL, latitude doppio(9,6) NOT NULL, longitude doppio(9,6) NOT NULL, PRIMARY KEY (id), CHIAVE postcode (postcode) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1696036 ; – devo indice full-text, il codice di avviamento postale campo come fare una ricerca su questo?

  5. 1

    Per chiarire questo come una risposta: se non si dispone già di questi indici, si dovrebbe considerare l’aggiunta di loro

    fare anche gli indici di questi:

    vehicles.status
    vehicles.date_from
    vehicles.date_to
    vehicles.type_id
    vehicles.price

Lascia un commento