Un po' di sano brainstorming: miglioriamo una query

14 contenuti / 0 new

Accedi o registrati per inserire commenti.

Gio, 29/09/2011 - 09:44

Ciao a tutti, oggi vorrei proporre una cosa diversa dal solito: migliorare una query MYSQL che viene creata tramite una vista:

Questa è la query:

SELECT node.nid AS nid,
node_data_field_product_utente.field_product_disegno_part_fid AS
node_data_field_product_utente_field_product_disegno_part_fid,
node_data_field_product_utente.field_product_disegno_part_list AS
node_data_field_product_utente_field_product_disegno_part_list,
node_data_field_product_utente.field_product_disegno_part_data AS
node_data_field_product_utente_field_product_disegno_part_data,
node.language AS node_language, node.type AS node_type, node.vid AS
node_vid, node_data_field_product_utente.field_product_name_value AS
node_data_field_product_utente_field_product_name_value,
node_data_field_product_utente.field_product_competitor_value AS
node_data_field_product_utente_field_product_competitor_value,
node_data_field_product_utente.field_product_mactype_tax_value AS
node_data_field_product_utente_field_product_mactype_tax_value,
node_data_field_product_utente.field_product_modello_value AS
node_data_field_product_utente_field_product_modello_value,
node_data_field_product_utente.field_product_mostraprezzo_value AS
node_data_field_product_utente_field_product_mostraprezzo_value,
uc_products.sell_price AS uc_products_sell_price,
node_data_field_product_peso.field_product_peso_value AS
node_data_field_product_peso_field_product_peso_value,
node_data_field_product_peso.field_product_mostraprezzo_value AS
node_data_field_product_peso_field_product_mostraprezzo_value, RAND()
AS _random FROM shop_node node LEFT JOIN shop_content_type_product
node_data_field_product_utente ON node.vid =
node_data_field_product_utente.vid LEFT JOIN shop_uc_products
uc_products ON node.vid = uc_products.vid LEFT JOIN
shop_content_type_product node_data_field_product_peso ON node.vid =
node_data_field_product_peso.vid WHERE (node.type = 'product') AND
(node.language in ('en')) AND
(node_data_field_product_utente.field_product_utente_uid IS NULL) AND
(node.status <> 0) ORDER BY
node_data_field_product_peso_field_product_peso_value ASC,
node_data_field_product_peso_field_product_mostraprezzo_value DESC,
_random ASC
limit 0,10;

I tempi di questa query sono mastodontici (su 400000+ nodi): 6.15secondi

Da cosa partiamo ?

Gio, 29/09/2011 - 10:29

ziobudda

Inizio io con "se tolgo l'ordinamento" la query è velocissima.

E se vado di "EXPLAIN" ottengo:

+----+-------------+--------------------------------+--------+----------------------------+-----------+---------+---------------+--------+----------------------------------------------+
| id | select_type | table                          | type   | possible_keys              | key       | key_len | ref           | rows   | Extra                                        |
+----+-------------+--------------------------------+--------+----------------------------+-----------+---------+---------------+--------+----------------------------------------------+
|  1 | SIMPLE      | node                           | range  | node_status_type,node_type | node_type | 14      | NULL          | 210284 | Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | node_data_field_product_utente | eq_ref | PRIMARY                    | PRIMARY   | 4       | shop.node.vid |      1 | Using where                                  |
|  1 | SIMPLE      | uc_products                    | ref    | PRIMARY,vid                | vid       | 4       | shop.node.vid |      1 |                                              |
|  1 | SIMPLE      | node_data_field_product_peso   | eq_ref | PRIMARY                    | PRIMARY   | 4       | shop.node.vid |      1 |                                              |
+----+-------------+--------------------------------+--------+----------------------------+-----------+---------+---------------+--------+----------------------------------------------+

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 12:02

aturetta

Evidentemente la tabella node_data_field_product_peso (essendo creata da CCK) mancherà dell'indice necessario.

Prova a creare un indice tipo:

CREATE INDEX IDX_PESO ON node_data_field_product_peso (field_product_peso_value ASC, field_product_mostraprezzo_value DESC);

E vedi se migliora. Mi sconvolge un po' il RAND() usato nell'ordinamento, ma penso che l'indice possa fare bene comunque.

Angelo Turetta

Gio, 29/09/2011 - 12:10

ziobudda

Ciao, gia' fatto tutti gli indici possibili. Ecco un explain aggiornato:

l'indice che viene utilizzato è quello basato su type, status e language.

Anche creando il tuo INDICE non cambia molto.

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 12:28

aturetta

In effetti il where è su quei tre campi..

Il problema è che views aggiunge bovinamente tabelle in join, e non prova a fare delle subquery. Quei custom field su cui basi l'ordinamento costringono a ricopiare tutti i dati in memoria per poi ordinarli...

Angelo Turetta

