Всем доброго времени суток, делаю небольшую биллинговую систему для офисной АТС
требуется помощь в составлении запроса
на текущий момент есть запрос:

запрос выбирает данные с сервера и выдает сформированный ответ кто, куда , стоимость, страна и т.д.
столкнулся с проблемой при доработке запроса, надо выбрать нужную тарифную опцию по наиболее большому количеству совпадений с таблицей tariff-data на основании данных r.uid,sr.uid,i.uid , посредством гугла и других источников была сделана примерно такая конструкция:

она работает только с ручной заменой данных внутри FIND_IN_SET ,как можно доработать основной запрос с этим или подобным функционалом?

Это сообщение отредактировал(а) alligator - 26.8.2015, 19:58

Если вопрос в выборе номера тарифа -- то вписать его попросту в zoneinfo. Поскольку в примере запроса он только от записи в zoneinfo и зависит. В таком случае мне, правда, непонятно, почему Вы тариф называете тарифной опцыей --  тарифная опцыя по смыслу это что-то, что выставляет оператор при заведении абонента.

А если там действительно есть тарифные опцыи, разные для разных пользователей -- то всё равно вписать номер тарифа для направления в zoneinfo, а пользователю прописать номер его тарифной опцыи, и сделать полную таблицу, в которой содержалось бы дэкартово произведение всех тарифов по направлениям (их, дажэ с учётом всех имеющихся стран, всего в пределах двух сотен) и всех тарифных опцый пользователей (ну, их тожэ дажэ в крупных операторах в пределах нескольких сот) -- получится маленькая таблица с десятком тысяч записей о цэнах.

tzirechnoy, вы предлагаете использовать zoneinfo, это подходит только для одного направления а у меня их больше 4, когда я только начинал делать данный запрос он как раз использовал zoneinfo.

Не получиться сделать то что вы предлагаете, например у меня таблица zoneinfo содержит пример 14к записей * на кол-во направлений =4 и получим 56к записей

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

тоесть получаеться когда разбираю звонок у меня получается следующая информация посредством которой надо определить тариф, зону и стоимость звонка в зону.:
звонок на 903 123 45 67 относиться к направлению dst1, зоне Москва и МО(131), провайдер Вымпелком(8), подрегион и NULL

в общем :
dst1 , 131 , 8 , null

На основании полученной информации смотрим таблицу tariff в которой ищем тариф по конкретному направлению, затем получаем для тарифа список тарифицируемых зон в tariff-data и получаем что-то типа такого:

uid    tuid    name    price    isp    region    subregion    currency    description
1    1 [->]    Москва (фикс.)    0.58    NULL    50    NULL    NULL    NULL
2    1 [->]    ФСС (Москва,МО)    1.69    NULL    131,165    NULL    NULL    NULL
3    1 [->]    ФСС (Москва,МО) Вымпелком    1.28    8    131    NULL    NULL    NULL

Мне кажется что такое решение не раздувало бы базу, и было бы более оптимальным с точки зрения кол-ва записей.
Но, ввиду того что опыт работы с базами SQL небольшой, не могу утверждать как лучше сделать и собственно поэтому сюда и обратился....



Это сообщение отредактировал(а) alligator - 27.8.2015, 10:20

Это копейки. Кабы речь шла о сотнях миллионов записей - вот тогда можно было бы кивать на их количество.


Оптимизировать размер ценой производительности и нормализации, усложняя логику, имхо глупо.

Всех понял, поддерживаю, тогда может таблице tariff-data сделать вместо 3-х полей isp,region,subregion одно поле zones и вносить туда zoneinfo uid ,  а проверять FIND_IN_SET ,так будет оптимально?
Если да, то я затрудняюсь сказать какой длинны может получиться строка с zoneinfo uid для москвы к примеру, в какой тип можно выставить поле?


Это сообщение отредактировал(а) alligator - 27.8.2015, 10:54

Звучит разумно.


А вот это - нет.
Нахрена где-то там собирать этот сет, чтобы потом его же парсить? почему нельзя по-человечески нормализовать данные и не плодить хитровывернутых геморроев?

Как я понимаю ваше предложение хранить каждый zoneinfo uid в отдельной строке по каждой зоне тарификации, правильно?

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

И да, в zoneinfo я предлагал указывать как раз индэкс тарифа направления. Их, типично, штук 5 по РФ, возможно ещё 1-5 по своей зоне и штук 150 по остальному миру. Если  всё достаточно маньячно -- то будет около 100 по РФ (на каждый субъект) 150 по остальному миру. Если всё совсем маньячно, и надо описывать в каждом регионе несколько локальных зон -- то будет 400 по РФ. Как видите, никаких 15k нет и в помине -- хотя 15k направлений, действительно, можэт быть (можэт быть и большэ).

