Re: Slower machine 100's of times faster??
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
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
1555
December 13, 2005 05:10AM
1503
December 13, 2005 10:53AM
1632
December 14, 2005 11:17PM
1656
December 16, 2005 05:15AM
1573
December 16, 2005 06:11AM
1823
December 16, 2005 08:14AM
Re: Slower machine 100's of times faster??
1628
December 16, 2005 11:30AM
1579
December 16, 2005 11:46AM
1513
December 16, 2005 12:06PM
1473
December 21, 2005 09:21AM
1647
December 21, 2005 10:08AM
1956
December 23, 2005 09:22AM
1520
December 26, 2005 08:32PM
1500
December 14, 2005 10:47PM
1511
December 23, 2005 12:29PM