create table Users(
orgId varchar(64) not null,
id varchar(64) not null,
firstName varchar(64) not null,
lastName varchar(64) not null,
primary key (orgId, id)
)
В таблице — 45 млн записей. orgId — 200 уникальных значений. На один orgId приходится 225 тыс записей. Хочется быстро выполнять вот такие 2 запроса:
-- Q1:
select * from Users where orgId = '123' and firstName like 'a%' and lastName like 'b%'
-- Q2:
select * from Users where orgId = '123' and (firstName like 'a%' or lastName like 'a%')
В обоих случаях 'a%' и 'b%' — это ввод пользователя. Пользователь может ввести 'j' ('j%'), а может 'jonathan' ('jonathan%').
Сделал индекс:
create index OrgIdFirstNameLastName on Users(orgId, firstName, lastName)
Индекс применяется для Q1, запрос работает быстро. Для Q2 не применяется, Mysql использует PRIMARY. Q2 сильно медленнее Q1.
Если добавить "use index":
-- Q2_B:
select * from Users use index (OrgIdFirstNameLastName)
where orgId = '123' and (firstName like 'a%' or lastName like 'a%')
R> orgId varchar(64) not null, R> id varchar(64) not null,
Зачем тут varchar? Заменить на char, на лучше на int.
R>select * from Users where orgId = '123' and firstName like 'a%' and lastName like 'b%'
Создать два индекса: orgId,firstName,lastName и orgId,lastName
R>select * from Users where orgId = '123' and (firstName like 'a%' or lastName like 'a%')
select * from Users where ( orgId = '123' and firstName like 'a%' ) or ( orgId = '123' and lastName like 'a%' )
Здравствуйте, L.K., Вы писали:
LK>Создать два индекса: orgId,firstName,lastName и orgId,lastName
Попробовал — использует PRIMARY всё равно.
LK>select * from Users where ( orgId = '123' and firstName like 'a%' ) or ( orgId = '123' and lastName like 'a%' )
Ну он не настолько глупый же (попробовал, нет разницы)
Здравствуйте, rosencrantz, Вы писали:
R>-- Q2: R>select * from Users where orgId = '123' and (firstName like 'a%' or lastName like 'a%') R>[/code]
R>Как добиться, чтобы Q2 работал быстро?
Здравствуйте, BlackEric, Вы писали:
BE>Здравствуйте, rosencrantz, Вы писали:
R>>-- Q2: R>>select * from Users where orgId = '123' and (firstName like 'a%' or lastName like 'a%') R>>[/code]
R>>Как добиться, чтобы Q2 работал быстро?
BE>Попробуйте заменить or на union select
Индекс по (orgId,firstName,lastName) применяется только для случаев выборки по (orgId), (orgId,firstName), (orgId,firstName,lastName). Для случая (orgId,lastName) он не применяется.
Если кроме индекса (orgId,firstName,lastName) добавить ещё (orgId,lastName), запросы:
-- Q3:
select * from Users where orgId = '123' and firstName like 'a%'
-- Q4:
select * from Users where orgId = '123' and lastName like 'a%'
работают быстро (и union конечно тоже). Но у меня к сожалению нет возможности заменить where/or на select/union/select. Эти запросы строит ORM по куче параметров, приходящих снаружи, и хочется по возможности избежать разной логики "для этого" и "для всего кроме этого".
Если не получится решить проблему какими-то хитрыми индексами или любым другим "общим" решением, скорее всего буду делать отдельный API под этот конкретный запрос.
Здравствуйте, rosencrantz, Вы писали:
R>Если не получится решить проблему какими-то хитрыми индексами или любым другим "общим" решением, скорее всего буду делать отдельный API под этот конкретный запрос.
Or почти всегда просаживает производительность. И заставить его использовать индексы не получается.
В этом случае вам нужно копать в сторону того что бы заставить ORM генерить запрос по другому. Ну или если ваш ORM позволяет, то заставьте тут выполнять запрос напрямую, без генерации.
(попробовал, нет разницы)
