NeedExpertHelp - 2:14 pm on Mar 20, 2011 (gmt 0)

Hi guys,

I can't seem to wrap my head around this one.

I have a MySQL query that runs in about 0.03 seconds and has every column it refers to fully-indexed, yet it is popping up continuously in my MySQL Slow Log.

From what I understand, the MySQL Slow Log is only meant to log queries that are longer than 1 second or could benefit from indexing.

I don't see how my query could further benefit from indexing since all 7 columns it refers to have already been indexed, and the query is taking a lot less than 1 second to execute, so I have no idea why MySQL is considering it "slow".

I have copied the query below with the corresponding EXPLAIN output to see if anyone can shed some light on this.

Needless to say, if I can further improve this query's speed, that would be great, but right now it is overfilling my slow log.

The Query:

---------------------------
(SELECT tableOne . * , tableTwo . * FROM TABLEONE AS tableOne
LEFT JOIN TABLETWO AS tableTwo USING ( col1 )
LEFT JOIN TABLETHREE AS tableThree USING ( col1 )
WHERE tableOne.col1 != '25202'
AND tableOne.col2 = 'ZZ'
AND tableThree.col3 = 'Y'
AND tableThree.col4 > ( UNIX_TIMESTAMP( ) -7200 )
AND tableOne.col5 LIKE 'F'
AND (tableOne.col6 LIKE 'M' OR tableOne.col6 LIKE 'M-B')
AND tableOne.col1 NOT IN (20758)
GROUP BY tableOne.col1
ORDER BY tableOne.col7 DESC LIMIT 50)

UNION

( SELECT tableOne . * , tableTwo . * FROM TABLEONE AS tableOne
LEFT JOIN TABLETWO AS tableTwo USING ( col1 )
LEFT JOIN TABLETHREE AS tableThree USING ( col1 )
WHERE tableOne.col1 != '25202'
AND tableThree.col3 = 'Y'
AND tableThree.col4 > ( UNIX_TIMESTAMP( ) -7200 )
AND tableOne.col5 LIKE 'F'
AND (tableOne.col6 LIKE 'M' OR tableOne.col6 LIKE 'M-B')
AND tableOne.col1 NOT IN (20758)
GROUP BY tableOne.col1
ORDER BY tableOne.col7 DESC LIMIT 50)

LIMIT 0, 5 ;


EXPLAIN:
Rows: 7
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY tableThree range col4,col3,col1 col4 5 NULL 549 Using where; Using temporary; Using filesort
1 PRIMARY tableOne eq_ref PRIMARY,col2,col5,col6 PRIMARY 4 Database.tableThree.col1 1 Using where
1 PRIMARY tableTwo eq_ref PRIMARY PRIMARY 4 Database.tableOne.col1 1
2 UNION tableThree range col4,col3,col1 col4 5 NULL 549 Using where; Using temporary; Using filesort
2 UNION tableOne eq_ref PRIMARY,col5,col6 PRIMARY 4 Database.tableThree.col1 1 Using where
2 UNION tableTwo eq_ref PRIMARY PRIMARY 4 Database.tableOne.col1 1
NULL UNION RESULT <union1,2> ALL NULL NULL NULL NULL NULL
---------------------------

To re-iterate: col1, col2, col3, col4, col5, col6, and col7 above are all indexed.

Thanks a lot for your help, I really appreciate it!