Slow SQL in large table, filter by timestamps(int12)

I have a problem with slow query, making a "simple" select.. i don't understand what is the problem... it's a big table.. but开发者_StackOverflow中文版 it's a simple query..

The tables:

business: (1.000.000 reg)
id (INDEX PRIMARY INT(11) UNSIGNED)
active (INDEX TINYINT(1))

products: (32.000.000 reg)
id (INDEX PRIMARY INT(11) UNSIGNED)
business_id (INDEX INT(11) UNSIGNED)
offer_start (INDEX INT(12) UNSIGNED) (timestam unix)
offer_end (INDEX INT(12) UNSIGNED) (timestamp unix)
price_offer (VARCHAR(10)) (price with decimals)
active (INDEX TINYINT(1))

business.id, products.id, products.offer_start and products.offer_end are INDEX (separated)

When I make this:

SELECT SQL_NO_CACHE * FROM products 
LEFT JOIN business ON business.id = products.business_id 
WHERE
 (business.active = '1' AND business.paylimit > 1314029906) 
AND 
 (products.active = '1' AND products.offer_start < 1314029906 AND products.offer_end > 1314029906 AND products.price_offer > 0) 
LIMIT 0,10

Take 21 Seconds. The problem is this: products.offer_start < 1314029906 AND products.offer_end > 1314029906 takes ~20 seconds to give me results

Is it possible to make this query, filtering other forms to speed up?

---

Some notes about your table schema:

1. offer_start (INDEX INT(12) UNSIGNED) - 12 doesn't make sense because the max symbols in INT is 11. The same for offer_end
2. active (INDEX TINYINT(1)) - index is useless because of cardinality on 1000000 will be 2 - 1 or 0
3. price_offer (VARCHAR(10)) - you can use float or decimal for this one. products.price_offer > 0 will work faster.

---

Try

SELECT SQL_NO_CACHE * FROM products 
    LEFT JOIN business ON business.id = products.business_id 
WHERE business.paylimit > 1314029906
    AND products.offer_start < 1314029906
    AND products.offer_end > 1314029906
    AND products.price_offer > 0
    AND business.active = '1'
LIMIT 0,10

MySQL processes filters from left to right so make sure that the condition that is the most selective (returns the least rows) is on the left. Having business.active = '1' on the leftmost side of a condition may use an index, but if it's selectivity is 50% then the rest of the condition doesn't use an index.

You may want to read How MySQL Uses Indexes in the manual.

Edit: A short explanation on how MySQL uses indexes: 3 ways MySQL uses indexes