[MySQL] Query met veel sortering versnellen

[bindsa]

Ik heb een query die vrij veel moet sorteren en ook nog wat joins nodig heeft, en daardoor redelijk traag wordt:

SQL:

SELECT accounts.*, sys_provinces.name AS province_name, sys_cities.name AS city_name, sys_cities.province_id AS province_id, c2.cnt AS cnt
FROM (accounts)

LEFT JOIN (
    SELECT 1 as vip_city, city_id 
    FROM accounts AS accs_2 
    WHERE account_type_id = 1 
    AND accs_2.state = 1 
    AND accs_2.accepted = 1 
    AND accs_2.activated = 1
) AS accs_2 
ON accounts.city_id = accs_2.city_id

JOIN sys_cities 
ON sys_cities.id = accounts.city_id

JOIN (
    SELECT city_id, COUNT(city_id) AS cnt 
    FROM accounts 
    WHERE accounts.state = 1 
    AND accounts.activated = 1 
    AND accounts.accepted = 1 
    GROUP BY city_id
) AS c2 
ON c2.city_id = accounts.city_id

LEFT JOIN (
    SELECT place_id AS rating_acc,count(rating1) AS cnt_ratings_abs, sum(round((rating1+rating2+rating3+rating4)/4, 1))/count(rating1) AS cnt_ratings 
    FROM ratings 
    GROUP BY ratings.place_id) AS ratings 
ON accounts.id = ratings.rating_acc

LEFT JOIN sys_provinces 
ON sys_provinces.id = sys_cities.province_id

WHERE (accounts.state = 1 
AND accounts.activated = 1 
AND accounts.accepted = 1)

ORDER BY 
    vip_city desc, 
    c2.cnt desc, 
    city_id desc, 
    FIELD(accounts.account_type_id, 1) desc, 
    ratings.cnt_ratings_abs desc, 
    ratings.cnt_ratings desc, 
    accounts.company_name

Relevante indexes:

accounts.account_type_id	Normal	BTREE
accounts.city_id	Normal	BTREE
accounts.activated	Normal	BTREE
accounts.state	Normal	BTREE
accounts.accepted	Normal	BTREE
accounts.activated,accounts.state,accounts.accepted	Normal	BTREE
ratings.place_id	Normal	BTREE
sys_cities.province_id	Normal	BTREE
accounts.company_name	Normal	BTREE

Explain:

SQL:

1   PRIMARY <derived3>  ALL                 223 Using temporary; Using filesort
1   PRIMARY accounts    ref city_id,activated,accepted,state,state_2,state_3    city_id 9   c2.city_id  2   Using where
1   PRIMARY <derived2>  ALL                 1   
1   PRIMARY <derived4>  ALL                 4   
1   PRIMARY sys_cities  eq_ref  PRIMARY,id  PRIMARY 4   theme_system.accounts.city_id   1   Using where
1   PRIMARY sys_provinces   eq_ref  PRIMARY PRIMARY 4   theme_system.sys_cities.province_id 1   
4   DERIVED ratings index       place_id    9       5   
3   DERIVED accounts    ref activated,accepted,state,state_2,state_3    activated   1       488 Using where; Using temporary; Using filesort
2   DERIVED accs_2  ref account_type_id,activated,accepted,state,state_2,state_3    account_type_id 4       1   Using where

En ook een query die de postcodes in een range zoekt:

SQL:

SELECT DISTINCT city_id 
FROM sys_postal_codes
WHERE
    ROUND(
        ( 6378136 * acos( cos( radians( 52.273300 ) ) 
        * cos( radians( sys_postal_codes.lat ) ) 
        * cos( radians( sys_postal_codes.lng ) - radians( 5.243605 ) ) 
        + sin( radians( 52.273300 ) ) 
        * sin( radians( sys_postal_codes.lat ) ) ) ) < '15000'
        )
        ORDER BY ROUND(
                ( 6378136 * acos( cos( radians( 52.273300 ) ) 
        * cos( radians( sys_postal_codes.lat ) ) 
        * cos( radians( sys_postal_codes.lng ) - radians( 5.243605 ) ) 
        + sin( radians( 52.273300 ) ) 
        * sin( radians( sys_postal_codes.lat ) ) ) )) 
LIMIT 20

Deze duurt ong. 1.5 sec. Relevante indexes die ik geplaatst heb:

sys_postal_codes.name	Normal	BTREE
sys_postal_codes.lat	Normal	BTREE
sys_postal_codes.lng	Normal	BTREE

Explain:

SQL:

1 SIMPLE sys_postal_codes ALL 650430 Using where; Using temporary; Using filesort

Hebben jullie enig idee hoe deze queries verder te optimaliseren zijn?

[frickY]

Mogelijk met denormalisatie.
Zorg dat je geen berekeningen of aggregaties op kolommen hoeft te doen waarop je wilt sorteren, zodat je daar (bruikbare) indexen op kunt plaatsen.

Een quick-win kan zijn om je sort buffers en temporary table size te verhogen. In je explain zie je nu 2x een filesort. Het zal ook al schelen als hij dat in memory kan doen.

[ Voor 35% gewijzigd door frickY op 09-04-2012 15:51 ]

[Voutloos]

Die laatste query is wel heel makkelijk te fixen door een bounding box (zoekterm voor kant en klare oplossingen) in je where clause toe te voegen. Dwz een grof vierkant gebied waarvoor wel indexes gebruikt kunnen worden.

[bindsa]

In die richting zat ik zelf ook al te denken, ik hoopte dat ik nog ergens mijn joins etc. kon optimaliseren. In ieder geval bedankt, ik ga de relevante triggers aanmaken en een bounding box toevoegen.

[GlowMouse]

Ik snap je query niet goed. Wat doet de join op regel 4-15, wat doet de GROUP BY op regel 40, en wat doet de FIELD op regel 45?

frickY schreef op maandag 09 april 2012 @ 15:49:
In je explain zie je nu 2x een filesort. Het zal ook al schelen als hij dat in memory kan doen.

gebeurt dat nu niet?

[bindsa]

GlowMouse schreef op maandag 09 april 2012 @ 18:29:
Ik snap je query niet goed. Wat doet de join op regel 4-15, wat doet de GROUP BY op regel 40, en wat doet de FIELD op regel 45?

[...]

gebeurt dat nu niet?

4-15: Ik wil gaan orderen op bepaalde steden, namelijk de steden die VIP accounts hebben. Daarom doe ik een subquery in de join die een 1 selecteerd als de stad één of meerdere VIP accounts heeft, en omdat ik LEFT JOIN zal de rest null geven. En dus werkt ORDER BY vip_city precies zoals de bedoeling is.

De FIELD sorteert eerst op records waarbij account_type_id = 1 en daarna volgt de rest.

De GROUP BY was een foutje en heb ik weggehaald (Deze query wordt dynamisch gegenereerd, en ik had in de gauwigheid de verkeerde stukken aan elkaar geplakt.