[Solved] How to optimize mysql queries with multiple inequalities?
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

How to optimize mysql queries with multiple inequalities?

Database type:

I have hundreds of website queries that look something like this:

SELECT blah, blah, blah FROM sometable
WHERE '2015-12-15 18:55:20' BETWEEN date_from AND date_to  
AND date_from > '2015-06-26 10:40:08'  
and ctcx_latitude >= 41.87403474 and ctcx_latitude <= 41.93293328 
and ctcx_longitude >= -87.64343262 and ctcx_longitude <= -87.61356354 
and (LP_LIST_PRICE BETWEEN 90000 and 250000) and BTH_BATHS >= 1.0 and BR_BEDROOMS >= 1
order by LP_LIST_PRICE

Notice how every condition is an inequality.

MYSQL is completely unable to use any index for this query. I've tried many possible combinations, but what it boils down to, is that inequalities don't seem to be properly recognized by the mysql optimizer. This is mysql5.6 on Amazon RDS (latest version supported).

Currently I'm able to work-around this by growing server memory to match table sizes, but this is already 4x more expensive than necessary and will only get worse.

Can this kind of query run well (using indexes efficiently) in mysql?

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
Optimal indexes for this query:
ALTER TABLE `sometable` ADD INDEX `sometable_idx_date_from` (`date_from`);
The optimized query:
        '2015-12-15 18:55:20' BETWEEN sometable.date_from AND sometable.date_to 
        AND sometable.date_from > '2015-06-26 10:40:08' 
        AND sometable.ctcx_latitude >= 41.87403474 
        AND sometable.ctcx_latitude <= 41.93293328 
        AND sometable.ctcx_longitude >= -87.64343262 
        AND sometable.ctcx_longitude <= -87.61356354 
        AND (
            sometable.LP_LIST_PRICE BETWEEN 90000 AND 250000
        AND sometable.BTH_BATHS >= 1.0 
        AND sometable.BR_BEDROOMS >= 1 
        AND sometable.ASM_ASSESMENT_ASSOCIATION_DUES <= 500 

Related Articles

* original question posted on StackOverflow here.