Eenvoudige MySQL-query duurt meer dan twee uur

Dus ik heb een database met 5 miljoen records en deze slaat de informatie op voor een vraag- en antwoordsite. De structuur is ...

question, qid, quserid, answer, auserid;

(qid is het id-nummer voor de vraag)

Ik probeer alle gebruikers te vinden die vragen hebben die NIET door een bepaalde gebruiker zijn beantwoord en deze te sorteren op het aantal vragen dat niet door een bepaalde gebruiker is beantwoord. Hier is mijn vraag:

SELECT quserid, COUNT(quserid)
FROM `qanda` 
WHERE qid NOT IN (SELECT qid FROM `qanda` WHERE auserid='myusername' GROUP BY qid) 
GROUP BY quserid 
ORDER BY COUNT(quserid) DESC 
LIMIT 0,1000;

Het probleem: het duurt meer dan 2 uur en de klok tikt nog steeds weg! Iedereen weet hoe deze database te versnellen? Ik denk dat er een fout zit in de database of zoiets, meestal duurt het maar 30 seconden voor een eenvoudige zoekopdracht, dus dit wordt een beetje belachelijk. Iedereen kent eventuele tweaks? Misschien een simpele wijziging in het configuratiebestand of zoiets?

..........

Hier zijn wat gegevens uit de database die ik zojuist heb gekopieerd en geplakt. Sorry voor het gebrek aan opmaak.

you could have any one person in the entire wor... greendaystud    ive got the person i want...its great...because sh...  •glitter•rock•  191437  If you could have any one person in the entire wor...  just~another~slave2tears    i already got em
                            •glitter•rock•  191437  If you could have any one person in the entire wor...  korn_chick2007  matt or chris...i have feelings for them
                            •glitter•rock•  189555  why are you so sexy?
    just~another~slave2tears    my b/f says i am...i dun tink so tho
                            •glitter•rock•  189555  why are you so sexy?
    korn_chick2007  im not
                            •glitter•rock•  189555  why are you so sexy?
    MyKool-AidsSexy     i dont think i am
                            †brokengirl†    115228  If you are supposed to expect the unexpected,
doe... death-tone  yip
                            †brokengirl†    115228  If you are supposed to expect the unexpected,
doe... _doieverknowwhoiam_     you know whats weird? my friend sandy says that a ...
                            †brokengirl†    115228  If you are supposed to expect the unexpected,
doe... Cute_Physco_kitty   Pretty much..
                            †brokengirl†    115228  If you are supposed to expect the unexpected,
doe... Leslie02    WHAT! OK, now im confused!
                            †brokengirl†    114995  Why does my listerine taste like sausage this
mor... death-tone  what's listerine?
                            †brokengirl†    114995  Why does my listerine taste like sausage this
mor... _doieverknowwhoiam_     i don't know, and maybe it's jut me bu...
                            †brokengirl†    114995  Why does my listerine taste like sausage this
mor... darksunofdeath  How old is the listerine pack?
                            †brokengirl†    114995  Why does my listerine taste like sausage this
mor... Cute_Physco_kitty   uhh...New brand of Listerine?
    †brokengirl†    114995  Why does my listerine taste like sausage this
mor... Leslie02    did you have sausage for breakfast?     †brokengirl†    104305  What should I name my pinky toe on my left
foot?¿...  death-tone  "Pinkytoe"        

En, de verwachte output, met handige kolomtitels ...

Questioner User ID | Number of questions asked by the Questioner that were unanswered by 'myuserid'

Greenbay Packer | 6
DollyDoll | 63
PsychoticPokemon | 62
HelloKitty | 61
GreenDayFan | 60

...

IDontAskManyQuestion | 2
WhatsAQuestion? | 1

En hier is de EXPLAIN-uitgang

> mysql-> EXPLAIN
>     ->
>     -> SELECT quserid, COUNT(quserID)
>     -> FROM `qanda`
>     -> WHERE qid NOT IN (SELECT qid FROM `qanda` WHERE auserid='boxocereal' GROU P BY qid)
>     -> GROUP BY quserid
>     -> ORDER BY COUNT(quserid) DESC
>     -> LIMIT 0,1000;
> 

