CODEWITHSUNDEEP
mysqlsqlsql-optimization
Kit Sunde
Kit Sunde

Reputation: 37095

SELECT on MIN column value and CHAR column really slow in MySQL

I'm reasonable sure the answer to this lies in having a different index. I have a query that's unreasonably slow, but only when it's in the following complete form, if I remove parts of the query it's blazing fast, how can I make it better?

Slow:

SELECT json
  FROM requests
  WHERE spider = 'foo'
    AND load_count = ( SELECT MIN( load_count ) FROM requests )
    AND load_count < 50
  LIMIT 500;

EXPLAIN:

+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+
| id | select_type | table    | type | possible_keys           | key          | key_len | ref   | rows   | Extra                        |
+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+
|  1 | PRIMARY     | requests | ref  | load_count,spider_index | spider_index | 90      | const | 200845 | Using where                  |
|  2 | SUBQUERY    | NULL     | NULL | NULL                    | NULL         | NULL    | NULL  |   NULL | Select tables optimized away |
+----+-------------+----------+------+-------------------------+--------------+---------+-------+--------+------------------------------+

Database structure:

CREATE TABLE `requests` (
  `added` int(11) NOT NULL AUTO_INCREMENT,
  `url` char(255) NOT NULL,
  `spider` char(30) NOT NULL,
  `referer` char(255) DEFAULT NULL,
  `json` text NOT NULL,
  `load_count` int(11) NOT NULL DEFAULT '0',
  `processed` tinyint(1) NOT NULL DEFAULT '0',
  `invalid` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`added`),
  UNIQUE KEY `url` (`url`),
  KEY `load_count` (`load_count`),
  KEY `spider_index` (`spider`)
) ENGINE=MyISAM AUTO_INCREMENT=5285840 DEFAULT CHARSET=utf8

After updating my index like Neo suggested I get drastic improvements:

+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+
| id | select_type | table    | type | possible_keys     | key               | key_len | ref         | rows | Extra                        |
+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+
|  1 | PRIMARY     | requests | ref  | spider_load_count | spider_load_count | 94      | const,const | 1487 | Using where                  |
|  2 | SUBQUERY    | NULL     | NULL | NULL              | NULL              | NULL    | NULL        | NULL | Select tables optimized away |
+----+-------------+----------+------+-------------------+-------------------+---------+-------------+------+------------------------------+

Upvotes: 0

Views: 618

Answers (3)

Neo
Neo

Reputation: 5463

alter table requests drop index load_count;
alter table requests drop index spider_index;

alter table requests add index spider_load_count(load_count, spider);

Upvotes: 1

pkt1975
pkt1975

Reputation: 98

A few comments/suggestions:

  • Have you tried using the MySQL Explain Statement on your Slow SELECT statement? This probably will give you some indication of the problem.
  • I suspect the issue with the slow query is that it has both spider and load_count in the WHERE clause, but no index that covers both fields. Adding an index with both in will probably fix this example.
  • The first two queries have "AND load_count < 50" in the WHERE, which is not needed as you also have a "load_count = [exact value]". MySQL will ignore the "AND load_count < 50" in it's query optimisation.

Upvotes: 0

Devart
Devart

Reputation: 122022

What about this?

SELECT MIN(load_count) INTO @min_load_count FROM requests;

SELECT json
  FROM requests
  WHERE load_count = @min_load_count
    AND load_count < 50
  LIMIT 500;

And having index on spider field may help you.

Upvotes: 0

Related Questions