Can't force mysql to use index

Question

There are many similar questions about force index but mine is something different because i successfully use index at local dev server but can't on production server.

I have a table with more than 20 millions records MyISAM.

CREATE TABLE `cs` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `upload_id` int(10) unsigned NOT NULL,
  `amount` decimal(9,2) DEFAULT NULL,
  `user_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `upload_id` (`upload_id`)
  KEY `for_where` (`user_id`,`amount`,`upload_id`),
) ENGINE=MyISAM AUTO_INCREMENT=20547623;

i need to run following query:

SELECT COUNT(DISTINCT `t`.`id`)
  FROM `cs` `t`
  LEFT OUTER JOIN `uploads` `Upload` ON (`t`.`upload_id`=`Upload`.`id`)
WHERE (((t.user_id=1) AND (upload_id IN (1,2,3)))
  AND (((Upload.term IS NOT NULL) AND (Upload.term<>"")) AND (t.amount>0)))

query took more than 20 seconds and EXPLAIN says that it "using where"

+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
| id | select_type | table  | type  | possible_keys             | key       | key_len | ref                | rows   | Extra       |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+
|  1 | SIMPLE      | Upload | range | PRIMARY                   | PRIMARY   | 4       | NULL               |      3 | Using where |
|  1 | SIMPLE      | t      | ref   | user_id,for_where,upload_id | upload_id | 4       | mydb.Upload.id | 381427 | Using where |
+----+-------------+--------+-------+---------------------------+-----------+---------+--------------------+--------+-------------+

but if i'll drop key upload_id or just add IGNORE INDEX (upload_id) it will use 'for_where' key and 'Using index condition' and query take only 1,5 sec.

+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
| id | select_type | table  | type   | possible_keys   | key      | key_len | ref                  | rows   | Extra                 |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+
|  1 | SIMPLE      | t      | range  | user_id,for_where | for_where | 11      | NULL                 | 693667 | Using index condition |
|  1 | SIMPLE      | Upload | eq_ref | PRIMARY         | PRIMARY  | 4       | adrevyii.t.upload_id |      1 | Using where           |
+----+-------------+--------+--------+-----------------+----------+---------+----------------------+--------+-----------------------+

it's good result for me so i try to roll out this on production server but no luck. It always say 'Using where' and still run more than 20 sec.

I was check for differences but can't find one. Both, local and production table use MyISAM, similar indexes and same data with one exception — production table have 2 millions records more.

My local server version: 5.6.13 Mac OS My production server version: 5.1.69-0ubuntu0.11.10.1 (Ubuntu)

Why this happens? How i can force production server to use index? May be some server settings can affect it? Is it because version? Can it be fixed? Is it possible to speed this query up more?

Answer 1

Your query can be simplified to:

SELECT COUNT(DISTINCT t.id)
FROM cs AS t
  JOIN uploads AS u 
    ON t.upload_id = u.id
WHERE t.user_id = 1 
  AND t.upload_id IN (1,2,3)
  AND u.term > '' 
  AND t.amount > 0 ;

and also:

SELECT COUNT(*)
FROM cs AS t
WHERE t.user_id = 1 
  AND t.upload_id IN (1,2,3)
  AND t.amount > 0
  AND EXISTS
      ( SELECT *
        FROM uploads AS u 
        WHERE t.upload_id = u.id
          AND u.term > '' 
      ) ;