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,