CODEWITHSUNDEEP
mysqldatabaserelational-databasequery-performance
Amir Mohsen
Amir Mohsen

Reputation: 851

Slow query when use order by with limit 1

I have a table in mysql with over 80M records. In MYISAM engine.

When I run this query 

SELECT id FROM mytable WHERE (key1=-5) AND key2=467476  ORDER BY id  DESC LIMIT 1

query is slow and after 5 minutes I must kill query to release table.

But when i just increase limit size this query work successfully in 44ms

For example (I just increase limit size):

SELECT id FROM mytable WHERE (key1=-5) AND key2=467476  ORDER BY id  DESC LIMIT 2

Now to solve this issue i try the following query and this work successfully (this is temporary solution) 

 SELECT id FROM (SELECT id FROM mytable WHERE (key1=-5) AND key2=467476  ORDER BY id  DESC LIMIT 2) AS tbl ORDER BY id DESC LIMIT 1

Note : id is primary and auto increment !

Update:

key1 ==> folder_id

key2 ==> userid

 CREATE TABLE `bm60_mails` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userid` int(11) NOT NULL DEFAULT '0',
  `betreff` varchar(255) CHARACTER SET utf8 NOT NULL,
  `von` varchar(255) CHARACTER SET utf8 NOT NULL,
  `an` varchar(255) CHARACTER SET utf8 NOT NULL,
  `cc` varchar(255) CHARACTER SET utf8 NOT NULL,
  `body` longtext NOT NULL,
  `folder` int(11) NOT NULL,
  `datum` int(11) NOT NULL DEFAULT '0',
  `trashstamp` int(11) NOT NULL,
  `priority` enum('low','normal','high') NOT NULL,
  `fetched` int(11) NOT NULL DEFAULT '0',
  `msg_id` varchar(128) CHARACTER SET utf8 NOT NULL,
  `virnam` varchar(128) CHARACTER SET utf8 NOT NULL,
  `trained` tinyint(4) NOT NULL DEFAULT '0',
  `refs` text CHARACTER SET utf8 NOT NULL,
  `flags` int(11) NOT NULL DEFAULT '-1',
  `size` int(11) NOT NULL,
  `color` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `mailUser` (`userid`),
  KEY `mailFlags` (`flags`),
  KEY `mailFolder` (`folder`)
) ENGINE=MyISAM AUTO_INCREMENT=95953499 DEFAULT CHARSET=latin1

Upvotes: 2

Views: 421

Answers (1)

O. Jones
O. Jones

Reputation: 108651

I suspect if you create a compound index on (key1, key2, id) your problem will vanish.

The id column is also included because that's not implicit in MyISAM tables, althought it is in InnoDB tables.

Upvotes: 1

Related Questions