MySQL Forums
Forum List  »  Newbie

Nasty Query, Optimization Help Please!
Posted by: Dave Lawson
Date: February 17, 2012 04:10PM

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,

Options: ReplyQuote


Subject
Written By
Posted
Nasty Query, Optimization Help Please!
February 17, 2012 04:10PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.