+----+--------------------+-------+------+---------------+------+---------+-----
> -+---------+----------------------------------------------+ | id | select_type        | table | type | possible_keys | key  | key_len |
> ref  | rows    | Extra                                        |
> +----+--------------------+-------+------+---------------+------+---------+-----
> -+---------+----------------------------------------------+ |  1 | PRIMARY            | qanda | ALL  | NULL          | NULL | NULL    |
> NULL  | 3167995 | Using where; Using temporary; Using filesort | |  2
> | DEPENDENT SUBQUERY | qanda | ALL  | NULL          | NULL | NULL    |
> NULL  | 3167995 | Using where; Using temporary; Using filesort |
> +----+--------------------+-------+------+---------------+------+---------+-----
> -+---------+----------------------------------------------+ 2 rows in set (0.02 sec)
> 
> mysql->
0
@Interrobang dat kan onmogelijk waar zijn ... echt ??
toegevoegd de auteur McGarnagle, de bron
@Interrobang Ik weet dat MySql gratis is en allemaal, maar misschien moeten ze beginnen met opladen en alleen het geld om een ​​paar ontwikkelaars in te huren of zoiets ... gewoon zeggen
toegevoegd de auteur McGarnagle, de bron
@ NathanCantos: Zou u INDEX kunnen toevoegen aan de vereiste velden en query opnieuw uitvoeren. Met behulp van indexering zijn zoekopdrachten sneller ... Zie hier bijvoorbeeld
toegevoegd de auteur Fahim Parkar, de bron
@NathanCantos: Bedankt voor het bevestigen.
toegevoegd de auteur Fahim Parkar, de bron
@ NathanCantos: Kunt u voorbeeldgegevens en verwachte uitvoer opgeven? Het zou ons echt helpen.
toegevoegd de auteur Fahim Parkar, de bron
@ NathanCantos: ik heb voorbeeldgegevens gemaakt hier . laat het me weten als dit in orde is.
toegevoegd de auteur Fahim Parkar, de bron
@NathanCantos: formatteer vraag met voorbeeldinvoer alstublieft ..
toegevoegd de auteur Fahim Parkar, de bron
Welke indexen heb je?
toegevoegd de auteur Jon Skeet, de bron
Heb je al een join geprobeerd?
toegevoegd de auteur Gumbo, de bron
MySQL is niet slim genoeg om de resultaten van de afhankelijke subquery in de cache op te slaan. Het wordt eenmaal voor elke rij van uw resultaatset uitgevoerd.
toegevoegd de auteur Interrobang, de bron
@dbaseman yep: stackoverflow.com/questions/3417074/… Ik kan de daadwerkelijke handmatige invoer vinden als je wilt
toegevoegd de auteur Interrobang, de bron
Moet de tabel veel rijen per vraag bevatten, één per antwoord (dat wil zeggen een niet-genormaliseerde één-op-veel)?
toegevoegd de auteur Chris Trahey, de bron
Is qanda een tabel of een weergave?
toegevoegd de auteur Chris Trahey, de bron
Wat doet EXPLAIN ? Kun je een screenshot posten?
toegevoegd de auteur Ashwini Dhekane, de bron
@Fahim Parkar: voorbeeldgegevens zien er goed uit.
toegevoegd de auteur Nathan Cantos, de bron
@ctrahey: Yep, eigenlijk komt elke rij overeen met een uniek antwoord. Een gegeven vraag kan meerdere antwoorden hebben, dus meerdere rijen kunnen bij een enkele vraag 'horen'.
toegevoegd de auteur Nathan Cantos, de bron
Zowat elke 'cel' heeft een waarde in de database, dus indexering zou van marginale voordelen zijn. Zelfs als dat niet het geval was, klinkt het volgens het artikel alsof indexering een verbetering heeft op een lineaire 10-20% manier [O (1.2 * n) versus O (1.0 * n)], niet de sprong van 0 (n ^ 3) naar O (n log n) soort manier die is wat ik echt nodig heb.
toegevoegd de auteur Nathan Cantos, de bron
Het is een tafel en ik geloof niet dat ik indexen heb.
toegevoegd de auteur Nathan Cantos, de bron

4 antwoord

