MySQL ereg?

zip111 · 1 Июн 2011

Здравствуйте.

есть пару вопросов по оптимизации запроса к базе.
В двух словах - таблица `prices` содержащая около 5 миллионов записей со стоимостью минуты телефонного разговора того или иного провайдера связи.

Таблица `pricelists` содержит группы цен, этих самых прайсов, с разницей по времени, когда их присылал оператор-поставщик.

Таблица billing_packs содержит названия поставщиков и имеет ключевую связь с таблицей `terminators`, в которой содержится тех информация о поставщиках.

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

ползьователь вводит номер 380501234567

в прайсах есть коды 38050, 3805, 38, так вот правильным будет нахождение кода 38050, как наиболее полного совпадения.

Поехали...

пробую так: (для просто будем считать, что пользователь ввел 38050)

SELECT * FROM config.terminators
LEFT JOIN config.billing_packs ON billing_packs.id = terminators.billing_pack_id
LEFT JOIN config.pricelists ON pricelists.billing_pack_id = billing_packs.id
LEFT JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW()
AND (
prices.code = (LEFT('38050',5) OR LEFT('38050',4) OR LEFT('38050',3) OR LEFT('38050',2) OR LEFT('38050',1))

)
AND prices.enabled = 'y'

в целом, запрос выводит почти то, что нужно, НО очень долго.

Смотрим Explain

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE terminators ALL NULL NULL NULL NULL 73
1 SIMPLE billing_packs eq_ref PRIMARY PRIMARY 2 config.terminators.billing_pack_id 1 Using where
1 SIMPLE prices ALL pricelist_id NULL NULL NULL 4012929 Using where
1 SIMPLE pricelists eq_ref PRIMARY PRIMARY 2 config.prices.pricelist_id 1 Using where

не круто, идет полный перебор прайсов, не подходит...

пробую махинировать с LIKE '38050%' (или например '38050' LIKE CONCAT() ) - тоже полный перебор, хотя я всегда думал, что база юзает индексы в любом случае при такой конструкции.

пробую регулярки - тоже полный перебор...

В общем пока идеи кончились.. подскажите плиз?

Второй вопрос такой:
мне нужно отобрать только те прайсы, у которых в таблице `pricelists` строка `activation_time` (datetime) уже наступило. пока что я сделал просто WHERE pricelists.activation_time >= NOW(), но это не совсем верно, так как может быть такая ситуация, что есть прайслисты от одного и того же поставщика, с датой активации например 2011-05-30 00:00:00 и 2011-06-03 00:00:00, то есть нужно выбрать только то, что фактически наступило (2011-05-30 00:00:00), а в случае с WHERE pricelists.activation_time >= NOW() выберет обе записи.

Тут пока вообще идей нет.

Заранее всем спасибо

Gas · 1 Июн 2011

чтоб перебора всей таблицы prices не было, действительно используй like + индекс на первые 5 символов по полю code
для начала примерно так попробовал бы (не запускал):

Код:

select t.*
from
(
  select p.billing_pack_id
  from
  (
    select pricelist_id, 1 AS sort from prices where code like '38050%' and enabled = 'y'
    union
    select pricelist_id, 2 AS sort from prices where code like '3805%' and enabled = 'y'
    union
    select pricelist_id, 3 AS sort from prices where code like '380%' and enabled = 'y'
  ) as t1
  join pricelists AS p ON p.id = t1.pricelist_id and p.activation_time >= NOW()
  order by sort ASC
  limit 1
) t2
join billing_packs AS bp ON bp.id = t2.billing_pack_id
join terminators as t ON t

Gas · 1 Июн 2011

но всё зависит ещё от селективности данных

вполне возможно что лучше использовать связки

from pricelists AS pl
join prices AS p ON pl.id = p.pricelist_id
where pl.activation_time >= NOW() AND p.code like '38050%' and p.enabled = 'y'

тогда на таблицу prices лучше составной индекс (pricelist_id,code(5))

Если в базе нет секретной инфы, можешь выложить дамп, повертел бы

zip111 · 1 Июн 2011

У меня что-то не хочет использовать индекс при LIKE '38050%'

составной UNIQUE индекс есть - (pricelist_id,code)

если явное указание code = '38050' то WHERE всего по 600 записям, если LIKE - полный перебор

Gas · 1 Июн 2011

приведи пример запроса где не используется и explain, можно и show create table prices для полноты картины

zip111 · 1 Июн 2011

