MySQL not using index

[Dani]

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.

[pritaeas]

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)

[Dani]

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) ??

[Dani]

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?

[cereal]

Have you tried FORCE INDEX? http://dev.mysql.com/doc/refman/5.5/en/how-to-avoid-table-scan.html

[Dani]

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

[cereal]

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

[cereal]

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:

• in MyISAM you need to change key_buffer_size: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_key_buffer_size
• in InnoDB instead is innodb_buffer_pool_size: http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size

[Dani]

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.

[Dani]

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!