Запрет повторного нахождения

[fog]

Зпарет повторного нахождения

Задача такая

есть две таблицы.

в первой номера телефонов и длительность разговора
id
dnis
duration

во второй коды и названия направлений (стран)
id
code
name

необходимо сгенерировать отчёт по направлениям, куда звонили, просуммировав длительность по каждому направлению.

т.е. в отчёте - все записи из второй таблицы (названия направлений), и напротив - общая длительность.

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

какая была идея:
- таблица со звонками копируется во временную
- все таблицы с кодами помещаются в массив (отсортирован по длинне кода, начиная с длинных)
- массив кодов проходим, делая запрос для каждого кода "SELECT sum(duration) from tmp_calls where dnis like '$code%'"
- затем удаляем совпавшие с этим кодом записи "delete from tmp_calls where cdrs_dnis LIKE '$code%'"
- далее обратно к пункту 1.

Таким образом получается, что для каждого кода находится совпадение, затем совпавшие записи удаляются.

Всё бы хорошо, но только работает очень медленно.

Может кто-нибудь предложит как генерацию такого отчёта можно оптимизировать?

MySQL 4.0
Вот пример функции которая это делает

-~{}~ 23.01.05 13:53:

Записей в таблице направлений (стран) - примерно 3000, звонков 30,000 и будут увеличиваться в десятки раз.
Функция приведённая выше, на довольно мощном сервере отрабатывает за две с половиной минуты.

 

[Yurik]

затем удаляем совпавшие с этим кодом записи

а где гарантия что удалится 12345 а не 12?

fog у тебя ненормализированная структура БД поэтому оптимизировать вряд ли чего получится. Надо чтобы первая таблица связывалась по ключу а не по некоторых ваших богу известных соглашениях.

-~{}~ 24.01.05 03:35:

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

Но сути это не меняет. 2*3000 запросов LIKE 'bla%' это изврат

 

[fog]

Как раз сортируется по длинне и заполняется/проходится массив довольно быстно. Приемлемо во всяком случае. Проблема не в этом месте, а вот как раз в том месте, где начинаются 2*3000 запросов.

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

 

[chira]

fog
Направление звонка нужно определять сразу и записывать в таблицу 1 id направления.
Если в первой таблице будет поле direction_id, которое содержит id из второй таблицы и будет проиндексированно, то получим один запрос:

SELECT d.*, SUM(a.duration) duration_sum
FROM table2 d
INNER JOIN table1 a ON d.id=a.direction_id
GROUP BY d.id

-~{}~ 24.01.05 11:22:

запись из таблицы 2 (направление звонка) можно определить так:

SELECT d.*
FROM table2 d
WHERE 'номер телефона' LIKE CONCAT(d.code,'%')
ORDER BY LENGTH(d.code) DESC
LIMIT 1

 

[Buteo]

fog
совет: сделать поле в первой таблице destination и связать со второй (то что Yurik говорит), и во время добавления звонка в таблицу искать для него id направление...
....
упсь опередили

 

[fog]

chira, Buteo Это был мой первый вариант, который я предложил. Но заполняю её не я, а те кто этим занимается - по какой-то причине отказываются. Приходится выкручиваться.

 

[Buteo]

fog
ну если не сможешь им доказать, что так надо делать, то попробуй такой вариант: сделать еще одну таблицу, куда ты будешь складывать результат подсчетов, например за день...

ну или делать первый вариант самому раз в N часов...

 

[fog]

Да, это вариант!
Раз в n часов выбирать записи, в которых не установлен ID направления, и заполнять их...
Спасибо за идею, как-то не подумал.

 

[chira]

fog
приведи данные к виду , о котором тебе говорят
в результате , с помощью 1-2 update получишь нужные данные и потом быстро всё подсчитаешь

 

[fog]

> приведи данные к виду , о котором тебе говорят

В смысле, поле добавить с ID направления?

 

[chira]

да

 

[fog]

Ну так я об этом пост выше и написал

Да, это вариант!
Раз в n часов выбирать записи, в которых не установлен ID направления, и заполнять их...
Спасибо за идею, как-то не подумал.