MariaDB, storage engine и тип физических носителей

Ещё раз, НЕЛЬЗЯ на innodb использовать COUNT на постоянку т.к. этот тип таблиц НЕ имеет счётчика количества строк и ВСЕГДА их пересчитывает. 

LIKE '%___%') это не полноценный поиск. Это просто поиск вхождения при чём индекс ВООБЩЕ не используется при таком поиске. Вообще никакой и никогда.

Полнотекстовый это match against с использование fulltext index. И тут есть свои нюансы т.к. его нельзя использовать с другими условиями иначе он будет тормозить. А так же он будет медленно работать пока не загрузит индекс в память полностью.

Mobiaaa, UNIX_TIMESTAMP()-864000 и php time()-864000, подставленное в запрос, не дает существенной разницы по времени выполнения запроса (засекаю время только mysqli_query, fetch не произвожу). Не заметил. Предполагаю что MySQL считает цифру один раз до прохода по таблице.

LEOnidUKG, это тест! Оптимизация базы и подбор носителя для ее хранения. Реальные запросы, даже самые тяжелые, на старом железе занимают до 5 секунд, а я хочу доли секунды. Я сейчас намеренно гоняю базу по всей таблице, в результате чего у меня по 44 секунды на запрос идет. Если при каком-то действии 44 секунды вдруг уменьшатся до 30 это эффективная оптимизация, если бы это было 4.4 против 3.0 я бы разницу не заметил и списал разницу на любой другой фактор (крон задача  в этот момент обращалась к памяти или что-то еще).  В одном из реальных запросов используется COUNT после выбора по критериям, и подсчитываются только несколько тысяч записей. Пусть подсчитываются, без этого нельзя и это не занимает много ресурсов если все остальное работает быстро.

Осилил приближенное вычисление значения id (primary key) по date (timestamp) с точностью достаточной для выполнения необходимой задачи. Выигрыш WHERE  id>xxx  (id unsigned int(4) - primary) перед WHERE date>yyy  (date unsigned int(4), индекс ) на MyISAM в 3.5 раза, Ariadb 3.25 раз, InnoDB в 7.4 раза. Это уже успех, но останавливаваться на этом пока еще не готов. Всё равно пока еще не понял, почему primary работает быстрее обычного индекса.

> это не полноценный поиск

Пардон, фигню сморозил. Да, полнотекстовый использовал в какой-то задаче но не здесь.

> Вообще никакой и никогда

А об этом можно подробнее? При сравнении 2 запросов

SELECT * FROM table1 WHERE [key1]=[value1] AND [key2]=[value2] AND date>[xxx]
SELECT * FROM table1 WHERE [key1]=[value1] AND [key2]=[value2] AND date>[xxx] AND myvarchar LIKE '%blablabla%'

получаю во втором существенно меньшее число строк, удовлетворяющих условиям, но время, затраченное на сам запрос, одинаково для всех механизмов хранения. Например, MyIsam 22.22 и 21.46 сек, InnoDB 82.41 и 79.16 сек.

explain для обоих запросов посмотрите

Сравниваю 2 запроса:

EXPLAIN EXTENDED SELECT * FROM table1 WHERE key1=value1 AND key2=value2 AND id>97262353;
EXPLAIN EXTENDED SELECT * FROM table1 WHERE key1=value1 AND key2=value2 AND date>1590969600;

При условии по primary key (id) type=range, по дате ref.

possible_keys в 1 случае PRIMARY, key2, key1;  во втором - date, key2, key1 (видимо, обратный порядок это норма).

Длина ключа 8 ref=NULL, если по дате длина 4 ref = const. Сильно сокращает время "Using index condition".

Что с длиной ключа я не понимаю, key1 - tinyint (1 байт), key2, id и date - int (4 байта). Как из этого получаются цифры 4 и 8 не ясно, используются только 2 индекса, на остальные база кладёт? Чуть раньше пробовал принудительно USE INDEX на key1, key2, date либо просто key1, key2 - разницы по времени не заметил.

По настройкам самой базы данных не понятно, может для MyISAM выделены ресурсы, для InnoDB зажаты, поэтому такая и разница.

По хранению тоже не очень понятно, InnoDB может хранить всё в одном файле, может в разных.

Настройки my.cnf и доступные ресурсы укажите, так будет понятнее

possible_keys  - это возможные индексы, но не факт, что они будут использоваться при выполнении запроса

Используемые ключи в запросе это  key

key_len это длинна используемого индекса/ов (составного индекса)

Дефолтные настройки MySQL для Debian 9. File_per_Table включен, ненавижу братские могилы. Лимит по открытым файлам с запасом, потому можно.