Spesso capita che una pagina web ci metta molto a rispondere e quindi a caricarsi. Questo può dipendere anche dal fatto che l’interrogazione verso il database faccia da collo di bottiglia magari per una query che ci impiega molto tempo a restituire i record.
Per il database open source acquisito da Oracle, c’è mysql-slow-log, un file di testo nel quale MySQL scrive tutte le query che vengono eseguite in un tempo superiore al valore impostato nel parametro long_query_time (che di default vale 10 secondi). Solitamente si trova sotto /var/log/mysql/.
Ecco un esempio:
SELECT `Coupling`.`id`, `Coupling`.`sti_type`, `Coupling`.`model_id`, `Coupling`.`section_id`, `Coupling`.`product_id`, `Coupling`.`ratio_table_code`, `Coupling`.`start_year`, `Coupling`.`end_year`, `Coupling`.`serie`, `Coupling`.`shifter_group`, `Coupling`.`legacy_id`, `Coupling`.`legacy_coupling_id`, `Coupling`.`last_user_id`, `Coupling`.`created_at`, `Coupling`.`updated_at`, `Model`.`id`, `Model`.`description`, `Model`.`brand_id`, `Model`.`displacement_id`, `Model`.`legacy_id`, `Model`.`last_user_id`, `Model`.`created_at`, `Model`.`updated_at`, `Section`.`id`, `Section`.`spread_id`, `Section`.`section_description_id`, `Section`.`description`, `Section`.`position`, `Section`.`description_centauro_it`, `Section`.`description_centauro_en`, `Section`.`position_centauro`, `Section`.`legacy_id`, `Section`.`last_user_id`, `Section`.`created_at`, `Section`.`updated_at`, `Section`.`description_en`, `Section`.`description_de`, `Section`.`description_fr`, `Section`.`description_es`, `Section`.`description_pt`, `Coupling_note`.`id`, `Coupling_note`.`note_id`, `Coupling_note`.`coupling_id`, `Coupling_note`.`legacy_id`, `Coupling_note`.`last_user_id`, `Coupling_note`.`created_at`, `Coupling_note`.`updated_at`, `Product`.`id`, `Product`.`code`, `Product`.`section_id`, `Product`.`availability`, `Product`.`dimension_x`, `Product`.`dimension_y`, `Product`.`dimension_z`, `Product`.`weight`, `Product`.`trade_family`, `Product`.`trade_group`, `Product`.`is_kit`, `Product`.`legacy_id`, `Product`.`last_user_id`, `Product`.`created_at`, `Product`.`updated_at`, `Product`.`pieces`, `Description`.`id`, `Description`.`product_id`, `Description`.`description`, `Description`.`legacy_id`, `Description`.`last_user_id`, `Description`.`created_at`, `Description`.`updated_at`, `Description`.`description_en`, `Description`.`description_de`, `Description`.`description_fr`, `Description`.`description_es`, `Description`.`description_pt` FROM `couplings` AS `Coupling` LEFT JOIN `models` AS `Model` ON (`Coupling`.`model_id` = `Model`.`id`) LEFT JOIN `sections` AS `Section` ON (`Coupling`.`section_id` = `Section`.`id`) LEFT JOIN `coupling_notes` AS `Coupling_note` ON (`Coupling_note`.`coupling_id` = `Coupling`.`id`) LEFT JOIN `products` AS `Product` ON (`Product`.`id` = `Coupling`.`product_id`) LEFT JOIN `descriptions` AS `Description` ON (`Coupling`.`product_id` = `Description`.`product_id`) WHERE `Coupling`.`model_id` = 7009 LIMIT 20
Lanciamo: EXPLAIN SELECT `Coupling`.`id`, `Coupling`.`sti_type`, `Coupling`.`model_id`...
EXPLAIN viene utilizzata per consultare il “query optimizer”, la parte del server che effettua le valutazioni su come rendere più veloce l’interrogazione che viene richiesta al database: è questa parte che decide se un indice dovrà essere utilizzato, l’ordine di verifica delle condizioni ecc.
In pratica, interrogando il query optimizer si chiede a MySQL come intende sviluppare la query.
Le informazioni che fornisce EXPLAIN sono utili per molti aspetti:
- forniscono indizi circa l’opportunità di aggiunta di alcuni indici alle tabelle;
- se una tabella ha già degli indici utilizzati, l’output del comando aiuta a capire come vengono utilizzati dal motore;
- se gli indici esistono ma non vengono utilizzati dal query optimizer, aiuta a scrivere meglio il codice SQL affinché la query venga eseguita beneficiando della presenza dell’indice.
Nella fattispecie della mia query è bastato aggiungere l’indice per il campo coupling_id nella tabella coupling_notes per ridurre il tempo di esecuzione della query da 250 secondi (quattro minuti) a 0.06 secondi.
Forte vero?
Approfondisci la lettura qui:
http://dev.mysql.com/doc/refman/5.6/en/optimization.html
http://dev.mysql.com/doc/refman/5.6/en/using-explain.html
http://dev.mysql.com/doc/refman/5.6/en/explain.html