Sous-requête pour rassembler des sous-ensembles groupés avec des limites

Question

J'essaie de trouver un moyen de rassembler un ensemble de données sans avoir à boucler 700 000 requêtes mysql.

J'ai deux tableaux

users avec

id autoincrement, 
time timestamp, 
username varchar(200), 
email varchar(100), 
ip varchar(20)

y uniq_ip avec

ip unique varchar(20), 
most_recent datetime, 
count (int)

users compte 25 millions de lignes et enregistre l'activité des utilisateurs lorsqu'ils travaillent sur le site. uniq_ip contient une liste de tous les numéros d'IP et le nombre de fois qu'ils sont répertoriés dans les utilisateurs (lors de la mise à jour des déclencheurs).

En ce moment, en codant en rêve, j'obtiens une liste de toutes les IPs de uniq_ip et les mettre en boucle pour obtenir les 2000 enregistrements les plus récents pour chacune de ces IP. Comme uniq_ip a 700 000 lignes, cette boucle est vraiment méchante, faisant 700 000 requêtes au total, utilisant

select * from users where ip = '$outerloopip' order by `time` desc limit 2000;

J'essaie d'obtenir une requête unique qui récupère les 2000 derniers listings pour chacune des IPs. Si 1.2.3.4 est listé 10 000 fois, je veux juste les 2000 plus récents, basés sur le champ de temps.

Avez-vous une idée de la façon de le faire en une seule requête ?

Answer 1

Je suis désolé pour la réponse précédente et j'ai relu et appliqué la requête mise à jour. Je me suis trompé et j'ai pensé que vous vouliez seulement les 2000 adresses IP les plus récentes. Quoi qu'il en soit, cette recherche porte sur TOUTES les adresses IP et limite le nombre total d'enregistrements par IP à 2 000, les plus récents figurant en tête de liste. Je m'assurerais que vous avez un index sur

(IP,TIME DESC)

Ensuite, essayez cette requête. La chose critique que j'ai oublié de clarifier. La clause HAVING est appliquée APRÈS toute clause group-by ou order-by. Donc les données sont pré-remplies dans l'ordre approprié de l'adresse IP et de la date/heure en ordre décroissant, puis les variables @sql sont appliquées. Une fois que l'enregistrement est qualifié et prêt à être ajouté à l'ensemble des résultats finaux, la clause HAVING est appliquée. À ce moment-là, il regarde le compteur de séquences et dit... s'il est supérieur à 2000, il faut le jeter et passer à l'enregistrement suivant.

D'après ma requête initiale, il sauvegardait tout, puis passait en revue une deuxième fois et éliminait les données supérieures à 2000, ce qui explique probablement pourquoi l'espace disque était saturé.

select
      U.*,
      @LastSeq := IF( @LastIP = U.IP, @LastSeq +1, 1 ) as IPSequence,
      @LastIP := U.IP as carryForNextRecord
   from 
      ( select @LastIP := '', @LastSeq := 0 ) sqlvars,
      Users U
   order by
      U.IP,
      U.time DESC
   having 
      IPSequence <= 2000

Answer 2

SELECT ip as IP ,
     (SELECT GROUP_CONCAT(time)     FROM users WHERE ip = IP ORDER BY time DESC LIMIT 2000) as Time,
     (SELECT GROUP_CONCAT(username) FROM users WHERE ip = IP ORDER BY time DESC LIMIT 2000) as UserName,
     (SELECT GROUP_CONCAT(email)    FROM users WHERE ip = IP ORDER BY time DESC LIMIT 2000) as Email
FROM uniq_id