Indexing for multiple columns

Question

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?

Nope, because you do not search for them. Once the condition is met, you have access to the data. So it depends on the proper indexing for user1 and user2. And using numerical field types (INTEGER or BIGINT) for columns that contain only numerical values will also benefit performance. — syck
– syck, Commented Jul 29, 2016 at 9:07
never worked it this way before but maybe like this: AND 717054941 IN (user1,user2) — Jeff
– Jeff, Commented Jul 29, 2016 at 9:08
I recommend creating computed index on win+last+drawn ..dev.mysql.com/doc/refman/5.7/en/… i am not sure how two seperate indexes (user1,user2) can be used — TheGameiswar
– TheGameiswar, Commented Jul 29, 2016 at 9:11

Gordon Linoff · Accepted Answer · 2016-07-29 11:31:38Z

2

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.)

answered Jul 29, 2016 at 11:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Indexing for multiple columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related