MySQL Forums
Forum List  »  Performance

Re: Indexes not being used?
Posted by: David Wynter
Date: December 12, 2005 05:14AM

I have a very similar problem with another query. The pattern seems to be that my setup of MySQL is fast for INSERT and UPDATE (which it does an awful lot of) and slow for these selects. This query takes about 30 minutes to run

SELECT ce1.Event_Id, MAX(ce2.Event_Id) FROM
corax_event ce1, corax_event ce2
WHERE ce1.Event_Id > ce2.Event_Id
AND ce1.Cap_Chng_Market_Lvl_Id = ce2.Cap_Chng_Market_Lvl_Id
GROUP BY ce1.Event_Id
UNION
SELECT ce3.Event_Id, MAX(ce4.Event_Id) FROM
corax_event ce3, corax_event ce4
WHERE ce3.Event_Id > ce4.Event_Id
AND ce3.Div_Market_Lvl_Id = ce4.Div_Market_Lvl_Id
GROUP BY ce3.Event_Id
UNION
SELECT ce5.Event_Id, MAX(ce6.Event_Id) FROM
corax_event ce5, corax_event ce6
WHERE ce5.Event_Id > ce6.Event_Id
AND (ce5.ASSET_ID = ce6.ASSET_ID AND ce5.Event_Type = ce6.Event_Type AND
UNIX_TIMESTAMP(ce5.Event_Time) - UNIX_TIMESTAMP(ce6.Event_Time) < 172800
AND ce5.Event_Type IN (3,4,5,6,7))
GROUP BY ce5.Event_Id
UNION
SELECT ce7.Event_Id, MAX(ce8.Event_Id) FROM
corax_event ce7, corax_event ce8
WHERE ce7.Event_Id > ce8.Event_Id
AND (ce7.Event_Type=8 AND ce8.Event_Type=8
AND UNIX_TIMESTAMP(ce7.Event_Time) - UNIX_TIMESTAMP(ce8.Event_Time) < 172800)
GROUP BY ce7.Event_Id

Explain is here

1, 'PRIMARY', 'ce1', 'index', 'PRIMARY,Cap_Chng_Market_idx', 'PRIMARY', '8', '', 38859, ''
1, 'PRIMARY', 'ce2', 'ref', 'PRIMARY,Cap_Chng_Market_idx', 'Cap_Chng_Market_idx', '9', 'securitymaster.ce1.Cap_Chng_Market_Lvl_Id', 19429, 'Using where; Using index'
2, 'UNION', 'ce3', 'index', 'PRIMARY,Div_Market_Lvl_idx', 'PRIMARY', '8', '', 38859, ''
2, 'UNION', 'ce4', 'ref', 'PRIMARY,Div_Market_Lvl_idx', 'Div_Market_Lvl_idx', '9', 'securitymaster.ce3.Div_Market_Lvl_Id', 1, 'Using where; Using index'
3, 'UNION', 'ce5', 'index', 'PRIMARY,Asset_Id_idx,Event_Type_idx', 'PRIMARY', '8', '', 38859, 'Using where'
3, 'UNION', 'ce6', 'ref', 'PRIMARY,Asset_Id_idx,Event_Type_idx', 'Asset_Id_idx', '9', 'securitymaster.ce5.ASSET_ID', 1, 'Using where'
4, 'UNION', 'ce7', 'ref', 'PRIMARY,Event_Type_idx', 'Event_Type_idx', '5', 'const', 1, 'Using where; Using temporary; Using filesort'
4, 'UNION', 'ce8', 'ref', 'PRIMARY,Event_Type_idx', 'Event_Type_idx', '5', 'const', 1, 'Using where'
, 'UNION RESULT', '<union1,2,3,4>', 'ALL', '', '', '', '', , ''

I can see it does a table scan for the even suffixed ce tables (ce1, ce3 etc.) but even then it is only 38859 rows (which will grow by about 3000 rows per day) . I am hoping Pro MySQL will explain a lot of these behaviours when it arrives in a few weeks.

Thx.

David

Options: ReplyQuote


Subject
Views
Written By
Posted
2196
December 09, 2005 11:45AM
1428
December 09, 2005 11:51AM
1502
December 09, 2005 11:53AM
1577
December 09, 2005 12:11PM
1364
December 09, 2005 12:12PM
1359
December 09, 2005 12:33PM
1392
December 10, 2005 10:50AM
Re: Indexes not being used?
1461
December 12, 2005 05:14AM
1391
December 13, 2005 03:16AM
1531
December 14, 2005 11:17PM
1494
December 16, 2005 11:46AM
1421
December 26, 2005 08:32PM
1411
December 14, 2005 10:47PM
1425
December 23, 2005 12:29PM


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.