Help Optimizing a MySQL SELECT with ORDER BY

Currently the table has the following indexes:

1. forum_id_index
2. other_forum_id_index
3. forum_id_on_other_forum_id_index => [forum_id, other_forum_id]

The query:

SELECT `topics.*` 
FROM `topics` 
WHERE (table.forum_id = ? OR table.other_forum_id = ?) 
ORDER by sticky, replied_at DESC LIMIT 25

I've tried adding indexes on the following:

1. sticky
2. replied_at
3. [sticky, replied_at]
4. [forum_id, other_forum_id, sticky, replied_at]
5. [sticky, replied_at, forum_id, other_forum_id]

This is for a forum, trying to get the top 25 topics in the forum, but placing sticky topics (sticky is a binary field for sticky/nonsticky) at the top.

I've read pretty much everything I can get my 开发者_如何学JAVAhands on about optimizing ORDER BY, but no luck. This is on MySQL 5.1, INNODB. Any help would be greatly appreciated.

EDITS

As requested in comments (sorry if I'm doing this wrong - new to posting on SU). Results of EXPLAIN currently:

id = 1

select_type = SIMPLE

table = topics

type = index_merge

possible_keys = index_topics_on_forum_id,index_topics_on_sticky_and_replied_at,index_topics_on_forum_id_and_replied_at,index_topics_on_video_forum_id,index_forum_id_on_video_forum_id,

keys = index_topics_on_forum_id,index_topics_on_video_forum_id

key_len = 5,5

ref = NULL

rows = 13584

Extra = Using union(index_topics_on_forum_id,index_topics_on_video_forum_id); Using where; Using filesort

SHOW INDEXES FROM topics returns https://gist.github.com/1079454 - Couldn't get formatting to show up here well.

EDIT 2

SELECT `topics`.*
FROM `topics`
WHERE topics.forum_id=4
ORDER BY sticky desc, replied_at DESC

Runs incredibly fast (1.4ms). So does the query when I change topics.forum_id to topics.video_forum_id - just not when I have them both in the query with an or.

---

I think this should be very fast.

Indexes:

ALTER TABLE `topics` 
    ADD INDEX `forum` (`forum_id` ASC, `sticky` ASC, `replied_at` DESC), 
    ADD INDEX `other_forum` (`other_forum_id` ASC, `sticky` ASC, `replied_at` DESC);

Query:

(
    SELECT `topics.*` 
    FROM `topics` USE INDEX (`forum`)
    WHERE `topics`.forum_id = ?
    ORDER by sticky, replied_at DESC 
    LIMIT 25
) UNION (
    SELECT `topics.*` 
    FROM `topics` USE INDEX (`other_forum`)
    WHERE `topics`.other_forum_id = ?
    ORDER by sticky, replied_at DESC 
    LIMIT 25
)
ORDER by sticky, replied_at DESC 
LIMIT 25

---

Try indexes [forum_id, sticky, replied_at desc] and [other_forum_id, sticky, replied_at desc]

You might try wording the query as a union, as well:

    SELECT `topics.*` FROM `topics` WHERE (table.forum_id = ?)
UNION
SELECT `topics.*` FROM `topics` WHERE (table.other_forum_id = ?)
 ORDER by sticky, replied_at DESC LIMIT 25

---

Use MySQL's EXPLAIN command to learn about the cost associated with your query:

EXPLAIN SELECT ...

Look out for table-scans, which are likely to be costly.

Moreover, MySQL may or may not use an index. This solely depends on how the query optimizer understands your query.

FORCE INDEX might be of help, since this option tells MySQL, that table-scans will be super-costly. Have a look here.

---

You can try 2 things:

• One, with indexes on:

  • (forum_id, sticky, replied_at)
  • (other_forum_id, sticky, replied_at)

either with your original query or Karolis' suggestion or

• Two, with indexes on:

  • (forum_id)
  • (other_forum_id)
  • (sticky, replied_at)