Salve a tutti , ho un applicazione Ror che purtroppo ha dei problemi di
performance per quanto riguarda alcune query,
San Mongrel 
mi ha dato dei preziosissimi suggerimenti su quali
sono le query che ammazzano il server …
Io ho fatto un po’ di prove e qualcosina sono riuscito ad ottimizzare
con gli index e stavo pensando di passare da InnoDB a myIsam visto che
l’intero sito e’ quasi esclusivamente in lettura e le scritture sono
veramente poche.
Prima di cambiare engine ( InnoDB sebbene piu’ lento e’ decisamente piu’
robusto) volevo chiedervi come ottimizzereste queste query.
All’ inizio c’e’ l’output della chiamata ticica della pagina che mi
interessa velocizzare , sotto ho messo piu’ o meno i tempi minimi e
massimi di esecuzione delle query che ho trovato nei log
Chiamata complessiva
Processing Frontend::BaseController#serch_category_customers (for
151.15.40.226 at 2010-10-20 15:11:34) [POST]
Parameters: {“search”=>{“city”=>“5491”, “subcategory”=>"",
“category”=>“4”, “work_on_247”=>“0”, “work_on_saturday”=>“0”,
“work_on_night”=>“0”, “work_on_sunday”=>“0”, “province”=>“63”},
“action”=>“serch_category_customers”,
“authenticity_token”=>“Z/ILznFIyX42xcl7xsK/Dy86/ZGzIKTavTZHndtSOqo=”,
“controller”=>“frontend/base”}
Page Load (0.2ms) SELECT * FROM pages
Account Columns (2.6ms) SHOW FIELDS FROM accounts
Attachment Columns (1.1ms) SHOW FIELDS FROM attachments
SQL (579.2ms) SELECT count(*) AS count_all FROM accounts INNER
JOIN accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘4’)
SQL (168.4ms) SELECT count(*) AS count_all FROM accounts INNER
JOIN accounts_cities ON (accounts.id =
accounts_cities.account_id) INNER JOIN cities ON (cities.id =
accounts_cities.city_id) WHERE (city_id = ‘5491’ and
is_punto_vendita = 1)
Account Load (4848.7ms) SELECT accounts.* FROM accounts INNER
JOIN accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘4’) ORDER BY
rating DESC, company
Account Load (293.3ms) SELECT accounts.* FROM accounts INNER
JOIN accounts_cities ON (accounts.id =
accounts_cities.account_id) INNER JOIN cities ON (cities.id =
accounts_cities.city_id) WHERE (city_id = ‘5491’ and
is_punto_vendita = 1) ORDER BY rating DESC, company
SQL (177.7ms) SELECT count(*) AS count_all FROM accounts INNER
JOIN accounts_provinces ON (accounts.id =
accounts_provinces.account_id) INNER JOIN provinces ON
(provinces.id = accounts_provinces.province_id) WHERE
(province_id = ‘63’ and is_punto_vendita = 1)
Account Load (462.5ms) SELECT accounts.* FROM accounts INNER
JOIN accounts_provinces ON (accounts.id =
accounts_provinces.account_id) INNER JOIN provinces ON
(provinces.id = accounts_provinces.province_id) WHERE
(province_id = ‘63’ and is_punto_vendita = 1) ORDER BY rating DESC,
company
City Columns (328.4ms) SHOW FIELDS FROM cities
City Load (0.6ms) SELECT * FROM cities WHERE (cities.id =
‘5491’) LIMIT 1
Province Columns (0.8ms) SHOW FIELDS FROM provinces
Province Load (0.4ms) SELECT * FROM provinces WHERE
(provinces.id = ‘63’) LIMIT 1
… etc , il resto del log per questa pagina non mostra operazioni lente
Queste sono le query critiche che ho trovato pascolando nel log
5359ms
SELECT accounts.* FROM accounts INNER JOIN accounts_categories ON
accounts_categories.account_id = accounts.id INNER JOIN categories
ON categories.id = accounts_categories.category_id WHERE
(category_id = ‘4’) ORDER BY rating DESC, company
4145.7ms
SELECT accounts.* FROM accounts INNER JOIN accounts_categories ON
accounts_categories.account_id = accounts.id INNER JOIN categories
ON categories.id = accounts_categories.category_id WHERE
(category_id = ‘56’) ORDER BY rating DESC, company
1155.6ms
SELECT DISTINCT accounts.id, accounts.company, accounts.address,
accounts.city, accounts.cap, accounts.province FROM accounts INNER
JOIN accounts_categories ON accounts.id =
accounts_categories.account_id WHERE (role = ‘customer’ AND
accounts_categories.category_id = 29 ) ORDER BY rating DESC, company
545ms
SELECT count() AS count_all FROM accounts INNER JOIN
accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘56’)
540.2ms
SELECT count() AS count_all FROM accounts INNER JOIN
accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘29’)
598.0ms
SELECT count() AS count_all FROM accounts INNER JOIN
accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘4’)
549.0ms
SELECT count() AS count_all FROM accounts INNER JOIN
accounts_categories ON accounts_categories.account_id =
accounts.id INNER JOIN categories ON categories.id =
accounts_categories.category_id WHERE (category_id = ‘24’)
596.5ms
SELECT accounts.* FROM accounts INNER JOIN accounts_categories ON
accounts_categories.account_id = accounts.id INNER JOIN categories
ON categories.id = accounts_categories.category_id WHERE
(category_id = ‘25’) ORDER BY rating DESC, company
540.5ms
SELECT accounts.* FROM accounts INNER JOIN accounts_categories ON
accounts_categories.account_id = accounts.id INNER JOIN categories
ON categories.id = accounts_categories.category_id WHERE
(category_id = ‘24’) ORDER BY rating DESC, company
506.2ms
SELECT accounts.* FROM accounts INNER JOIN accounts_provinces ON
(accounts.id = accounts_provinces.account_id) INNER JOIN
provinces ON (provinces.id = accounts_provinces.province_id)
WHERE (province_id = ‘46’ and is_punto_vendita = 1) ORDER BY rating
DESC, company
126ms
SELECT accounts.* FROM accounts INNER JOIN accounts_cities ON
(accounts.id = accounts_cities.account_id) INNER JOIN cities
ON (cities.id = accounts_cities.city_id) WHERE (city_id = ‘5784’
and is_punto_vendita = 1) ORDER BY rating DESC, company
p.s. purtroppo alcune query sono generate da gemme e fiche’ posso
ottimizzare il database preferirei evitare di mettere mano al codice di
gemme esterne