Optimisation des performances MySQL: ordre par champ datetime

Question

J'ai une table avec environ 100.000 billets de blog, liée à une table avec 50 alimente via un 1:n relation. Quand j'ai une requête à la fois des tables avec une instruction select, commandé par un champ date / heure de l'affichage de la table, MySQL utilise toujours filesort, entraînant une très lent temps de requête (>1 seconde). Voici le schéma de l' postings tableau (simplifié):

+---------------------+--------------+------+-----+---------+----------------+
| Field               | Type         | Null | Key | Default | Extra          |
+---------------------+--------------+------+-----+---------+----------------+
| id                  | int(11)      | NO   | PRI | NULL    | auto_increment |
| feed_id             | int(11)      | NO   | MUL | NULL    |                |
| crawl_date          | datetime     | NO   |     | NULL    |                |
| is_active           | tinyint(1)   | NO   | MUL | 0       |                |
| link                | varchar(255) | NO   | MUL | NULL    |                |
| author              | varchar(255) | NO   |     | NULL    |                |
| title               | varchar(255) | NO   |     | NULL    |                |
| excerpt             | text         | NO   |     | NULL    |                |
| long_excerpt        | text         | NO   |     | NULL    |                |
| user_offtopic_count | int(11)      | NO   | MUL | 0       |                |
+---------------------+--------------+------+-----+---------+----------------+

Et voici l' feed tableau:

+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int(11)      | NO   | PRI | NULL    | auto_increment |
| type        | int(11)      | NO   | MUL | 0       |                |
| title       | varchar(255) | NO   |     | NULL    |                |
| website     | varchar(255) | NO   |     | NULL    |                |
| url         | varchar(255) | NO   |     | NULL    |                |
+-------------+--------------+------+-----+---------+----------------+

Et voici la requête qui prend >1 seconde à exécuter. Veuillez noter que l' post_date champ a un indice, mais MySQL n'est pas à l'utiliser pour trier les écritures de la table:

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    (`postings`)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

Le résultat de l' explain extended commande sur cette requête indique que MySQL à l'aide de filesort:

+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
| id | select_type | table    | type   | possible_keys                         | key       | key_len | ref                      | rows  | Extra                       |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+
|  1 | SIMPLE      | postings | ref    | feed_id,is_active,user_offtopic_count | is_active | 1       | const                    | 30996 | Using where; Using filesort |
|  1 | SIMPLE      | feeds    | eq_ref | PRIMARY,type                          | PRIMARY   | 4       | feedian.postings.feed_id |     1 | Using where                 |
+----+-------------+----------+--------+---------------------------------------+-----------+---------+--------------------------+-------+-----------------------------+

Lorsque je retire l' order by partie, MySQL cesse à l'aide de filesort. S'il vous plaît laissez-moi savoir si vous avez des idées sur la manière d'optimiser cette requête pour obtenir MySQL afin de trier et de sélectionner les données à l'aide des index. J'ai déjà essayé quelques petites choses comme la création d'un indice combiné sur tous où/de la commande par les champs, comme l'ont suggéré quelques billets de blog, mais cela ne fonctionne pas non plus.

Answer 1

Créer un indice composite, soit sur l' postings (is_active, post_date) (dans cet ordre).

Il sera utilisé à la fois pour le filtrage sur is_active et de commande par post_date.

MySQL devrait montrer REF méthode d'accès sur cet indice en EXPLAIN EXTENDED.

Notez que vous avez un RANGE conditions de filtrage sur user_offtopic_count, c'est pourquoi vous ne pouvez pas utiliser un index sur ce champ à la fois dans le filtrage et le tri par d'autres champ.

En fonction de la sélectivité est votre user_offtopic_count (j'. e. combien de lignes de satisfaire user_offtopic_count < 10), il pourrait être plus utile de créer un index sur user_offtopic_count et de laisser le post_dates être triés.

Pour ce faire, créez un index composite sur postings (is_active, user_offtopic_count) et assurez-vous que l' RANGE méthode d'accès plus cet indice est utilisé.

L'index sera plus rapide dépend de vos données distribuion. Créer deux index, FORCE et de voir qui est plus rapide:

CREATE INDEX ix_active_offtopic ON postings (is_active, user_offtopic_count);
CREATE INDEX ix_active_date ON postings (is_active, post_date);

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_offtopic)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show RANGE access with few rows and keep the FILESORT */

SELECT 
    `postings`.`id`, 
    UNIX_TIMESTAMP(postings.post_date) as post_date, 
    `postings`.`link`, 
    `postings`.`title`, 
    `postings`.`author`, 
    `postings`.`excerpt`, 
    `postings`.`long_excerpt`, 
    `feeds`.`title` AS feed_title, 
    `feeds`.`website` AS feed_website
FROM 
    `postings` FORCE INDEX (ix_active_date)
JOIN 
    `feeds` 
ON 
    `feeds`.`id` = `postings`.`feed_id`
WHERE 
    `feeds`.`type` = 1 AND 
    `postings`.`user_offtopic_count` < 10 AND 
    `postings`.`is_active` = 1
ORDER BY 
    `postings`.`post_date` desc
LIMIT 
    15

/* This should show REF access with lots of rows and no FILESORT */

Answer 2

MySQL a deux filesort algorithmes: une ancienne filesort qui trie les enregistrements sur le disque, et une nouvelle version qui fonctionne dans la mémoire.

Si elle ne peut pas utiliser un index sur la première table de la jointure pour trier la requête, il devra faire un filesort. Si le jeu de résultats avant de les trier converti à largeur fixe le format est plus grand que le buffer de tri, OU si elle contient des champs de texte, il faut utiliser le plus lent sur disque filesort algorithme (la deuxième condition est satisfaite puisque votre requête a un champ de texte).

MySQL est de choisir d'utiliser le is_active colonne, soi-disant parce qu'il pense que la colonne est plus sélective, en éliminant les lignes avant de poursuivre avec l'autre le rejoint et où les conditions de travail. La première chose que je dirais serait d'essayer de créer des index composites avec post_date, feed_id, et les colonnes dans la condition where, par exemple (is_active, user_offtopic_count, post_date, feed_id).

Answer 3

De plus, il est important de se rappeler que MySQL n'utilisera pas d'index si la colonne que vous commandez a une fonction qui lui est appliquée.

Vous devriez également essayer d'aliasing postings.post_date comme autre chose. Cela indiquera à MySQL de classer par la colonne non modifiée, et vous sélectionnerez toujours l'horodatage unix.