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?

Replay

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

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

search

Front-end development

Database

development tools

Open Platform

Javascript development

.NET development

cloud computing

server

Copyright (C) avrocks.com, All Rights Reserved.

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