Comment optimiser un agrégat SQL ?

Question

Supposons que le schéma de la table soit le suivant :

name amount_1, amount_2, cond_1, cond_2

la table compte plus de 500 000 lignes.

Comment optimiser les requêtes comme :

select 
  name
  , sum(amount_1) as total_1
  , sum(amount_2) as total_2
  , sum(amount_1+amount_2) as total 
from table_name 
where cond_1 in ('a', 'b') 
group by name 
order by total desc 
limit 10;

L'exécution du code SQL actuel prendra plusieurs minutes.

Answer 1

Il est probable qu'il manque des index sur ces colonnes :

• name pour le groupement
• cond_1 pour le filtrage

Si vous préférez n'avoir qu'un index composé, je vous recommande d'en faire un index sur (cond_1, name) (et non l'inverse). Pourquoi ? cond_1 doit être la première colonne de l'index composé, car le prédicat de filtrage ne peut profiter que des colonnes situées à gauche d'un index.

Ceci est expliqué ici, par exemple : http://use-the-index-luke.com/sql/where-clause/the-equals-operator/concatenated-keys

Answer 2

L'indexation vous aidera, et vous pourrez réécrire légèrement la requête :

SELECT
  s.*, s.total_1 + s.total_2 AS total
FROM (
  SELECT
    name
    , SUM(amount_1) as total_1
    , SUM(amount_2) as total_2
  FROM table_name 
  WHERE cond_1 in ('a', 'b') 
  GROUP BY name ) s
ORDER BY total DESC 
LIMIT 10;

Cela évitera de faire la somme de (total_1 + total_2) sur toutes les lignes, mais réutilisera les totaux calculés dans la rubrique total_1 y total_2 .

Vous pouvez également essayer cette variante, qui peut être plus lente ou plus rapide :-).
Si vous avez un index sur amount1 et amount2 et qu'une fraction substantielle, mais pas énorme, est 0, cela peut être beaucoup plus rapide.

SELECT
  s.*, s.total_1 + s.total_2 AS total
FROM (
  SELECT
    name
    , SUM(amount_1) as total_1
    , SUM(amount_2) as total_2
  FROM table_name 
  WHERE cond_1 in ('a', 'b') AND (amount_1 <> 0 AND amount_2 <> 0)
  GROUP BY name ) s
ORDER BY total DESC 
LIMIT 10;

Si vous avez beaucoup de choses dans le IN il peut être plus rapide de faire

WHERE cond_1 BETWEEN 'a' AND 'z'

Cela permet d'échanger 26 tests OR contre 2 tests AND.

Answer 3

A l'exception des index mentionnés par Lukas Eder, je suppose que l'un des sum n'est pas nécessaire, car si amount_1 y amount_2 sont NOT NULL vous aurez alors l'équation

total = total_1 + total_2

Ainsi, si vous avez besoin de total pour l'ordonnancement, vous pouvez perdre le total_2 et le calculer après l'exécution de la requête comme suit (total - total_1)

Je ne sais pas si cela vous fera gagner du temps, mais cela me semble être une petite optimisation.

Answer 4

Si c'est une partie importante de votre activité, envisagez l'OLAP, c'est le moyen le plus rapide que je connaisse