Nous avons une table myisam avec environ 75 lignes de millets qui possèdent 5 colonnes: Nous avons un index principal sur la colonne ID, un index unique (user_id, date_id_id_crated) et Un index composite (page_id, date_created) p> Le problème est que la requête ci-dessous prend jusqu'à 90 secondes pour terminer p> C'est l'explication de cette Query P> SELECT SQL_NO_CACHE user_id, count(*) nr FROM `table` USE INDEX (usridexp) WHERE `page_id`=301 and `date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' AND page_id<>user_id group by `user_id` ORDER BY NULL
+----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
| 1 | SIMPLE | table | ref | usridexp | usridexp | 4 | const | 3943444 | Using where; Using index |
+----+-------------+----------------------------+------+---------------+----------+---------+-------+---------+--------------------------+
3 Réponses :
1) Cela dépend de vos données - mais vous devez disposer de plusieurs index disponibles pour permettre à MySQL de choisir le meilleur. par exemple. Si la table avait un index sur la page_ID, il ne scrutait pas tant de lignes.
2) Il existe un moyen d'optimiser les recherches de la date. Je n'ai pas encore mis en œuvre cela moi-même, mais j'ai un problème similaire que j'ai pensé à peu près.
Fondamentalement, vous recherchez des données de jour - mais la date de comparaison est vraiment lente. Ce que vous pourriez faire est de créer une autre table qui stocke des identifiants les plus anciens et les plus récents de table pour chaque jour. Cette table devrait être peuplée à la fin de chaque journée.
Après cela, vous pouvez casser votre requête en deux parties:
i) Trouvez les identifiants pour rechercher y en cours d'exécution de deux requêtes: Sélectionnez DoindIstid à partir d'IDCachetable où date = '2012-01-03'; Sélectionnez Date d'IDCachetable lorsque Date = '2012-02-03';
ii) Vous pouvez ensuite rechercher directement sur la clé primaire de la table, sans effectuer une date de comparaison de chaque ligne, ce qui serait plus rapide de Waaaaaaay.
Sélectionnez SQL_NO_CACHE User_ID, comptez (ID) NR
De La solution exacte à votre problème dépendra de ce que vos données ressemblent à cependant, plutôt que d'une de ces deux choses étant toujours correctes. table
Où page_id = 301
et (id> = DOEDIESTID et ID <= DREETID)
Et page_id <> user_id
Groupe par user_id ;
Je ne pense pas que le problème est un index maintenant. Fondamentalement, MySQL utilise à la fois la page_ID et la date d'index. Je le sais car avant que nous n'ayant pas eu d'indice composite et que le nombre de lignes retournées serait d'environ 4 milles, et également en regardant la longueur de la clé, vous pouvez le voir utiliser les deux champs. La requête est assez rapide pour une table cette grosse pages avec quelques entrées à la plage de dates.
Ah, désolé a manqué qu'il utilisait déjà un index sur page_ID. "La requête est assez rapide pour une table cette grosse pages avec quelques entrées à la plage de la date." Oui, mais en fonction de vos données, vous pouvez probablement examiner beaucoup moins de lignes, en utilisant un comparateur beaucoup plus rapide que celui de la date, si vous savez à quoi le premier identifiant de clé principale et le dernier identifiant de clé primaire de «Table» pourrait être.
Quelques modifications susceptibles d'améliorer la requête:
Changement Ajouter un Le Une autre chose n'est pas liée à la performance de cette requête:
Si votre Nombre (ID) à compte (*) . Etant donné que ID est (je suppose) la clé primaire et non NULL , les résultats seront identiques.
Commander par null après ther groupe par clause. Dans MySQL, un groupe par opération trie également les résultats, sauf si vous spécifiez d'autres sages.
(page_id, date_created) est probablement le meilleur indice que MySQL peut utiliser pour cette requête, mais vous pouvez également essayer (page_id, user_id, date_crated) (Pouvez-vous aussi poster l'expliquer si vous ajoutez cet index?)
(user_id, page_id, date_created) est unique et l'identifiant est généré automatiquement (et non utilisé pour autre chose que Une clé primaire), vous pouvez en faire la clé primaire et déposer la colonne ID . Un index de moins et 4 octets moins par rangée.
Merci! L'index que vous avez suggéré de gagner beaucoup de choses. J'ai mis à jour la question avec l'explication que vous avez obligée. En forçant l'indice que j'ai réussi à le faire effectuer sous 0,1 seconde et sans le forcer juste sous 1s. Pensez-vous que cela affectera nos moments d'insertion si nous laissons les deux index? Nous exécutons 3 insertions une fois par jour qui sélectionnent des données de 3 tables.
Avoir de nombreux indices peut affecter les instructions d'insertion / de suppression / mise à jour des performances. Gardez les indices dont vous avez besoin pour vos requêtes / déclarations et vos tests si cela affecte les insertions massives inacceptables.
Le nombre (1) serait probablement meilleur que le nombre (*)
@Jeffk pas vraiment. Ils devraient produire des plans identiques.
sonne impair, mais essayez d'ajouter une déclaration de jointure:
SELECT SQL_NO_CACHE user_id, count(id) nr FROM `table` t JOIN `table` t2 ON t.`user_id`= t2.`user_id` WHERE t.`page_id`=301 and t.`date_created` BETWEEN '2012-01-03' AND '2012-02-03 23:59:59' AND t.`page_id`<>t.`user_id` group by t.`user_id`
Quel est cet étrange
page_id <> user_id conditionner?Vous pouvez modifier le note
(ID) NR surcompter (*) nr . Cela améliorera légèrement les performances.@YPERCUBE La table stocke des interactions entre utilisateurs et pages, mais les pages peuvent également interagir avec eux-mêmes. Avec cette requête, je souhaite voir combien de fois chaque utilisateur a interagi avec la page, à l'exception de la page elle-même. Je l'ai supprimé sans m'empêcher d'améliorations. Le comte (*) semble améliorer les performances un peu, mais à partir de ce que j'ai vu uniquement sur des pages avec moins d'entrées dans la plage. Merci!
D'autres choses que vous voudrez peut-être vérifier sont les paramètres de mémoire tampon. Si les données peuvent être maintenues en mémoire (pour la tri du groupe), sans la table TEMP frappant le disque, elle vous aidera.