Gio, 29/09/2011 - 12:35

ziobudda

Ciao, si ho notato e ho tolto la join di troppo. Ho anche sostituito gli "IN" con degli "=".
La query è migliorata un po', ma non più di tanto.

15 rows in set (4.18 sec)

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 12:44

aturetta

Il fatto è che questa query andrebbe riscritta facendo una subquery senza la tabella node_data_field_product_peso in cui il WHERE limita drasticamente le righe, per poi aggiungere il JOIN con node_data_field_product_peso per l'ordinamento (ma a quel punto le righe sono 15 e non 50000).

Purtroppo views non è l'ideale per qualsiasi situazione.

Angelo Turetta

Gio, 29/09/2011 - 14:08

ziobudda

@aturetta: sai che non ho mica capito. Se il peso è una parte dell'ORDER BY come fai a toglierla dalla query principale ?

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 14:20

aturetta

Quello che intendevo è qualcosa tipo:

SELECT sub.*, node_data_field_product_peso.field_product_peso_value AS
node_data_field_product_peso_field_product_peso_value,
node_data_field_product_peso.field_product_mostraprezzo_value AS
node_data_field_product_peso_field_product_mostraprezzo_value, RAND() AS _random FROM
(SELECT node.nid AS nid,
node_data_field_product_utente.field_product_disegno_part_fid AS
node_data_field_product_utente_field_product_disegno_part_fid,
node_data_field_product_utente.field_product_disegno_part_list AS
node_data_field_product_utente_field_product_disegno_part_list,
node_data_field_product_utente.field_product_disegno_part_data AS
node_data_field_product_utente_field_product_disegno_part_data,
node.language AS node_language, node.type AS node_type, node.vid AS
node_vid, node_data_field_product_utente.field_product_name_value AS
node_data_field_product_utente_field_product_name_value,
node_data_field_product_utente.field_product_competitor_value AS
node_data_field_product_utente_field_product_competitor_value,
node_data_field_product_utente.field_product_mactype_tax_value AS
node_data_field_product_utente_field_product_mactype_tax_value,
node_data_field_product_utente.field_product_modello_value AS
node_data_field_product_utente_field_product_modello_value,
node_data_field_product_utente.field_product_mostraprezzo_value AS
node_data_field_product_utente_field_product_mostraprezzo_value,
uc_products.sell_price AS uc_products_sell_price
FROM shop_node node LEFT JOIN shop_content_type_product
node_data_field_product_utente ON node.vid =
node_data_field_product_utente.vid LEFT JOIN shop_uc_products
uc_products ON node.vid = uc_products.vid
WHERE (node.type = 'product') AND
(node.language in ('en')) AND
(node_data_field_product_utente.field_product_utente_uid IS NULL) AND
(node.status <> 0)
) sub
 LEFT JOIN
shop_content_type_product node_data_field_product_peso ON node_vid =
node_data_field_product_peso.vid
 ORDER BY
node_data_field_product_peso_field_product_peso_value ASC,
node_data_field_product_peso_field_product_mostraprezzo_value DESC,
_random ASC
limit 0,10;

Prova se funziona, e se i tempi sono diversi
Solo che, anche se funzionasse, non so come farai a convincere views a generare qualcosa di simile...

PS: ho corretto la query...

Angelo Turetta

Gio, 29/09/2011 - 14:34

#10

ziobudda

Ciao, provato, ma non è cambiato nulla.

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 15:12

#11

aturetta

Che casino, collega due volte la stessa tabella....

Ultimo tentativo...

SELECT sub.*,
node_data_field_product.field_product_disegno_part_fid AS node_data_field_product_utente_field_product_disegno_part_fid,
node_data_field_product.field_product_disegno_part_list AS node_data_field_product_utente_field_product_disegno_part_list,
node_data_field_product.field_product_disegno_part_data AS node_data_field_product_utente_field_product_disegno_part_data,
node_data_field_product.field_product_name_value AS node_data_field_product_utente_field_product_name_value,
node_data_field_product.field_product_competitor_value AS node_data_field_product_utente_field_product_competitor_value,
node_data_field_product.field_product_mactype_tax_value AS node_data_field_product_utente_field_product_mactype_tax_value,
node_data_field_product.field_product_modello_value AS node_data_field_product_utente_field_product_modello_value,
node_data_field_product.field_product_mostraprezzo_value AS node_data_field_product_utente_field_product_mostraprezzo_value,
uc_products.sell_price AS uc_products_sell_price,
node_data_field_product.field_product_peso_value AS node_data_field_product_peso_field_product_peso_value,
node_data_field_product.field_product_mostraprezzo_value AS node_data_field_product_peso_field_product_mostraprezzo_value,
RAND() AS _random FROM
(SELECT node.nid AS nid,
node.language AS node_language,
node.type AS node_type,
node.vid AS node_vid,
FROM shop_node node
LEFT JOIN shop_content_type_product ON node.vid =shop_content_type_product.vid AND field_product_utente_uid IS NULL
WHERE (node.type = 'product') AND
(node.language = 'en') AND
(node.status > 0)
) sub
LEFT JOIN shop_content_type_product node_data_field_product ON node_vid = node_data_field_product.vid
LEFT JOIN shop_uc_products uc_products ON node_vid = uc_products.vid
ORDER BY
node_data_field_product_peso_field_product_peso_value ASC,
node_data_field_product_peso_field_product_mostraprezzo_value DESC,
_random ASC
limit 0,10;

