Poor Performance on an Indexed Query - Large Table
Hi All,
I have a reasonably simple query that is just taking forever (2 min). It is on a table that has about 50 million rows. The query uses an indexed field in it's WHERE clause. The query is as follows:
select SQL_CALC_FOUND_ROWS MyKey, 0 AS Event, MsTime,
Sequence, Firm, Source, User, Token, BuySell, Shares, Symbol, Price, '' AS Contra, Clearing, Display, Lifetime, Capacity AS LiqCap FROM Orders WHERE Display = 'Y'
AND Symbol = 'SYM1' AND Firm = 'FOMA' LIMIT 100;
both Symbol and Firm are indexed. The data is truncated and written new each day. The EXPLAIN is:
+----+-------------+--------+------+-------------------------+--------------+---------+--------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+-------------------------+--------------+---------+--------+------+-------------+
| 1 | SIMPLE | Orders | ref | Index_firm,Index_symbol | Index_symbol | 6 | const | 662 | Using where |
+----+-------------+--------+------+-------------------------+--------------+---------+--------+------+-------------+
Any help would be appreciated!!!!!!!!
thanks!