Understanding Slow_Queries

Hi,

According to phpmyadmin I have 15 slow queries somewhere.
And that is, (again according to phpmyadmin), to many.

How can I find what queries they were and what could have caused them to be slow.

During the same period I had around 2 million queries, so I am not too worried about the 15, but I just want to make sure that they did not block all the other queries.

So, what are Slow_Queries, and how do I find out what they are?

FFMG

 

Go through your code and test all queries with EXPLAIN. Then see where you can squeeze more out of your database.

 

I have looked at EXPLAIN but I have a 3 way JOIN that I just cannot understand/see how else I could optimize it.

SELECT
  t1.SOME_ID,
  t1.title,
  t1.contents,
  t2.text1,
  t2.text2,
  t2.text3,
  t3.text4,
  t1.date,
  t2.number3
FROM
  t3,
  t1,t2
WHERE
  t3.w_id = t1.w_id
AND
  t1.SOME_ID = t2.SOME_ID_2
AND
  t2.SOME_ID_3 != 25
AND
  number_a='3'
AND
  number_b='0'
ORDER BY t1.SOME_ID DESC
LIMIT 0, 30;

Code (markup):

The EXPLAIN does not explain much really.

What do you think is needed?

FFMG

 

Can't tell without seeing the EXPLAIN output as well as the table structure.

But I'm no expert. I always post my troubles on ExpertsExchange.

 

Thanks for your time.

id    select_type     table     type    possible_keys     key     key_len     ref                rows     Extra
1      SIMPLE           t1    index       w_id          PRIMARY     4         NULL                16877   Using where
1      SIMPLE           t3    eq_ref     PRIMARY        PRIMARY     4       database1.t1.w_id     1    
1      SIMPLE           t2    eq_ref     PRIMARY        PRIMARY     4       database1.t1.SOME_ID  1       Using where

Code (markup):

When I look at the explain it tells me that there is somehting wrong with the first row, (t1), but I do have an index

PRIMARY   PRIMARY     16877    Edit  Drop  e_id
  w_id    INDEX        2411    Edit  Drop  w_id

Code (markup):

So what other key should I create?

FFMG

 

MySQL executes from right to left. So move all the excluding stuff to the right of the query so it narrows down which part to actually look for.

AND
t2.SOME_ID_3 != 25 --> move to the far right
AND
number_a='3'
AND
number_b='0'

As far as I understand, every column you do the WHERE on need an index if you want it to be lightning fast. But that query doesn't look that bad. How long does it take to execute?

ORDER BY can also cause delays. You can let PHP do that for you, instead of sorting it in MySQL.

 

another sidenote: it isn't always a problem with your query structure itself. sometimes, when your server is under a lot of stress for other reasons (making stats? rotating logs?) and mysql is trying to do things at the same time, things may take an abnormally long time.

also I'd suggest looking into httpd.conf, setting the slow query log to be on, and specifying the sloq query log time to something you find acceptable. default is 10 seconds, iirc.

if you do have queries that actually take 10 seconds to execture (this does happen !), you should definitely look into learning how to cache your queries.

 

phpmyadmin only tells part of the story.

In order to tune and understand what is happening in your mysql server - take a look at tiniuri.com and tiniuri.com and tiniuri.com

If you cannot install/access these 3 tools, then suggest to the server admin might find them of use. If you run your own server, then they should all be installed - they are all invaluable in your quest of tweaking and tuning your MySQL server and understanding what it is doing.