EXPLAIN SELECT *
FROM config.terminators
JOIN config.billing_packs ON billing_packs.id = terminators.billing_pack_id
JOIN config.pricelists ON pricelists.billing_pack_id = billing_packs.id
JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW( )
AND prices.code LIKE '38050%'
AND prices.enabled = 'y'

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE terminators ALL NULL NULL NULL NULL 73
1 SIMPLE billing_packs eq_ref PRIMARY PRIMARY 2 config.terminators.billing_pack_id 1 Using where
1 SIMPLE prices ALL pricelist_id NULL NULL NULL 3521049 Using where
1 SIMPLE pricelists eq_ref PRIMARY PRIMARY 2 config.prices.pricelist_id 1 Using where

явное указание

explain SELECT * FROM config.terminators
JOIN config.billing_packs ON billing_packs.id = terminators.billing_pack_id
JOIN config.pricelists ON pricelists.billing_pack_id = billing_packs.id
JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW()
AND prices.code = '38050' and prices.enabled = 'y'

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE terminators ALL NULL NULL NULL NULL 73
1 SIMPLE billing_packs eq_ref PRIMARY PRIMARY 2 config.terminators.billing_pack_id 1 Using where
1 SIMPLE pricelists ALL PRIMARY NULL NULL NULL 679 Using where
1 SIMPLE prices ref pricelist_id pricelist_id 55 config.pricelists.id,const 1 Using where

zip111 · 1 Июн 2011

даже WHERE не по 600 записей, а по 1.. меняется последовательность запроса

zip111 · 1 Июн 2011

и что еще страннее

explain SELECT * FROM config.terminators
JOIN config.billing_packs ON billing_packs.id = terminators.billing_pack_id
JOIN config.pricelists ON pricelists.billing_pack_id = billing_packs.id
JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW()
AND (
(prices.code = '38050' AND prices.enabled = 'y') OR
(prices.code = '3805' AND prices.enabled = 'y') OR
(prices.code = '380' AND prices.enabled = 'y')
)

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE terminators ALL NULL NULL NULL NULL 73
1 SIMPLE billing_packs eq_ref PRIMARY PRIMARY 2 config.terminators.billing_pack_id 1 Using where
1 SIMPLE prices ALL pricelist_id NULL NULL NULL 4247155 Using where
1 SIMPLE pricelists eq_ref PRIMARY PRIMARY 2 config.prices.pricelist_id 1 Using where

InnoDB....

Gas · 1 Июн 2011

похоже mysql выбирает не лучший план,
попробуй сделать optimize table prices и pricelists или во вложенном запросе получить pricelists и уже к ним джойни billing_packs и terminators

select from
(
select
from pricelists
join prices
) as t
join billing_packs
join terminators

а вообще начни с простого, что говорит
explain SELECT * FROM config.pricelists
JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW()
AND prices.code like '38050%' and prices.enabled = 'y'

zip111 · 1 Июн 2011

Да, OPTIMIZE помогло - теперь база правильно юзает индекс

explain SELECT * FROM config.terminators
JOIN config.billing_packs ON billing_packs.id = terminators.billing_pack_id
JOIN config.pricelists ON pricelists.billing_pack_id = billing_packs.id
JOIN config.prices ON prices.pricelist_id = pricelists.id
WHERE pricelists.activation_time >= NOW()
AND (
(prices.code = '38050' AND prices.enabled = 'y') OR
(prices.code = '3805' AND prices.enabled = 'y') OR
(prices.code = '380' AND prices.enabled = 'y')
)

1 SIMPLE terminators ALL NULL NULL NULL NULL 73
1 SIMPLE billing_packs eq_ref PRIMARY PRIMARY 2 config.terminators.billing_pack_id 1 Using where
1 SIMPLE pricelists ALL PRIMARY NULL NULL NULL 343 Using where
1 SIMPLE prices ref pricelist_id pricelist_id 5 config.pricelists.id 1363 Using where

Однако все равно запрос выполняется больше 2-х секунд, что неприемлемо.
А как постпупать с оптимизацией таблицы? Ставить в крон раз в неделю OPTIMIZE?

Gas · 1 Июн 2011

Однако все равно запрос выполняется больше 2-х секунд, что неприемлемо.

1. во вложенном запросе получить pricelists и уже к ним джойни billing_packs и terminators
2. попробовать использовать несколько запросов через union вместо OR

Gas · 1 Июн 2011

и всё таки выложи show create table prices;

zip111 · 1 Июн 2011

CREATE TABLE `prices` (
`id` bigint(5) unsigned NOT NULL auto_increment,
`pricelist_id` int(11) default NULL,
`enabled` enum('y','n') NOT NULL default 'y',
`code` varchar(16) NOT NULL,
`price` double NOT NULL default '0',
`connect_price` double NOT NULL default '0',
`min` int(10) unsigned NOT NULL default '0',
`increment` int(10) unsigned NOT NULL default '1',
`free` int(10) unsigned NOT NULL default '0',
`priority` int(11) NOT NULL default '0',
`toll_free` enum('y','n') NOT NULL default 'n',
`huntstop` enum('y','n') NOT NULL default 'n',
`_agent_id` int(11) NOT NULL,
`_department_id` int(11) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `pricelist_id` (`pricelist_id`,`code`)
) ENGINE=InnoDB AUTO_INCREMENT=13708812 DEFAULT CHARSET=utf8

