mysql indexing with multiple rows

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:

FROM h2hstats
WHERE (won + lost + drawn) > 5 AND user1 = 717054941
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.)

Category: php Time: 2016-07-29 Views: 0
Tags: php mysql sql

Related post

iOS development

Android development

Python development

JAVA development

Development language

PHP development

Ruby development


Front-end development


development tools

Open Platform

Javascript development

.NET development

cloud computing


Copyright (C), All Rights Reserved.

processed in 0.111 (s). 12 q(s)