MySQL Performance: MySQL => $100 Paypal if you can optimize this query

$100 Paypal if you can optimize this query [message #2610]

Mon, 11 February 2008 07:02

Ythan
Messages: 4
Registered: February 2008
Location: NY, USA

Junior Member

I'm trying to optimize a query for my forums. The query is related to "my posts" functionality where a user can see a list of all the threads they've posted in, ordered by most recent activity. Here is a stripped down version of the query:

mysql> EXPLAIN SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS DISTINCT Main.B_Number
    -> FROM BB_Posts AS Main, BB_Posts AS Reply
    -> WHERE Main.B_Number = Reply.B_Main
    -> AND Reply.B_PosterId = 654
    -> AND Reply.B_Board IN ('1', '2', '3')
    -> ORDER BY Main.B_Last_Post DESC;
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                     | rows | Extra                                        |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------------------------------------+
|  1 | SIMPLE      | Reply | ref    | ID_ndx        | ID_ndx  | 4       | const                   | 7872 | Using where; Using temporary; Using filesort | 
|  1 | SIMPLE      | Main  | eq_ref | PRIMARY       | PRIMARY | 4       | WWWThreads.Reply.B_Main |    1 |                                              | 
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+------+----------------------------------------------+

Here's a dump of a small subset of the table: example.sql
(P.S. I know the structure is kind of ugly eg. all the enums and some of the indexes, it's due to attempts at backwards-compatible optimization.)

I'll pay $100 via Paypal if someone can suggest an alternate query or an index which gets rid of the temporary + filesort without affecting the results.

Thanks in advance for any help, this is driving me crazy. Confused

Regards,

-Y

Report message to a moderator