mysql одноколоночный и многоколоночный индекс, поле rows

Тут нужно использовать индекс (f1,f2,f3), всё верно он выбирает. Одноколоночный по f3 однозначно приведёт к проблемам скорости если конечно поля f1 f2 действительно меняются. Чтобы mysql правильнее показывало статистику запроса в explain - попробуй сделать optimize table. Реально он проверит примерно 100 строк - индекс по f1,f2,f3 позволяет сразу выбрать нужные и не заниматься брутфорсом.

optimize table показывает ‘Table does not support optimize, doing recreate + analyze instead’, analyze table тоже не меняет ситуацию с rows

еще заметил что при использовании (f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5 (длина char поля f3), т.е. при использовании первого индекса длина f3 не включается в статистике key_len странно это как то..

скорость запросов в обоих случаях одинаковая

почему mysql … считает это выгодней

Ну значит все правильно считает

Пересоздай ключ, может там при создании подкрутили чтобы из f3 бралось 0 символов.

Table does not support optimize, doing recreate + analyze instead

InnoDb что ли? Тогда проверит не 100 а 200 строк (offset будет вручную пересчитывать), потому что в нём индексы немного порезанные по сравнению с MyISAM. Но всё равно луче чем ключ чисто по f3.

И вообще можешь show create table выложить.

Пересоздай ключ, может там при создании подкрутили чтобы из f3 бралось 0 символов.

для char полей в отличие от text нет необходимости писать длину поля, всё поле индексируется по умолчанию

еще заметил что при использовании (f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5 (длина char поля f3), т.е. при использовании первого индекса длина f3 не включается в статистике key_len странно это как то..

такое поведение наблюдал много где, не зависит от конкретно этой таблицы

InnoDb что ли?

да, MyISAM вроде как умер

Ну как видишь у тебя индексируется не всё поле. Если боишься трогать имеющийся - создай ещё один ключ (f1,f2,f3) с дефолтами и посмотри.

да, MyISAM вроде как умер

Тебя жестоко обманули. Это InnoDb плавно идёт к депрекации, вместо него готовится развивающийся Aria. А в нише быстрых и простых движков MyISAM вне конкуренции как был так и остался.

Сделай `SHOW CREATE TABLE tablename` и `SHOW KEYS IN tablename`

Может быть потому что type «ref» считается лучше чем type «index», а «rows» планировщик в MySQL не анализирует?

Правда ни «500000 для миллиона записей», ни "(f1,f2,f3) key_len - 8 (два int), а при использовании f3 key_len - 5" у меня не получилось воспроизвести.

При вот таком заполнении:

SET SESSION max_recursive_iterations = 2000000;
INSERT INTO test2
	WITH RECURSIVE tgen(f1, f2, f3) AS (
		VALUES (0, 1, char(64))
		UNION ALL
		SELECT f1 + 2, f2 + 2, char(64 + (64 * rand()))
		FROM tgen
		WHERE f1 < 2000001
	)
	SELECT * FROM tgen
;

Может быть MySQL не в курсе какая нужда была строить индексы при куче записей с f1=0 и f2=0, и поэтому считает, что обычно «ref» должен быть лучше.

Может быть потому что type «ref» считается лучше чем type «index», а «rows» планировщик в MySQL не анализирует?

Может быть MySQL не в курсе какая нужда была строить индексы при куче записей с f1=0 и f2=0, и поэтому считает, что обычно «ref» должен быть лучше.

Это могло бы быть аргументами к «почему mysql не хочет использовать трёхколоночный ключ». Но он его очень даже хочет использовать, проблема там только в том как именно он его использует.

Эммм... прям в тупик поставили вывернув всё ровно наоборот.

«трёхколоночный ключ» как раз type=«ref» и дает. Ибо «=». Потому и выбирается он.

А, я не так понял. Но с keylen в 2 инта там не выйдет = же

Я думаю ты что-то путаешь. Используя индекс по f1,f2,f3 можно index scan’ом достать только нужные строки, вообще без единой лишней (за исключением тех то пропускает limit, естественно). А если ты будешь сканировать по индексу включающему только f3, то придётся пропускать все строки для которых f1 != 0 или f2 != 0.

https://onecompiler.com/mysql/3y548udvy

Хм, неожиданно. Ну, могу ещё такое предположить: содержимое индекса используется для WHERE, а сортировку делать не нужно т.к. индекс и так отсортирован. Т.е. находим нужную позицию по 8 байтам а потом просто читаем индекс по очереди, уже не смотря на значения ключа в нём. Вероятно и правда больше чем 8 байт читать не потребуется. Но третье поле индекса таки используется - неявно, потому что при его составлении (не этим запросом, а до) оно учитывалось.

Aria

Aria это движок из MariaDB, а не MySQL.

InnoDb плавно идёт к депрекации, вместо него готовится развивающийся Aria

Всё-таки Aria это замена MyISAM, а не InnoDB. В Aria вроде и не предполагаются блокировки на уровне строк, только таблиц целиком. А это значит, что если записей будет чуть больше, чем почти нисколько, то движок упрётся в блокировки.

Aria это движок из MariaDB, а не MySQL.

А MariaDB это то, куда переехала оригинальная ветка разработки после того, как название MySQL оказалось захвачено проприетарной компанией.

Всё-таки Aria это замена MyISAM, а не InnoDB.

Ну ладно тут спорный вопрос. Но InnoDb это как ни крути сторонний код, хоть и тесно интегрированный в базу. Возможно, заменят в итоге на свой аналог, как бы он ни назывался.