Timeline for Index to fix this query MySQL
Current License: CC BY-SA 3.0
11 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Jun 15, 2020 at 9:05 | history | edited | CommunityBot |
Commonmark migration
|
|
| Jan 28, 2017 at 9:30 | history | edited | ypercubeᵀᴹ | CC BY-SA 3.0 |
deleted 1 character in body
|
| Jan 28, 2017 at 9:29 | comment | added | ypercubeᵀᴹ |
An index on (userid, postid) will be more efficient for this query than one on (postid, userid).
|
|
| Jul 3, 2015 at 1:45 | comment | added | mustaccio |
I'd begin with the suggestion #3, as it might enable index-only access to thread. Also check if changing LEFT OUTER JOIN of post_thanks and post to INNER JOIN makes a difference, because the INNER JOIN of post and thread cancels the "outer-ness" of the former anyway.
|
|
| Jul 3, 2015 at 0:41 | comment | added | oNare |
Did you try adding 1 index on post_thanks.userid?
|
|
| Mar 31, 2014 at 18:07 | comment | added | user36445 | The indeces take quite awhile to add to the tables because of their size (15 minutes or so) so I have to schedule these changes in advance. I will try to get the (userid, postid) on post_thanks added tomorrow. | |
| Mar 31, 2014 at 17:56 | comment | added | Ulrich Thomas Gabor |
An index (postid,dateline) on post might help too. If it is fairly easy to alter it, I would add this too and later remove the one ((postid, dateline) or (threadid, dateline)) which is not used.
|
|
| Mar 31, 2014 at 17:45 | comment | added | Ulrich Thomas Gabor |
Then I would add an index (userid, postid) on post_thanks. Since this is the table with the most rows (according to the auto_increment values) and this index helps in finding matching rows. Please add a new output of EXPLAIN below the existing one, so we are able to compare.
|
|
| Mar 31, 2014 at 17:31 | comment | added | user36445 | Added combined index. Didn't appear to help at all. Updating first post. | |
| Mar 31, 2014 at 16:42 | comment | added | user36445 | Thank you for your help. We will take care of Step 1 this afternoon and report back. | |
| Mar 31, 2014 at 16:39 | history | answered | Ulrich Thomas Gabor | CC BY-SA 3.0 |