zip111 · 1 Июн 2011

explain select t.*
from
(
select p.billing_pack_id
from
(
select pricelist_id, 1 AS sort from prices where code = '38050' and enabled = 'y'
union
select pricelist_id, 2 AS sort from prices where code = '3805' and enabled = 'y'
union
select pricelist_id, 3 AS sort from prices where code = '380' and enabled = 'y'
) as t1
join pricelists AS p ON p.id = t1.pricelist_id and p.activation_time >= NOW()
order by sort ASC
limit 1
) t2
join billing_packs AS bp ON bp.id = t2.billing_pack_id
join terminators as t ON t.billing_pack_id = bp.id

id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived2> system NULL NULL NULL NULL 1
1 PRIMARY bp const PRIMARY PRIMARY 2 const 1 Using index
1 PRIMARY t ALL NULL NULL NULL NULL 73 Using where
2 DERIVED <derived3> ALL NULL NULL NULL NULL 654 Using filesort
2 DERIVED p eq_ref PRIMARY PRIMARY 2 t1.pricelist_id 1 Using where
3 DERIVED prices ALL NULL NULL NULL NULL 3921898 Using where
4 UNION prices ALL NULL NULL NULL NULL 3921898 Using where
5 UNION prices ALL NULL NULL NULL NULL 3921898 Using where
NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL

Так вообще жесткач.. только explain 8 секунд...

zip111 · 1 Июн 2011

и уже слетела оптимизация.... нужно по новой делать. База очень нагруженная

Gas · 1 Июн 2011

Так вообще жесткач.. только explain 8 секунд...

это потому что нужно добавить отдельный индекс alter table prices add index code(code(5)), а если enabled = 'y' имеет не большой процент среди всех записей, тогда лучше составной индекс code(code(5),enabled)

zip111 · 1 Июн 2011

Да, я забыл добавить, что структуру и индексы трогать нельзя!

Mols · 1 Июн 2011

Что значит "нельзя трогать" ?
Их и не надо трогать.... надо добавить ещё один индекс.
Потому, что существующий индекс не может и не должен работать в ваших запросах.
А если и добавлять нельзя - тогда о чем речь? Низя - значит низя. Ждите пока будет зя)))

zip111 · 1 Июн 2011

ну нельзя вносить изменения в дерево индексов и структуру... это реально работающий программный комплекс по маршрутизации телефонных звонков провайдера сотовой связи. Если вдруг, что-то где-то пойдет не так, производитель заявит, что было вмешательство в базу и может отказать в поддержке. Так что это полностью исключено и "зя" не появится, к сожалению..

пока наиболее оптимальный и скорострельный (0,05с) вариант через юнионы

(
SELECT * FROM prices
JOIN pricelists ON pricelists.id = prices.pricelist_id
JOIN billing_packs ON billing_packs.id = pricelists.billing_pack_id
JOIN terminators ON terminators.billing_pack_id = billing_packs.id
WHERE pricelists.activation_time >= NOW()
AND prices.code = '38050'
AND prices.enabled = 'y'
) UNION (
SELECT * FROM prices
JOIN pricelists ON pricelists.id = prices.pricelist_id
JOIN billing_packs ON billing_packs.id = pricelists.billing_pack_id
JOIN terminators ON terminators.billing_pack_id = billing_packs.id
WHERE pricelists.activation_time >= NOW()
AND prices.code = '3805'
AND prices.enabled = 'y'
) UNION (
SELECT * FROM prices
JOIN pricelists ON pricelists.id = prices.pricelist_id
JOIN billing_packs ON billing_packs.id = pricelists.billing_pack_id
JOIN terminators ON terminators.billing_pack_id = billing_packs.id
WHERE pricelists.activation_time >= NOW()
AND prices.code = '3805'
AND prices.enabled = 'y'
)

Конечно, это костыли, но реально работает и быстро. Других здравых идей все равно пока нет (

craz · 1 Июн 2011

в темповую таблицу свалить все и там сделать "зя" с индексами, не?

MySQL ereg?

Новичок

может по одной?

может по одной?

Новичок

может по одной?

Новичок

Новичок

Новичок

может по одной?

Новичок

может по одной?

может по одной?

Новичок

Новичок

Новичок

может по одной?

Новичок

Новичок

Новичок

Нестандартное звание