0

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?

  • Why do you have a LEF JOIN and then WHERE Upload.term IS NOT NULL? – ypercubeᵀᴹ Apr 17 '14 at 7:15
  • Because i have records with NULL or empty string in term column of uploads table and i don't need it and its associated records in result. – Yaroslav Apr 17 '14 at 10:16
  • I'd also try an index on (user_id, upload_id, amount) – ypercubeᵀᴹ Apr 17 '14 at 12:03
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 > '' 
      ) ;
|improve this answer|||||
  • Thanks. Your first query still runs 1:39 sec locally and user inex condition and 13 secs on production and still using where. But second one runs less than one second. I will accept your query but it isn't about simplifying. I figured out that COUNT(1) instead COUNT(id) solve my issue but still can't understand why i have different explain results locally and on production. – Yaroslav Apr 17 '14 at 23:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.