Optimisation requête avec GROUP BY (avec opérations) et ORDER BY

[ghisb]

Bonjour,

Je bloque depuis pas mal de temps sur la création d'un index pour une requête.

La table sur laquelle je veux créer un index contient 15 millions d'enregistrements (50000 enregistrements sont créés chaque jour).

La structure de ma table :

stats_property_history_property_id int(11) (PK)
stats_property_history_date date (PK)
stats_property_history_visits int(11)
stats_property_history_phones int(11)
stats_property_history_emails int(11)
stats_property_history_offers int(11)
stats_property_history_leads int(11)
stats_property_history_impressions int(11)
stats_property_history_impressions_homepage int(11)
stats_property_history_impressions_search int(11)
stats_property_history_alerts int(11)
stats_property_history_agent_id int(11)
stats_property_history_property_type_id int(11)
stats_property_history_property_type_sale_id int(11)
stats_property_history_property_housing_id int(11)
stats_property_history_bedroom_id int(11)
stats_property_history_loc_city_id int(11)
stats_property_history_loc_community_id int(11)
stats_property_history_loc_subcommunity_id int(11)
stats_property_history_loc_tower_id int(11)
stats_property_history_price double
stats_property_history_unified_price double
stats_property_history_sqft double
stats_property_history_images int(11)
stats_property_history_property_showstyle_id int(11)
stats_property_history_property_showstyle_level int(11)
stats_property_history_property_ranking_total int(11)

La requête en question :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SELECT SQL_CALC_FOUND_ROWS 
property_id, property_reference, property_property_type_id, property_property_type_sale_id,
property_property_housing_id, property_price_type_id, property_price, property_unified_price,
property_sqft, property_agent_id, property_bedroom_id, property_loc_community_id,
property_loc_subcommunity_id, property_loc_tower_id, property_nbimages, property_published, property_date_creation,
property_deleted, agent_name, property_type_name, property_type_sale_name, property_housing_name,
bedroom_name, loc_city_name, loc_community_name, loc_subcommunity_name,
loc_tower_name, price_type_name, property_property_showstyle_id, property_showstyle_name,
IFNULL(SUM(stats_property_history_visits),0) AS visits, IFNULL(SUM(stats_property_history_phones),0) AS phones, 
IFNULL(SUM(stats_property_history_emails),0) AS emails, IFNULL(SUM(stats_property_history_offers),0) AS offers, 
IFNULL(SUM(stats_property_history_leads),0) AS leads, IFNULL(SUM(stats_property_history_impressions),0) AS impressions, 
IFNULL(SUM(stats_property_history_visits)/SUM(stats_property_history_impressions),0) AS ctr1, 
IFNULL(SUM(stats_property_history_leads)/SUM(stats_property_history_visits),0) AS ctr2, 
IFNULL(AVG(stats_property_history_property_ranking_total),0) AS ranking_total, 
IFNULL(MAX(stats_property_history_property_showstyle_level),1) AS property_showstyle_level
FROM stats_property_history 
LEFT JOIN property p ON stats_property_history_property_id = property_id 
LEFT JOIN property_type ON property_type_id = property_property_type_id 
LEFT JOIN property_type_sale ON property_type_sale_id = property_property_type_sale_id 
LEFT JOIN property_housing ON property_housing_id = property_property_housing_id 
LEFT JOIN price_type ON price_type_id = property_price_type_id 
LEFT JOIN agent ON agent_id = property_agent_id 
LEFT JOIN bedroom ON bedroom_id = property_bedroom_id 
LEFT JOIN loc_community ON loc_community_id = property_loc_community_id 
LEFT JOIN loc_city ON loc_city_id = loc_community_loc_city_id 
LEFT JOIN loc_subcommunity ON loc_subcommunity_id = property_loc_subcommunity_id 
LEFT JOIN loc_tower ON loc_tower_id = property_loc_tower_id 
LEFT JOIN property_showstyle ON property_showstyle_id = property_property_showstyle_id 
WHERE stats_property_history_date BETWEEN '2011-03-22' AND '2011-04-22' 
AND stats_property_history_property_type_id = '1' 
AND stats_property_history_property_type_sale_id = '1' 
AND (stats_property_history_property_housing_id = '1'
AND (stats_property_history_agent_id = '1'
AND stats_property_history_loc_city_id = '1' 
AND (stats_property_history_loc_community_id = '1'
AND (stats_property_history_loc_subcommunity_id = '1'
AND (stats_property_history_loc_tower_id = '1'
GROUP BY stats_property_history_property_id 
ORDER BY leads DESC 
LIMIT 0, 100;

J'ai créé un index sur les colonnes

stats_property_history_date
stats_property_history_property_type_id
stats_property_history_property_type_sale_id
stats_property_history_property_housing_id
stats_property_history_agent_id
stats_property_history_loc_city_id
stats_property_history_loc_community_id
stats_property_history_loc_subcommunity_id
stats_property_history_loc_tower_id
stats_property_history_property_id
stats_property_history_visits
stats_property_history_phones
stats_property_history_emails
stats_property_history_offers
stats_property_history_leads
stats_property_history_impressions

Pour infos, je me suis arrêté à stats_property_history_impressions du fait de la limite des 16 champs par index imposé par Mysql.

Toutes les colonnes xxx_id sont des clés étrangères.

Avec cet index, ma requête met 30 secondes à s'exécuter, et un explain me donne l'horrible "Using where; Using temporary; Using filesort"

J'ai cherché pas mal d'infos sur Internet sur l'optimisation des requêtes avec Group By et opérations afin qu'elles utilisent un index, mais j'ai rien trouvé de vraiment concret.

J'espère que quelqu'un saura m'aider.

[SQLpro]

Il est normal que :

• votre requête ne soit pas optimisable
• vous n'ayez rien trouvez sur le net

parce que votre table n'est pas une table, c'est un fichier COBOL !

Je me marre toujours à votre des horreur pareilles. Connaissez vous la modélisation des données et notamment les formes normales ?

Si tel était le cas, vous n'auriez pas fait une telle table fourre tout et vos performances serait optimale (quelques ms).

A lire : http://blog.developpez.com/sqlpro/p1...ances-petites/

A +