This is my MySQL table:
CREATE TABLE IF NOT EXISTS `ank` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`uid` int(11) NOT NULL DEFAULT '0',
`skr` int(11) NOT NULL,
`sxx` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `sxx` (`sxx`,`skr`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;
--
-- Dumping data for table `ank`
--
INSERT INTO `ank` (`id`, `uid`, `skr`, `sxx`) VALUES
(1, 34846, 1443865873, 1),
(2, 34847, 1443777739, 1),
(3, 34848, 1443777741, 2),
(4, 34849, 1443777743, 3),
(5, 34850, 1443777745, 0),
(6, 34851, 1443777747, 0);
This simple query is OK:
explain SELECT id, skr FROM ank force index (sxx) where sxx='1' order by skr DESC limit 0, 30;
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | ank | ref | sxx | sxx | 1 | const | 1 | Using where |
+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+
but when I use
where sxx IN (1, 2, 3)
instead of
where sxx='1'
, it always uses filesort. I tried everyhing - to include end exclude order by column in SQl statement, to include all columns, to not include all columns, tried order by primary column, but always the same result: using_filesort.
explain SELECT id, skr FROM ank force index (sxx) where sxx IN (1, 2, 3) order by skr DESC limit 0, 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | ank | range | sxx | sxx | 1 | NULL | 3 | Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------------+
But the strangest thing is that if I join the same table with GROUP_CONCAT, using_filesort disappears.
explain SELECT n.id, n.skr, GROUP_CONCAT(f.uid) fll
-> FROM ank n
-> force index (sxx)
-> left join ank f on n.uid=f.uid
-> where
-> n.sxx IN (1, 2, 3)
-> order by n.skr DESC limit 0, 30;
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | n | range | sxx | sxx | 1 | NULL | 3 | Using where |
| 1 | SIMPLE | f | ALL | NULL | NULL | NULL | NULL | 6 | |
+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+
I don't need to join the same table in this query.Is there another way how to avoid using_filesort when I use [FONT=Courier New]where sxx IN (1, 2, 3)[/FONT]?