Oké, ik heb een idee om dit te vertalen naar een JOIN in plaats van een subquery. De techniek is om JOIN toe te voegen waar is en te antwoorden van die gebruiker (omdat dat radicaal efficiënter is), en dan gewoon die vragen uit te sluiten van de eindresultaten (in HAVING). Het is mogelijk dat dit nog verder zou kunnen worden verbeterd (ik heb het niet getest, maar de IS NULL-controle kan mogelijk in plaats daarvan worden verplaatst naar een WHERE-component).

Krijgt u waar u wilt zijn?

SELECT question.quserid, COUNT(question.quserid) as num_questions
FROM qanda as question 
LEFT OUTER JOIN qanda as answers 
  ON question.qid = answers.qid AND answers.auserid = 'myusername'
GROUP BY question.quserid
ORDER BY num_questions DESC
HAVING answers.auserid IS NULL;

EDIT: een beetje meer uitleg, voor het geval dit in de buurt komt en iemand anders kan helpen het idee in een oplossing te verfijnen.

In principe zal het hoofdgedeelte van de query elke vraag exact één keer selecteren (LINKER-BUITENSTE), en vervolgens voor de vragen die de gebruiker van interesse heeft beantwoord, zal deze ook de JOIN'd-kolommen selecteren. Dit brengt ons in een staat waarin we alle vragen hebben in de kandidaat-resultaatset, met 'vlaggen' gekoppeld aan diegenen waar we niet in geïnteresseerd zijn (d.w.z. niet-null gekoppelde kolommen). Vervolgens groeperen en weigeren we de rijen met niet-null samengevoegde gegevens.

2
toegevoegd
je hebt twee ; in je zoekopdracht. HAVING wordt uitgevoerd?
toegevoegd de auteur Fahim Parkar, de bron
Zie hier uw en OP-antwoord komen niet overeen.
toegevoegd de auteur Fahim Parkar, de bron
één vraag heeft veel antwoorden, dus in één tabel kunnen veel qid voorkomen, wat betekent dat qid geen primaire sleutel is voor deze tabel. zie hier
toegevoegd de auteur Fahim Parkar, de bron
Ik denk dat ik me daarvan bewust was toen ik deze oplossing bedacht. Is er een reden waarom het niet zal werken met betrekking tot qid?
toegevoegd de auteur Chris Trahey, de bron
In feite is dit de basis van mijn hele oplossing (d.w.z. self join ON qid) ...
toegevoegd de auteur Chris Trahey, de bron

With reference to @ctrahey answer I have changed query and got solution working as @ctrahey was not giving output as yours as shown here.

Kun je het proberen en me laten weten of dit sneller is?

SELECT question.quserid, COUNT(question.quserid) as num_questions
FROM qanda as question 
LEFT OUTER JOIN qanda as answers 
  ON question.qid = answers.qid AND answers.auserid = 'user2'
WHERE answers.auserid IS NULL
GROUP BY question.quserid
ORDER BY num_questions DESC;

Zie ook sqlfiddle . Uw vraag en bovenstaande query geven dezelfde uitvoer.

Ik hoop dat dit is wat je wilde.

0
toegevoegd
Hoe is dit een ander antwoord dan het mijne? Syntaxis/typo wijzigingen? Is dat niet de reden waarom SO het bewerken van antwoorden toestaat? Ik zei zelfs dat verhuizen HEEFT naar WAAR in mijn antwoord ...
toegevoegd de auteur Chris Trahey, de bron

Probeer ook deze:

SELECT quserid, COUNT(*) AS num
FROM qanda
WHERE qid NOT IN 
        ( SELECT qid
          FROM qanda 
          WHERE auserid = 'user2' 
        )  
GROUP BY quserid 
ORDER BY num DESC 
LIMIT 0,1000 ;

U zult zeker indices moeten toevoegen om efficiënt te zijn.

Is de (qid, quserid, auserid) de PRIMAIRE -toets? Is het UNIEK (ik veronderstel dat het een van de twee zou moeten zijn).

Een index op (quserid, qid) zou ook helpen (als uw tabel de InnoDB-engine gebruikt).

0
toegevoegd

Probeer op basis van de uitvoer uit te zoeken aan welke kandidaten u indexering kunt uitvoeren als er een volledige tabel wordt gescand.

0
toegevoegd