Как оптимизировать сортировку по полю внешней таблицы?

Question

[ragnar_ok]

Привет!

Есть таблица, которая хранит компании:

create table companies
(
    ID int(11) unsigned auto_increment primary key,
    IS_EXIST  tinyint(1) unsigned,
);

Есть таблица, которая хранит некую дополнительную информацию об этой компании:

create table companies_settings
(
    ID int(11) unsigned auto_increment primary key,
    NAME text null,
    COMPANY_ID int(18) null,
);

Нужно выбрать из companies_settings поле id и сортировать по полю companies.IS_EXIST:

SELECT *
FROM companies_settings
INNER JOIN companies ON companies_settings.COMPANY_ID = companies.ID
ORDER BY companies.IS_EXIST;

Создал индексы по companies.IS_EXIST ("is_exist") и companies_settings.COMPANY_ID ("companies").

Вне зависимости от наличия этих индексов скорость выполнения не меняется. EXPLAIN говорит, что:
1. possible_keys — companies, но при этом key — null;
2. в столбце "extra" EXPLAIN — Using temporary; Using filesort.

Насколько я понял, то Using filesort означает, что индекс по is_exist не используется

1,SIMPLE,companies_settings,NULL,ALL,companies,NULL,NULL,NULL,213950,100,Using where; Using temporary; Using filesort
1,SIMPLE,companies,NULL,eq_ref,PRIMARY,PRIMARY,4,companies_settings.company_id,1,100,Using where

spoiler

Смотрю на колонку Duration / Fetch MySQL Workbench. Без join 0,320 sec, а с join 1,139 sec.

MySQL 5.7.35-38

Answer 1

[Stalker_RED]

Создайте композитный индекс в котором оба поля и пользующихся в запросе.

Comments

[ragnar_ok]

Не очень понял про составной индекс в контексте моего запроса, ведь поле companies.IS_EXIST использующиеся в запросе из другой таблицы. Можете привести пример создания индекса? Возможно в моем запросе индексы вообще бессмысленны?

[Stalker_RED]

ragnar_ok,

ALTER TABLE `companies`
    ADD INDEX `wow_two_columns` (`ID`,`IS_EXISTS`)

[ragnar_ok]

Stalker_RED, этот композитный индекс при запросе из моего поста также не используется (см. possible_keys и key), а используется PRIMARY

[
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "companies_settings",
    "partitions": null,
    "type": "ALL",
    "possible_keys": null,
    "key": null,
    "key_len": null,
    "ref": null,
    "rows": 213950,
    "filtered": 100,
    "Extra": "Using where; Using temporary; Using filesort"
  },
  {
    "id": 1,
    "select_type": "SIMPLE",
    "table": "companies",
    "partitions": null,
    "type": "eq_ref",
    "possible_keys": "PRIMARY,wow_two_columns",
    "key": "PRIMARY",
    "key_len": "4",
    "ref": "companies_settings.company_id",
    "rows": 1,
    "filtered": 100,
    "Extra": "Using where"
  }
]

[Stalker_RED]

ragnar_ok, вы можете сделать композитный primary, или явно указать какой индекс использовать
https://dev.mysql.com/doc/refman/8.0/en/index-hint...

[ragnar_ok]

Stalker_RED, да, вместе с FORCE INDEX работает, но скорость выполнения еще хуже, чем без этого индекса.

[ragnar_ok]

Stalker_RED, а вот FORCE INDEX по не композитному индексу is_exist работает в полтора раза быстрее, так как читает строк значительно меньше. Интересно по какой причине приходится использовать FORCE INDEX, а автоматически он не используется

[Stalker_RED]

ragnar_ok, там же ещё разные виды индексов есть.
И от объёма данных зависит, и от настроек СУБД.
Эксперементируйте, в общем)