1,105,214 Community Members

MySQL not using index

Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
0
 

I have the following queries (as examples):

SELECT articles.*
FROM articles
WHERE articles.forumid IN (2,8,118,9,61,58,34,114,112,113,125,124,4,134,42,14,71,181)
ORDER BY lastpost DESC
LIMIT 0, 30
SELECT articles.*
FROM articles
WHERE articles.forumid IN (2,8,118,9,61,58,34,114,112,113,125,124,4,134,42,14,71,181)
AND NOT deleted
ORDER BY lastpost DESC
LIMIT 0, 30
SELECT articles.*, IF(articles.forumid = 8, articles.sticky, 0) AS show_sticky
FROM articles
WHERE articles.forumid IN (8)
ORDER BY show_sticky DESC, lastpost DESC
LIMIT 0, 30

I have an index on the articles table as such: (forumid, deleted, lastpost, sticky). None of these queries will use this index. When I do EXPLAIN SELECT they all just do USING WHERE; USING FILESORT. I tried adding the USE INDEX hint and the query still refuses to speed up. I've tried modifying the order of the columns in the index about 5 times already.

Member Avatar
pritaeas
mod_pritaeas
11,287 posts since Jul 2006
Reputation Points: 1,420 [?]
Q&As Helped to Solve: 1,832 [?]
Skill Endorsements: 154 [?]
Moderator
Featured
Sponsor
 
0
 

Can this be the issue?

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

Taken from here. (Apparantly this is only valid for below 5.5)

Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
0
 

Hi,

Unfortunately I don't think so. Essentially it is saying that if you have an index on (col1, col2, col3), then you can use that index when searching on (col1), (col1, col2), or (col1, col2, col3), but not on (col2, col3) or (col3), for example.

I am taking this into consideration. The index is on (forumid, delted, lastpost, sticky).
Maybe I need to put more throught into the first and third queries, but the second query mentioned uses (forumid, deleted, lastpost) ??

Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
0
 

Incidentally ... do I want an index on the ORDER BY column? What would be the ideal order of the columns in the index to work well for both the first and second queries shown?

Member Avatar
cereal
Posting Virtuoso
1,710 posts since Aug 2007
Reputation Points: 461 [?]
Q&As Helped to Solve: 345 [?]
Skill Endorsements: 44 [?]
 
1
 
Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
0
 

Yes, I have :( It doesn't help and I don't understand why not.

Member Avatar
cereal
Posting Virtuoso
1,710 posts since Aug 2007
Reputation Points: 461 [?]
Q&As Helped to Solve: 345 [?]
Skill Endorsements: 44 [?]
 
1
 

I don't know if you solved, but what you get if you run show indexes from articles; this will give you some values for each field in the index, check about cardinality, if this is too low in some fields, for example in forumid, deleted and lastpost then this can be the reason why the index is not working: too many rows to read. And if this happens the reading buffer can't read all rows and use filesort() to read small part of data, check: http://s.petrunia.net/blog/?p=24

So maybe you can try to increase sort_buffer_size (default is ~2MB) and let fit the index in memory: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_sort_buffer_size

Member Avatar
cereal
Posting Virtuoso
1,710 posts since Aug 2007
Reputation Points: 461 [?]
Q&As Helped to Solve: 345 [?]
Skill Endorsements: 44 [?]
 
0
 

Sorry for the additional comment but I forgot to add this to my previous reply: you can also check and increase the size of the buffer for the index instead of the sort buffer:

Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
0
 

I was finally able to get this problem solved. It turns out that I had to use USE INDEX () in the SQL query in order to force MySQL to use an optimal index. It works super speedy now. I have no idea why MySQL is refusing to pick the correct index to use. I actually did have to switch the forumid and lastpost columns in the index around as well.

Question Answered as of 2 Years Ago by cereal and pritaeas
Member Avatar
Dani
The Queen of DaniWeb
20,551 posts since Feb 2002
Reputation Points: 1,356 [?]
Q&As Helped to Solve: 927 [?]
Skill Endorsements: 203 [?]
Administrator
Featured
Sponsor
 
2
 

Have you tried FORCE INDEX?

I had to switch two columns of the index around. It still didn't work. Then I chose USE INDEX and now it's super speedy. Thanks!

You
This question has already been solved: Start a new discussion instead
Post:
Start New Discussion
View similar articles that have also been tagged: