J'ai une table de 3 666 058 enregistrements et 6 colonnes, définies comme suit:
CREATE TABLE IF NOT EXISTS `annoyance` (
`a` varchar(50) NOT NULL default '',
`b` varchar(50) NOT NULL default '',
`c` longtext,
`d` varchar(21) NOT NULL,
`e` float default NULL,
`f` smallint(6) NOT NULL default '0',
KEY `ab` (`a`,`b`),
KEY `b` (`b`),
KEY `d` (`d`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
J'essaie de récupérer le contenu des colonnes a , b et d , lorsque a commence par un certain préfixe (3 lettres de long), soit aaa . Donc, j'exécute la requête suivante: SELECT a, b, c de l'ennui où un comme 'aaa%'; . Cela devrait récupérer environ 1 835 000 enregistrements de la table.
Mon problème est: cette requête est très lente (lorsqu'elle n'est pas mise en cache bien sûr), et prend parfois jusqu'à quelques minutes.
Alors, comment puis-je accélérer les choses pour cette requête particulière? Simething j'ai essayé mais sans succès est de créer un index sur un a (taille 3 ou sans spécifier de taille): MySQL ne prendrait même pas la peine d'utiliser l'index sauf si je le forcerais avec FORCE INDEX (indices d'index) et cela n'a pas semblé accélérer l'exécution de la requête.
3 Réponses :
Pour récupérer 1,8 million de lignes sur 3,6 millions, il faut essentiellement analyser la table entière. Vous ne pouvez pas faire grand-chose pour améliorer les performances.
Les index n'aideront pas. Si vous récupériez, disons 1000 lignes de la table, les index peuvent vous aider. Et, un index sur a serait utilisé pour le like . Vous pouvez également formuler ceci comme suit:
where a >= 'aaa' and a < 'aab'
Si vous vouliez que l'optimiseur choisisse encore plus facilement l'index.
Avez-vous essayé LEFT () Selon ce test, il est plus rapide que LIKE. http://cc.davelozinski.com/sql/ like-vs-substring-vs-leftright-vs-charindex
SELECT a,b,c from annoyance where LEFT(a,3) = 'aaa'
LEFT ne permettra pas d’utiliser INDEX (a) . En règle générale: le masquage d'une colonne indexée dans une fonction empêche l'utilisation de cet index.
Cette référence n'est pas valide pour MySQL car elle fait référence à SQL Server.
En outre, cette référence vérifie les deux extrémités de la chaîne pour la sous-chaîne donnée; c'est plus complexe que ce que demande le PO.
INDEX (a, b, d) (ou (a, d, b) ) s'exécuterait plus rapidement car ce serait un index "couvrant".
(Remplacez d par c si c est vraiment ce que vous cherchez.)
Je suggérerais de repenser l'ensemble de l'approche, en commençant par «pourquoi avez-vous besoin de récupérer rapidement 1,8 million de lignes?».