MySQL Lent sur rejoindre. De toute façon à accélérer
J'ai 2 tables. 1 la musique et de la 2 est listenTrack. listenTrack pistes uniques joue de chaque chanson. Je suis en train d'obtenir des résultats pour les chansons populaires du mois. Je reçois mes résultats mais ils sont tout simplement trop long. Ci-dessous mes tables et de requêtes
De 430 000 lignes
CREATE TABLE `listentrack` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`sessionId` varchar(50) NOT NULL,
`url` varchar(50) NOT NULL,
`date_created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`ip` varchar(150) NOT NULL,
`user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=731306 DEFAULT CHARSET=utf8
12500 lignes
CREATE TABLE `music` (
`music_id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`title` varchar(50) DEFAULT NULL,
`artist` varchar(50) DEFAULT NULL,
`description` varchar(255) DEFAULT NULL,
`genre` int(4) DEFAULT NULL,
`file` varchar(255) NOT NULL,
`url` varchar(50) NOT NULL,
`allow_download` int(2) NOT NULL DEFAULT '1',
`plays` bigint(20) NOT NULL,
`downloads` bigint(20) NOT NULL,
`faved` bigint(20) NOT NULL,
`dateadded` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`music_id`)
) ENGINE=MyISAM AUTO_INCREMENT=15146 DEFAULT CHARSET=utf8
SELECT COUNT(listenTrack.url) AS total, listenTrack.url
FROM listenTrack
LEFT JOIN music ON music.url = listenTrack.url
WHERE DATEDIFF(DATE(date_created),'2009-08-15') = 0
GROUP BY listenTrack.url
ORDER BY total DESC
LIMIT 0,10
cette requête n'est pas très complexe et les lignes ne sont pas trop grandes, je ne pense pas.
Est-il un moyen pour accélérer le processus? Ou pouvez-vous suggérer une meilleure solution? Cela va être une tâche cron au début de chaque mois, mais je tiens également à le faire par les résultats de la journée ainsi.
Oh btw, je suis en cours d'exécution au niveau local, plus de 4 min à exécuter, mais sur la prod, il faut environ 45 secondes
Vous devez vous connecter pour publier un commentaire.
Je suis de plus en plus d'un Serveur SQL server gars, mais ces concepts devraient s'appliquer.
J'aimerais ajouter des index:
Ces indices devraient vitesse de la requête jusqu'énormément (j'ai d'abord eu les noms de table mélangées - corrigé dans la dernière édition).
Pour la plupart, vous devriez également l'indice de la colonne qui est utilisé dans une JOINTURE. Dans votre cas, vous devriez index
listentrack.url
etmusic.url
@jeff s - Un indice de la musique.date_created pas aider parce que vous êtes en cours d'exécution à travers une fonction d'abord de sorte que MySQL ne peut pas utiliser un index sur cette colonne. Souvent, vous pouvez réécrire une requête afin que la indexé colonne référencée est utilisé de manière statique comme:
devient
Cela permettra de filtrer les enregistrements qui sont de 2009-08-15 et de permettre à tous les index sur cette colonne pour être candidats. Notez que MySQL ne pourraient PAS utiliser cet index, il dépend d'autres facteurs.
Votre meilleur pari est de faire un double index sur
listentrack(url, date_created)
et puis un autre indice sur
music.url
Ces 2 indices couvrira cette requête particulière.
Notez que si vous exécutez
EXPLAIN
sur cette requête, vous allez toujours obtenir unusing filesort
parce qu'il doit écrire les enregistrements à une table temporaire sur le disque pour faire la COMMANDE PAR.En général, vous devez toujours exécuter votre requête en vertu de l'
EXPLAIN
pour avoir une idée sur comment MySQL va exécuter la requête, puis à partir de là. Voir laEXPLAIN
documentation:http://dev.mysql.com/doc/refman/5.0/en/using-explain.html
Essayez de créer un index qui va aider à la rejoindre:
Je pense que je pourrais avoir manqué à l'évidence avant. Pourquoi vous joindre à la musique de table à tous? Vous ne semblent pas être à l'aide de données dans la table et que vous effectuez une jointure gauche qui n'est pas nécessaire, à droite? Je pense que cette table dans la requête sera beaucoup plus lent et ne sera pas ajouter de la valeur. Prendre toutes les références à la musique, à moins que l'url de l'inclusion est nécessaire, dans ce cas vous avez besoin d'un droit de se joindre à la force de ne pas inclure une ligne sans une valeur correspondante.
Je voudrais ajouter de nouveaux indices, comme les autres le mentionner. Plus précisément, je voudrais ajouter:
musique url
listentrack date_created,url
Cela permettra d'améliorer votre adhérer à une tonne.
Alors que j'allais le chercher à la requête, vous êtes en forçant le système à effectuer des travaux sur chaque ligne de la table. Il serait préférable de reformuler la restriction de date comme un éventail.
Pas sûr de la syntaxe sur le dessus de ma tête:
où "2009-08-15 00:00:00' <= date_created < 2009-08-16 00:00:00
Qui devrait lui permettre de rapidement utiliser l'index pour localiser les documents appropriés. La combinaison de deux index de clé sur la musique devrait lui permettre de trouver les enregistrements basés sur la date et l'URL. Vous devriez expérimenter, ils pourraient être mieux de passer dans l'autre sens url,date_created sur l'index.
L'expliquer plan de cette requête devrait dire "à l'aide de l'indice" sur la colonne de droite pour les deux. Cela signifie qu'il n'aura pas à frapper les données dans le tableau afin de calculer vos sommes.
Je voudrais aussi vérifier les paramètres de la mémoire que vous avez configuré pour le serveur MySQL. Il semble que vous n'avez pas assez de mémoire allouée. Être très prudent sur les différences entre le serveur de base de paramètres et de fil en fonction des paramètres. Le serveur avec 10 MO de cache est assez petit, un thread avec 10 MO de cache peut utiliser beaucoup de mémoire rapidement.
Jacob
Pré-regroupement et puis en rejoignant rend les choses beaucoup plus vite avec MySQL/MyISAM. (Je me méfie moins de ce qui est nécessaire avec d'autres de la DB)
Cela devrait effectuer à peu près aussi rapide que la non-jointes version:
P. S. - mise en correspondance entre les deux tables avec un id au lieu d'une url est de bons conseils.
Pourquoi êtes-vous répéter l'url dans les deux tables?
Ont listentrack tenir une music_id au lieu de cela, et de le rejoindre sur ce point. Se débarrasser de la recherche de texte ainsi que des index.
En outre, il est sans doute plus correct. Vous effectuez le suivi de la fois que la piste a été écouté, pas l'url. Que faire si l'url change?
Après vous d'ajouter des index, alors vous voudrez peut-être explorer l'ajout d'une nouvelle colonne pour le date_created être un unix_timestamp, qui permettra de faire des opérations mathématiques plus rapide.
Je ne suis pas certain pourquoi vous avez la diff de la fonction si, comme il semble que vous êtes à la recherche pour toutes les lignes qui ont été mis à jour à une date donnée.
Vous voudrez peut-être chercher à votre requête, car il semble avoir une erreur.
Si vous utilisez des tests unitaires, alors vous pouvez comparer les résultats de votre requête et d'une requête à l'aide d'un timestamp unix à la place.
vous voudrez peut-être ajouter un index sur le champ d'adresse url de deux tables.
avoir dit que, lorsque je me suis converti à partir mysql, sql server 2008, avec les mêmes requêtes et les mêmes structures de base de données, les requêtes couru 1-3 ordres de grandeur plus rapide.
je pense que cela avait à voir avec le sgbdr (mysql optimiseurs sont pas si bon...) et qu'il pourrait avoir à faire avec la façon dont le sgbd système de la réserve de ressources. bien que les comparaisons ont été faites sur des systèmes de production où seule la db irait.
Ci-dessous serait probablement travailler à la vitesse de la requête.
CRÉER des INDEX music_url_index SUR la musique (url) à l'AIDE d'ARBRE;
CRÉER des INDEX listenTrack_url_index SUR listenTrack (url) à l'AIDE de BTREE;
Vous avez vraiment besoin de savoir le nombre total de comparaisons et de la ligne des scans qui sont en train d'arriver. Pour obtenir cette réponse, regarde le code ici de la façon de faire à l'aide d'expliquer http://www.siteconsortium.com/h/p1.php?id=mysql002.