CODEWITHSUNDEEP
phpmysqlsql
Joy Das
Joy Das

Reputation: 85

Indexing for multiple columns

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?

Upvotes: 2

Views: 73

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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:

SELECT *
FROM h2hstats
WHERE (won + lost + drawn) > 5 AND user1 = 717054941
UNION ALL
SELECT *
FROM h2hstats
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 user1 <> user2. If this is possible, you might want to use UNION rather than UNION ALL.)

Upvotes: 2

Related Questions