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

[David Wynter]

No, not the first SELECT in the 2nd query but the one you modified to use UNION back on the 9th December. Since I now have a performant 2nd query involving the corax_event table using the new Processed column I have moved back to the original query. Here it is in it's current form.

SELECT u.SECURITY_ID, u.CUSIP, NULL, NULL, NULL, NULL, NULL FROM customer_watch_list cw, secm_security_universe u WHERE cw.CUSIP <=> u.CUSIP AND cw.Portfolio_Id = 2
UNION SELECT u.SECURITY_ID, NULL, u.EQU_RIC, NULL, NULL, NULL, NULL FROM customer_watch_list cw, secm_security_universe u WHERE cw.RIC <=> u.EQU_RIC AND cw.Portfolio_Id = 2
UNION SELECT u.SECURITY_ID, NULL, NULL, u.ISIN, EXCHANGE_CD, NULL, NULL FROM customer_watch_list cw, secm_security_universe u WHERE (cw.ISIN <=> u.ISIN AND cw.Exch_Cd = u.EXCHANGE_CD) AND cw.Portfolio_Id = 2
UNION SELECT u.SECURITY_ID, NULL, NULL, NULL, NULL, u.SEDOL, NULL FROM customer_watch_list cw, secm_security_universe u WHERE cw.SEDOL <=> u.SEDOL AND cw.Portfolio_Id = 2
UNION SELECT u.SECURITY_ID, NULL, NULL, NULL, NULL, NULL, u.VALOREN FROM customer_watch_list cw, secm_security_universe u WHERE cw.VALOREN <=> u.VALOREN AND cw.Portfolio_Id = 2

+----+--------------+------------------+------+---------------+------------+---------+--------------------------------------------------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------------+------+---------------+------------+---------+--------------------------------------------------+------+--------------------------+
| 1 | PRIMARY | cw | ref | IX_P1 | IX_P1 | 4 | const | 1122 | |
| 1 | PRIMARY | u | ref | IX_CUSIP | IX_CUSIP | 12 | securitymaster.cw.CUSIP | 5 | Using where; Using index |
| 2 | UNION | cw | ref | IX_P1 | IX_P1 | 4 | const | 1122 | |
| 2 | UNION | u | ref | FK_EQU_RIC | FK_EQU_RIC | 23 | securitymaster.cw.RIC | 1 | Using where; Using index |
| 3 | UNION | cw | ref | IX_P1 | IX_P1 | 4 | const | 1122 | |
| 3 | UNION | u | ref | IX_ISIN | IX_ISIN | 19 | securitymaster.cw.ISIN,securitymaster.cw.Exch_Cd | 1 | Using where; Using index |
| 4 | UNION | cw | ref | IX_P1 | IX_P1 | 4 | const | 1122 | |
| 4 | UNION | u | ref | IX_SEDOL | IX_SEDOL | 11 | securitymaster.cw.SEDOL | 1 | Using where; Using index |
| 5 | UNION | cw | ref | IX_P1 | IX_P1 | 4 | const | 1122 | |
| 5 | UNION | u | ref | IX_VALOREN | IX_VALOREN | 9 | securitymaster.cw.VALOREN | 170 | Using where; Using index |
|NULL | UNION RESULT | <union1,2,3,4,5> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------------+------+---------------+------------+---------+--------------------------------------------------+------+--------------------------+
11 rows in set (0.05 sec)

Even taking the first SELECT out of this set of UNION and running that is very slow.

David