Ну и, 4 тарифных опцыи что на 500 тарифов по направлениям, что на 15k -- это совершэнно копеечная таблица, которая замечательно поместится в память (да и дажэ, вероятно, в кэш процэссора), и поиск в которой займёт максимум 100ns (десяток доступов к произвольной области паамяти, если она ещё не в кэшэ), а скорее и меньшэ.

Конечно.
А ещё точнее - я бы предложил провести анализ как положено и полностью нормализовать всю структуру.

Дополнил структуру, в итоге запрос получился таким:

Запрос стал отрабатывать чуток быстрее, но все же недостаточно быстро на текущий момент 43 секунды,  до этого было 52 =(
Сервер на базе виртуальной машины 4 ядра Intel® Core™ i7-4820K CPU @ 3.70GHz\ 4gb ram \ HDD 7200 (физика не нагружена другими виртуалками)
Как можно оптимизировать запрос? уж совсем медленно работает  (
P.S. когда проверяю некоторые запросы отдельно то работает практически мгновенно, но когда в один делаю долго
Explain

Во время выполнения запроса, процессор 100%




Это сообщение отредактировал(а) alligator - 27.8.2015, 15:52

согласен что тяжело понимать, надо видеть всю структуру, выкладываю дамп
P.S для работы запроса необходима MariaDB
Если есть время глянуть и посоветовать как сделать лучше, мне бы это очень помогло......


Это сообщение отредактировал(а) alligator - 27.8.2015, 16:50

Провел небольшие тесты
Тестировал так - 36 сек


В таком виде - 19 сек


так - 9 сек


Добавил индексы + добавил (FORCE INDEX), убрал 1 join получилось 9 секунд (возможно сделать хотябы до 3 секунд?)


Это сообщение отредактировал(а) alligator - 28.8.2015, 11:50

Что-то у меня, как это часто бывает при встрече с MySQL (а последние 15 лет я стараюсь встречаться с ним поменьшэ, но всё-таки периодически приходится), цэнзурные слова закончились. Запрос -- тривиален, если бы не баги оптимизатора мыскля, оно бы из памяти за миллисекунды отрабатывало. Если бы хотя бы spatial index работали, можно было бы через них заставить, никуда бы не делось.

Я ещё подумаю, что можно предпринять. Наверняка что-нибудь придумается. В концэ концов можно попытаться codes в MyISAM выкинуть, и сделать spatial index и симитировать геометрию -- всё равно эта таблица статичная и в транзакцыях не участвует. В общем, подумаю.

Но можэт ты, раз только начал -- возьмёшь postgres? Я понимаю, опыта у окружающих мало, и понимаю, что если переносить в лоб большое приложэние MySQL -- то встретятся и замедления и всё, но он ужэ почти 20 лет в среднем лучшэ, и в нём добиться нетривиального результата в среднем проще, чем в MySQL примерно во всех областях, в которых MySQL применяется.

Ага, нашёл. Кривизну оптимизатора можно нагнуть с помощью хранимки.
Создаю функцыю:


Потом таким запросом выбираю:


И всё ужэ нормально работает. Ну, как нормально, без хранимой оно было бы в два раза быстрее, но хоть так.

Да, поскольку у меня сейчас нет MariaDB -- то сменил этот регексп на простой LIKE, это понятно как вернуть обратно. И добавил поле code_is_valid -- поскольку реально можэт оказаться, что номер не попадает в код. По хорошэму, ещё надо или в get_cuid получать какой-нибудь cuid дажэ если нет такого префикса ABC/DEF или сделать там LEFT OUTER JOIN вместо INNER JOIN на все остальные таблицы.

А, и привёл всё в одну базу, я ещё запощу по этому поводу когда соберусь с мыслями.

И да, тройной индэкс для abcdef, start, end безсмысленен -- можно оставить только первые два поля. То есть третье вообще ни в каких логичных случаях использоваться не будет.

И такое использование BETWEEN для времени не всегда корректно (зависит от СУБД) -- лучшэ писать >= 'сегодня 00:00' AND < 'завтра 00:00'

И то, что я использовал prefix и number в том жэ списке полей, в котором их определил -- это по-моему ANSI SQL standard не очень одобряет и не все базы понимают. Но переписывать как правильно мне лень, да и так вполне идиоматично получилось.

сколько получилось время выполнения?


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

обновил архив с дампом.

потестил еще:


на моем запросе во втором select получилось 11 секунд


убрал третье поле время с 9 секунд сразу поднялось до 36, пока вернул обратно....

это выполнилось мгновенно


добавляю к запросу 



получилось 9 сек

Это сообщение отредактировал(а) alligator - 29.8.2015, 18:15