# OT , ottimizzazione query mySql

**URL:** <https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811>\
**Category:** IT\
**Created:** [October 20, 2010, 5:24pm UTC](https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811 "2010-10-20T17:24:10Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![okkezSS](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/o/85f322/32.png) [@okkezSS](https://www.ruby-forum.com/u/okkezSS)\
**Post date:** [October 20, 2010, 5:24pm UTC](https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811/1 "2010-10-20T17:24:10Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![ialuronico](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/c4cdca/32.png) [@ialuronico](https://www.ruby-forum.com/u/ialuronico)\
**Post date:** [October 20, 2010, 5:36pm UTC](https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811/2 "2010-10-20T17:36:00Z")

</div>

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. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid)

---

<div class="post-metadata">

**Author:** ![ialuronico](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/c4cdca/32.png) [@ialuronico](https://www.ruby-forum.com/u/ialuronico)\
**Post date:** [October 20, 2010, 6:06pm UTC](https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811/3 "2010-10-20T18:06:53Z")

</div>

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’

---

<div class="post-metadata">

**Author:** ![ialuronico](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/i/c4cdca/32.png) [@ialuronico](https://www.ruby-forum.com/u/ialuronico)\
**Post date:** [October 20, 2010, 6:28pm UTC](https://www.ruby-forum.com/t/ot-ottimizzazione-query-mysql/197811/4 "2010-10-20T18:28:20Z")

</div>

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:  
[Google Code Archive - Long-term storage for Google Code Project Hosting.](http://code.google.com/p/query-reviewer/)

Ciao  
Flavio