MySQL Forums
Forum List  »  Performance

Poor Performance on an Indexed Query - Large Table
Posted by: James Mangold
Date: March 29, 2005 08:24AM

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!

Options: ReplyQuote


Subject
Views
Written By
Posted
Poor Performance on an Indexed Query - Large Table
2464
March 29, 2005 08:24AM


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.