Re: Slower machine 100's of times faster??

[David Wynter]

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