con questo indice:

CREATE INDEX IDX_PESO ON shop_content_type_product (field_product_peso_value ASC, field_product_mostraprezzo_value DESC,vid ASC);

e al limite anche questo:

CREATE INDEX IDX_UID ON shop_content_type_product (vid ASC, field_product_utente_uid ASC);

(cancella il precedente)

Angelo Turetta

Gio, 29/09/2011 - 15:35

#12

ziobudda

Ciao, 10 rows in set (5.24 sec)

Fate conto che a regime i nodi su cui effettuare le operazioni saranno +300.000

Ovviamente possono anche modificare la query e farne usare una nuova tramite le varie hook_*

--
Michel 'ZioBudda' Morelli -- [email protected]
Sviluppo applicazioni CMS DRUPAL e web dinamiche -- Corsi Drupal -- Amministrazione Drupal -- Hosting Drupal

Gio, 29/09/2011 - 15:54

#13

mavimo

hook_query_alter() funziona anche con views, cmq il problema sembra essere nel trovare la query corretta.

Innazitutto NON usare SELECT * che è il male per ogni query, così come eviterei di fare subquery (non sono il male assoluto, ma nemmeno troppo belle).

Di seguito il codice un filo riordinato.

SELECT
  node.nid AS nid,
  pu.field_product_disegno_part_fid AS pu_field_product_disegno_part_fid,
  pu.field_product_disegno_part_list AS pu_field_product_disegno_part_list,
  pu.field_product_disegno_part_data AS pu_field_product_disegno_part_data,
  node.language AS node_language,
  node.type AS node_type,
  node.vid AS node_vid,
  pu.field_product_name_value AS pu_field_product_name_value,
  pu.field_product_competitor_value AS pu_field_product_competitor_value,
  pu.field_product_mactype_tax_value AS pu_field_product_mactype_tax_value,
  pu.field_product_modello_value AS pu_field_product_modello_value,
  pu.field_product_mostraprezzo_value AS pu_field_product_mostraprezzo_value,
  uc_products.sell_price AS uc_products_sell_price,
  pw.field_product_peso_value AS pw_field_product_peso_value,
  pw.field_product_mostraprezzo_value AS pw_field_product_mostraprezzo_value,
  RAND() AS _random
FROM shop_node node
LEFT JOIN shop_content_type_product pu ON node.vid = pu.vid
LEFT JOIN shop_uc_products uc_products ON node.vid = uc_products.vid
LEFT JOIN shop_content_type_product pw ON node.vid = pw.vid
WHERE
  (node.type = 'product') AND
  (node.language in ('en')) AND
  (pu.field_product_utente_uid IS NULL) AND
  (node.status <> 0)
ORDER BY
  pw_field_product_peso_value ASC,
  pw_field_product_mostraprezzo_value DESC,
  _random ASC
limit 0,10;

Ad occhio inizierei a capire se le where condiction sono tutte necessarie e se cambiando l'ordinamento delle JOIN cambia qualche cosa. Poi controllerei se per caso la tabella tende a swappare su disco. Dalll'explain sembra che se vuoi veramente estrarre le inforamzioni così non ci siano altri modi.. probabilmente impostando la views su node e non field, ti potresti evitare un pò di "problemi" ed ottenere una query più performante (per l'estrazionee degli NID da usare).

Ciao
Marco
--
My blog
Working at @agavee

Gio, 29/09/2011 - 16:20

#14

aturetta

@Mavimo: la subquery l'ho proposta io perché evidentemente l'ottimizzatore di mysql con quella completa non riesce ad ottimizzare una fava. La select * riguardava solo la subquery, ed era solo per test: chiaro che se i dati li vuoi caricare in un resultset php è meglio specificare i nomi delle colonne.

@ziobudda: ci rinuncio. Non capisco nemmeno perché nel test precedente avesse ritornato 15 righe, visto che c'è il LIMIT... Per fare altri test avrei bisogno del DB....

Angelo Turetta

Main menu

Accesso Utente

Nuovi argomenti del forum

Utenti on-line

Un po' di sano brainstorming: miglioriamo una query

Main menu

Tu sei qui

Accesso Utente

Nuovi argomenti del forum

Utenti on-line

Un po' di sano brainstorming: miglioriamo una query