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]?

    4 days later

    I can offer little assistance here other than to suggest some trial-and-error type stuff. Note in this you are comparing a string:

    where sxx='1'

    whereas these are ints:

    where sxx IN (1, 2, 3)

    Maybe try strings/ints in both cases to see if that affects anything?

    I'm also wondering if the number of records returned has anything to do with it. No reason to sort anything if you only get one record back. Might try various queries to see if filesort appears at some number threshold.

    MySQL docs have pretty extensive information about how queries are optimized. Here's the main-ish page:
    https://dev.mysql.com/doc/refman/5.5/en/select-optimization.html
    And this one is more specifically about ORDER BY:
    http://dev.mysql.com/doc/refman/5.7/en/order-by-optimization.html

    EDIT: that last link has a lot of information on why filesort is used. For example:

    You index only a prefix of a column named in the ORDER BY clause. In this case, the index cannot be used to fully resolve the sort order. For example, if you have a CHAR(20) column, but index only the first 10 bytes, the index cannot distinguish values past the 10th byte and a filesort will be needed.

      Write a Reply...