I have to run a query on a table where there are more than 10 million rows. The table structure is
user(varchar 100), played(int 11), won(int 11), lost(int 11), drawn(int 11), user1(varchar 30), user2(varchar 30). Where User - primary key user1 - index user2 - index
MySql database engine is MyISAM.
My problem is - When I run the below query it is taking more than 17 seconds.
SELECT * FROM h2hstats WHERE (won+lost+drawn) > 5 AND (user1 = '717054941' OR user2 = '717054941')
How can I reduce this execution time?
Will I make another indexing on (won+lost+drawn) columns?
First, if the user columns are numbers, then do not use single quotes for the constants. This can confuse the optimizer. This probably won't help your query (My SQL does a poor job of using indexes for
OR), but it is worth trying.
Next, consider rewriting the query as:
WHERE (won + lost + drawn) > 5 AND user1 = 717054941
WHERE (won + lost + drawn) > 5 AND user2 = 717054941 ;
MySQL will definitely use indexes for each of the subqueries. That should provide a performance boost.
(Note: This version assumes that
user2. If this is possible, you might want to use
UNION rather than