Don't like ads? PRO users don't see any ads ;-)
Public Pastes
Guest

Untitled

By: a guest on Jul 17th, 2012  |  syntax: None  |  size: 4.69 KB  |  hits: 14  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  1. Simple MySQL query is taking over two hours
  2. question, qid, quserid, answer, auserid;
  3.        
  4. SELECT quserid, COUNT(quserid)
  5. FROM `qanda`
  6. WHERE qid NOT IN (SELECT qid FROM `qanda` WHERE auserid='myusername' GROUP BY qid)
  7. GROUP BY quserid
  8. ORDER BY COUNT(quserid) DESC
  9. LIMIT 0,1000;
  10.        
  11. 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
  12.                             •glitter•rock•  191437  If you could have any one person in the entire wor...   korn_chick2007  matt or chris... i have feelings for them
  13.                             •glitter•rock•  189555  why are you so sexy?
  14.     just~another~slave2tears    my b/f says i am...i dun tink so tho
  15.                             •glitter•rock•  189555  why are you so sexy?
  16.     korn_chick2007  im not
  17.                             •glitter•rock•  189555  why are you so sexy?
  18.     MyKool-AidsSexy     i dont think i am
  19.                             †brokengirl†    115228  If you are supposed to expect the unexpected,
  20. doe...  death-tone  yip
  21.                             †brokengirl†    115228  If you are supposed to expect the unexpected,
  22. doe...  _doieverknowwhoiam_     you know whats weird? my friend sandy says that a ...
  23.                             †brokengirl†    115228  If you are supposed to expect the unexpected,
  24. doe...  Cute_Physco_kitty   Pretty much..
  25.                             †brokengirl†    115228  If you are supposed to expect the unexpected,
  26. doe...  Leslie02    WHAT! OK, now im confused!
  27.                             †brokengirl†    114995  Why does my listerine taste like sausage this
  28. mor...  death-tone  what's listerine?
  29.                             †brokengirl†    114995  Why does my listerine taste like sausage this
  30. mor...  _doieverknowwhoiam_     i don't know, and maybe it's jut me bu...
  31.                             †brokengirl†    114995  Why does my listerine taste like sausage this
  32. mor...  darksunofdeath  How old is the listerine pack?
  33.                             †brokengirl†    114995  Why does my listerine taste like sausage this
  34. mor...  Cute_Physco_kitty   uhh... New brand of Listerine?
  35.     †brokengirl†    114995  Why does my listerine taste like sausage this
  36. mor...  Leslie02    did you have sausage for breakfast?     †brokengirl†    104305  What should I name my pinky toe on my left
  37. foot?¿...   death-tone  "Pinkytoe"
  38.        
  39. Questioner User ID | Number of questions asked by the Questioner that were unanswered by 'myuserid'
  40.  
  41. Greenbay Packer | 6
  42. DollyDoll | 63
  43. PsychoticPokemon | 62
  44. HelloKitty | 61
  45. GreenDayFan | 60
  46.        
  47. IDontAskManyQuestion | 2<br>
  48. WhatsAQuestion? | 1<br>
  49.        
  50. > mysql-> EXPLAIN
  51. >     ->
  52. >     -> SELECT quserid, COUNT(quserID)
  53. >     -> FROM `qanda`
  54. >     -> WHERE qid NOT IN (SELECT qid FROM `qanda` WHERE auserid='boxocereal' GROU P BY qid)
  55. >     -> GROUP BY quserid
  56. >     -> ORDER BY COUNT(quserid) DESC
  57. >     -> LIMIT 0,1000;
  58. >
  59.  
  60. +----+--------------------+-------+------+---------------+------+---------+-----
  61. > -+---------+----------------------------------------------+ | id | select_type        | table | type | possible_keys | key  | key_len |
  62. > ref  | rows    | Extra                                        |
  63. > +----+--------------------+-------+------+---------------+------+---------+-----
  64. > -+---------+----------------------------------------------+ |  1 | PRIMARY            | qanda | ALL  | NULL          | NULL | NULL    |
  65. > NULL  | 3167995 | Using where; Using temporary; Using filesort | |  2
  66. > | DEPENDENT SUBQUERY | qanda | ALL  | NULL          | NULL | NULL    |
  67. > NULL  | 3167995 | Using where; Using temporary; Using filesort |
  68. > +----+--------------------+-------+------+---------------+------+---------+-----
  69. > -+---------+----------------------------------------------+ 2 rows in set (0.02 sec)
  70. >
  71. > mysql->
  72.        
  73. SELECT question.quserid, COUNT(question.quserid) as num_questions
  74. FROM qanda as question
  75. LEFT OUTER JOIN qanda as answers
  76.   ON question.qid = answers.qid AND answers.auserid = 'myusername'
  77. GROUP BY question.quserid
  78. ORDER BY num_questions DESC
  79. HAVING answers.auserid IS NULL;
  80.        
  81. SELECT question.quserid, COUNT(question.quserid) as num_questions
  82. FROM qanda as question
  83. LEFT OUTER JOIN qanda as answers
  84.   ON question.qid = answers.qid AND answers.auserid = 'user2'
  85. WHERE answers.auserid IS NULL
  86. GROUP BY question.quserid
  87. ORDER BY num_questions DESC;
  88.        
  89. SELECT quserid, COUNT(*) AS num
  90. FROM qanda
  91. WHERE qid NOT IN
  92.         ( SELECT qid
  93.           FROM qanda
  94.           WHERE auserid = 'user2'
  95.         )  
  96. GROUP BY quserid
  97. ORDER BY num DESC
  98. LIMIT 0,1000 ;
create a new version of this paste RAW Paste Data