OT , ottimizzazione query mySql

Salve a tutti , ho un applicazione Ror che purtroppo ha dei problemi di
performance per quanto riguarda alcune query,

San Mongrel :smiley: :smiley: :smiley: 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

Su quali colonne hai messo gli indici?

mi aspetto che siano su:
accounts_categories.account_id
accounts_categories.category_id
accounts.rating
accounts.company
accounts.province_id
accounts.city_id
accounts.is_punto_vendita
accounts_provinces.account_id
accounts_provinces.province_id
accounts_cities.account_id

Ciao,
A

2010/10/20 Simone R. [email protected]

Antonio C. wrote in post #955808:

Su quali colonne hai messo gli indici?

mi aspetto che siano su:
accounts_categories.account_id
C’e’
accounts_categories.category_id
C’e’
accounts.rating
C’e’
accounts.company
C’e’

accounts.province_id
accounts.city_id

L’accounts non ha queste colonne perche’ puo’ operare su piu’ province o
su piu’ citta’ della stessa provincia , esistono le due tabelle
di collegamento
accounts_cites ed account_provinces
composte dalle colonne:
account_id , city/province_id , is_punto_vendita

accounts.is_punto_vendita
non e’ nella tabella accounts

accounts_provinces.account_id
c’e’
accounts_provinces.province_id
c’e’
accounts_cities.account_id
c’e’

On Wednesday 20 October 2010 17:24:10 Simone R. wrote:

Salve a tutti , ho un applicazione Ror che purtroppo ha dei problemi di
performance per quanto riguarda alcune query,

Prova a farti consigliare anche da questa gemma:
http://code.google.com/p/query-reviewer/

Ciao
Flavio

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs