MySQL Forums
Forum List  »  Performance

Re: Slower machine 100's of times faster??
Posted by: David Wynter
Date: December 16, 2005 11:30AM

Well I got that wrong, when I remembered to add a index to the new Processed flag in corax_event the query flies. Still strange that 4.1.14 which has many more orws int his table and the same number in the table it joins to in this query did NOT need this additional restriction to perform well.

Here is the explain plan now

+----+--------------+----------------+-------------+---------------------------------------------+------------------------+---------+-------------------------------------------+-------+---------------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+----------------+-------------+---------------------------------------------+------------------------+---------+-------------------------------------------+-------+---------------------------------------------------------------------------------------+
| 1 | PRIMARY | ce1 | ref | PRIMARY,Cap_Chng_Market_idx,IX_Proc | IX_Proc | 2 | const | 1210 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | ce2 | ref | PRIMARY,Cap_Chng_Market_idx | Cap_Chng_Market_idx | 9 | securitymaster.ce1.Cap_Chng_Market_Lvl_Id | 24811 | Using where; Using index |
| 2 | UNION | ce1 | ref | PRIMARY,Div_Market_Lvl_idx,IX_Proc | IX_Proc | 2 | const | 1210 | Using where; Using temporary; Using filesort |
| 2 | UNION | ce2 | ref | PRIMARY,Div_Market_Lvl_idx | Div_Market_Lvl_idx | 9 | securitymaster.ce1.Div_Market_Lvl_Id | 1 | Using where; Using index |
| 3 | UNION | ce1 | ref | PRIMARY,Asset_Id_idx,Event_Type_idx,IX_Proc | IX_Proc | 2 | const | 1210 | Using where; Using temporary; Using filesort |
| 3 | UNION | ce2 | ref | PRIMARY,Asset_Id_idx,Event_Type_idx | Asset_Id_idx | 9 | securitymaster.ce1.ASSET_ID | 1 | Using where |
| 4 | UNION | ce1 | index_merge | PRIMARY,Event_Type_idx,IX_Proc | Event_Type_idx,IX_Proc | 5,2 | NULL | 1 | Using intersect(Event_Type_idx,IX_Proc); Using where; Using temporary; Using filesort |
| 4 | UNION | ce2 | ref | PRIMARY,Event_Type_idx | Event_Type_idx | 5 | const | 32 | Using where |
|NULL | UNION RESULT | <union1,2,3,4> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+----------------+-------------+---------------------------------------------+------------------------+---------+-------------------------------------------+-------+---------------------------------------------------------------------------------------+
9 rows in set (0.13 sec)

So just the first query to sort out now. Unfortunately I don't have the option of the same mechanism on that 1st query as I use on the 2nd query here.

David

Options: ReplyQuote


Subject
Views
Written By
Posted
2370
December 09, 2005 11:45AM
1518
December 09, 2005 11:51AM
1606
December 09, 2005 11:53AM
1673
December 09, 2005 12:11PM
1460
December 09, 2005 12:12PM
1451
December 09, 2005 12:33PM
1484
December 10, 2005 10:50AM
1551
December 12, 2005 05:14AM
1481
December 13, 2005 03:16AM
1632
December 14, 2005 11:17PM
Re: Slower machine 100's of times faster??
1628
December 16, 2005 11:30AM
1579
December 16, 2005 11:46AM
1520
December 26, 2005 08:32PM
1500
December 14, 2005 10:47PM
1511
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.