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
and ASM_ASSESMENT_ASSOCIATION_DUES <= 500 
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:

Description of the steps you can take to speed up the query.
The optimal indexes for this query, which you can copy and create in your database.
An automatically re-written query you can copy and execute in your database.

The optimization process and recommendations:

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:

SELECT
        sometable.blah,
        sometable.blah,
        sometable.blah 
    FROM
        sometable 
    WHERE
        '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 
    ORDER BY
        sometable.LP_LIST_PRICE