MySQL Forums :: Newbie :: Nasty Query, Optimization Help Please!

Hey Everyone,

I have a really nasty query that keeps popping up in one of the web applications that I'm hosting. It's taking about 20s to run. I can't change the query, but I was wondering what my options are for optimizing it.

Here is an example of what the query looks like.

SELECT m1.message, a.some_id, b.some_id, a.some_date, b.some_date
FROM SOME_TABLE a, SOME_TABLE b, MESSAGE_TABLE m1, MESSAGE_TABLE m2
WHERE m2.some_id = a.some_id
    AND a.thing_a != ''
    AND a.thing_a = b.thing_b
    AND b.some_id = m1.some_id
    AND m1.message='A REALLY LONG ENCRYPTED MESSAGE!!'
ORDER BY a.some_date, a.some_id;

As you can see it's hitting two tables, twice. It's a pretty brutal query but I can't change it.

I was wondering if anyone could give me an idea on what kinds of steps I can take to speed this up. I've played around with increasing the join buffer size.

Here is the result from an explain done on that query:

+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                     | rows  | Extra                          |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------------+
|  1 | SIMPLE      | a     | ALL    | NULL          | NULL    | NULL    | NULL                    | 11269 | Using where                    |
|  1 | SIMPLE      | b     | ALL    | NULL          | NULL    | NULL    | NULL                    | 11269 | Using where; Using join buffer |
|  1 | SIMPLE      | m1    | eq_ref | PRIMARY       | PRIMARY | 4       | oscar_mcmaster.b.lab_no |     1 | Using where                    |
|  1 | SIMPLE      | m2    | eq_ref | PRIMARY       | PRIMARY | 4       | oscar_mcmaster.a.lab_no |     1 |                                |
+----+-------------+-------+--------+---------------+---------+---------+-------------------------+-------+--------------------------------+

Output of mysql -V:

mysql  Ver 14.14 Distrib 5.1.41, for debian-linux-gnu (i486) using readline 6.1

Any help would be really appreciated.

Thanks,