Использование сортировки файлов для сортировки по столбцу даты и времени в MySQL

Question

У меня есть таблица Cars с datetime (ДАТА) и бит (PUBLIC).

Теперь я хотел бы взять строки, упорядоченные по DATE и с PUBLIC = 1, поэтому я использую:

select
  c.*
from
  Cars c
WHERE 
   c.PUBLIC = 1
ORDER BY 
   DATE DESC

Но, к сожалению, когда я использую объяснить, чтобы увидеть, что происходит, у меня есть это:

1   SIMPLE  a   ALL     IDX_PUBLIC,DATE     NULL    NULL    NULL    103     Using where; Using filesort

И эти данные занимают 0,3 мс, а у меня всего 100 строк. Есть ли другой способ отключить сортировку файлов?

Если я иду к индексам, у меня есть индекс на (PUBLIC, DATE) не уникальный.

Таблица определения:

CREATE TABLE IF NOT EXISTS `Cars` (
  `ID` int(11) NOT NULL auto_increment,
  `DATE` datetime NOT NULL,
  `PUBLIC` binary(1) NOT NULL default '0'
  PRIMARY KEY  (`ID`),
  KEY `IDX_PUBLIC` (`PUBLIC`),
  KEY `DATE` (`PUBLIC`,`DATE`)
) ENGINE=MyISAM  AUTO_INCREMENT=186 ;

Answer 1

Вам нужно иметь составной индекс на (public, date)

Сюда, MySQL будет фильтровать public и сортировать по date,

От твоего EXPLAIN Я вижу, что у вас нет составного индекса на (public, date),

Вместо этого у вас есть два разных индекса public и на date, По крайней мере, так их зовут IDX_PUBLIC а также DATE сказать.

Обновить:

Вы public колонка не является BIT, это BINARY(1), Это тип символов и использует сравнение символов.

При сравнении целых чисел с символами, MySQL преобразует последнее в первое, а не наоборот.

Эти запросы возвращают разные результаты:

CREATE TABLE t_binary (val BINARY(2) NOT NULL);

INSERT
INTO    t_binary
VALUES
(1),
(2),
(3),
(10);

SELECT  *
FROM    t_binary
WHERE   val <= 10;

---
1
2
3
10

SELECT  *
FROM    t_binary
WHERE   val <= '10';
---
1
10

Либо поменяй public столбец, чтобы быть bit или перепишите ваш запрос следующим образом:

SELECT  c.*
FROM    Cars c
WHERE   c.PUBLIC = '1'
ORDER BY 
        DATE DESC

сравнивать символы с символами, а не с целыми числами.

Answer 2

Если вы заказываете по дате, потребуется сортировка. Если индекса по дате нет, будет использована сортировка файлов. Единственный способ избавиться от этого - либо добавить индекс по дате, либо не выполнять заказ по.

Кроме того, сортировка файлов не всегда подразумевает, что файл будет отсортирован на диске. Это может быть сортировка в памяти, если таблица достаточно мала или буфер сортировки достаточно велик. Это просто означает, что сама таблица должна быть отсортирована.

Похоже, у вас уже есть индекс на дату, и, поскольку вы используете PUBLIC в предложении where, MySQL должен иметь возможность использовать этот индекс. Однако оптимизатор, возможно, решил, что, поскольку у вас так мало строк, не стоит беспокоиться об индексе. Попробуйте добавить около 10000 строк в таблицу, повторно проанализировать ее и посмотреть, изменит ли это план.