Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подскажите, как организовать БД (таблицу) для запросов с like / 25 сообщений из 32, страница 1 из 2
  1  все
19.09.2013, 09:42:40
    #38401051
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Есть таблица:
mac_prefixdescription11:22:33:44:55:66host11:22:33:44:group111:22:33:supergroup111:scope
Есть определенный MAC-адрес <mac> и его нужно классифицировать по всем соответствующим записям.

Вопрос первый — какой запрос лучше?
1. select * from mac_prefixes where <mac> like concat(mac_prefix,'%')
2. select * from mac_prefixes where mac_prefix = left(<mac>,length(mac_prefix))
3. Варианты п.2 с другими функциями вместо left (instr, substr или другое)

Ну и второй вопрос — помогут ли тут индексы?

________________________
Мы смотрим с оптимизмом...
...в оптический прицел.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:16:58
    #38401098
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Эти запросы примерно равноценны и все они не будут использовать индексы.
Можно примерно так:
Код: sql
1.
2.
3.
SELECT * FROM test WHERE mac_prefix IN ('11:22:33:44:55:66','11:22:33:44:55:',
                                        '11:22:33:44:','11:22:33:',
                                        '11:22:','11:')

где константы - фрагменты искомого мака.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:18:51
    #38401100
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Alibek B.,
1)все варианты хреновые, т.к.
2)индексы к ним не подцепятся
Предлагаю растащить <mac> на куски, которые могут встречаться в префиксах - в вашем примере это 6,4,3 и 1 байт -, после чего делать несколько запросов с прямым сравнением (ну или объединить их в один через union all).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:19:43
    #38401102
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
tanglirчерез union allмда, через in оно получше будет :)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:21:51
    #38401107
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
С in не получится, потому что как раз список префиксов динамический и может быть довольно большим.

А почему во втором варианте индекс не будет использоваться? Ведь это field=expression, разве нет?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:23:52
    #38401112
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
miksoftгде константы - фрагменты искомого мака.
А, понял.
То есть на клиенте вместо MAC-адреса получить шесть его фрагментов и использовать их.
Да, такое подходит, спасибо.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:29:08
    #38401120
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
tanglirtanglirчерез union allмда, через in оно получше будет :)Не уверен, надо пробовать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:31:59
    #38401126
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
miksoft,

ну шанс на использование индекса вроде такой же, как и в юнион-варианте
а проход будет всего один, в отличие от
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 10:42:56
    #38401137
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
tanglirmiksoft,

ну шанс на использование индекса вроде такой же, как и в юнион-варианте
а проход будет всего один, в отличие от"проходов" (сканов) при наличии индекса не будет ни одного в обоих вариантах.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 11:00:33
    #38401160
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
miksoft,

проходов по индексу будет 4 (столько, сколько будет вариантов мак-суффикса) в случае с юнионом
и всего один в случае с ином
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 11:19:32
    #38401189
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
tanglirmiksoft,

проходов по индексу будет 4 (столько, сколько будет вариантов мак-суффикса) в случае с юнионом
и всего один в случае с иному нас разные трактовки слова "проход" :)
для меня это table/index scan
а в обсуждаемых запросах будет index seek

И, кстати, почему с IN будет только один "проход"? MySQL же не может быть уверен, что константы в IN находятся в индексе подряд или даже в одной ветке индекса.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 13:44:18
    #38401465
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Alibek B.Есть таблица:
mac_prefixdescription11:22:33:44:55:66host11:22:33:44:group111:22:33:supergroup111:scope
Есть определенный MAC-адрес <mac> и его нужно классифицировать по всем соответствующим записям.

Вопрос первый — какой запрос лучше?
1. select * from mac_prefixes where <mac> like concat(mac_prefix,'%')
2. select * from mac_prefixes where mac_prefix = left(<mac>,length(mac_prefix))
3. Варианты п.2 с другими функциями вместо left (instr, substr или другое)

Ну и второй вопрос — помогут ли тут индексы?Что-то такого типа (допускающее использование индекса):
3.select * from mac_prefixes where mac_prefix >= '11:22:33:' and mac_prefix < '11:22:34:'

И непонятно, что такое <mac> - поле другой таблицы? константа? еще что-то?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 13:45:30
    #38401470
tanglir
tanglir
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Судя по тексту предлагавшихся ТСом запросов, константа.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:07:06
    #38401600
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Константа или параметр.

Cygapb-0073.select * from mac_prefixes where mac_prefix >= '11:22:33:' and mac_prefix < '11:22:34:'

Почему '11:22:33:' и '11:22:34:', а не '11:22:33:44:' и '11:22:33:45:' или '11:' и '12:'?
А также этот запрос не учитывает того, что мне нужно получить все префиксы, соответствующие MAC-адресу (с сортировкой по длине mac_prefix).
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:07:29
    #38401604
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Конвертируйте МАС в, скажем, bigint. Префикс - соответственно в пару от-до. И запрос станет простым и быстрым.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:10:58
    #38401610
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
AkinaКонвертируйте МАС в, скажем, bigint. Префикс - соответственно в пару от-до. И запрос станет простым и быстрым.Простым - может быть. Быстрым - не быстрее, имхо, моего варианта.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:11:18
    #38401611
Alibek B
Alibek B
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
AkinaКонвертируйте МАС в, скажем, bigint. Префикс - соответственно в пару от-до. И запрос станет простым и быстрым.
Вообще-то он у меня varbinary(6). В принципе можно и в bigint преобразовать.

То есть предлагаете использовать такую таблицу префиксов:
mac_frommac_todescription11:22:33:44:55:6611:22:33:44:55:66host11:22:33:44:00:0011:22:33:44:FF:FFgroup111:22:33:00:00:0011:22:33:FF:FF:FFsupergroup111:00:00:00:00:0011:FF:FF:FF:FF:FFscope
Да, тоже выход. Но заполнение таблицы усложнится и возможны коллизии.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:31:31
    #38401653
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Alibek B.Константа или параметр.

Cygapb-0073.select * from mac_prefixes where mac_prefix >= '11:22:33:' and mac_prefix < '11:22:34:'

Почему '11:22:33:' и '11:22:34:', а не '11:22:33:44:' и '11:22:33:45:' или '11:' и '12:'?
А также этот запрос не учитывает того, что мне нужно получить все префиксы, соответствующие MAC-адресу (с сортировкой по длине mac_prefix).Тогда, наверное, вот так?
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
select 11,22,33,44,59,66 -- получение параметров (int/varchar)
  into @m1, @m2, @m3, @m4, @m5, @m6;

select p.*
from(
  select concat_ws(':',@m1,@m2,@m3,@m4,@m5,@m6) mac
  union -- all
  select concat_ws(':',@m1,@m2,@m3,@m4,@m5,'') 
  union -- all
  select concat_ws(':',@m1,@m2,@m3,@m4,'') 
  union -- all
  select concat_ws(':',@m1,@m2,@m3,'') 
  union -- all
  select concat_ws(':',@m1,@m2,'') 
  union -- all
  select concat_ws(':',@m1,'') 
  )u 
join mac_prefixes p on p.mac_prefix=u.mac;

и индекс работает
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:34:18
    #38401663
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Alibek B.AkinaКонвертируйте МАС в, скажем, bigint. Префикс - соответственно в пару от-до. И запрос станет простым и быстрым.
Вообще-то он у меня varbinary(6). В принципе можно и в bigint преобразовать.

То есть предлагаете использовать такую таблицу префиксов:
mac_frommac_todescription11:22:33:44:55:6611:22:33:44:55:66host11:22:33:44:00:0011:22:33:44:FF:FFgroup111:22:33:00:00:0011:22:33:FF:FF:FFsupergroup111:00:00:00:00:0011:FF:FF:FF:FF:FFscope
Да, тоже выход. Но заполнение таблицы усложнится и возможны коллизии.
1) VarBinary для этой задачи - смерть.
2) Заполнение не усложнится - просто потребуются пара пользовательских функций преобразования.
3) Запрoс будет тупо between, и выполняться должен быстро при правильной индексации
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:35:24
    #38401666
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Ну а коллизии... их не должно быть при правильном проектировании заполнения.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:39:45
    #38401672
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Блин... это ж МАКи, а не IPv6, т.е. реально префиксы, а не диапазоны...
Всё вышесказанное - фтопку.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 15:51:38
    #38401691
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
AkinaБлин... это ж МАКи, а не IPv6, т.е. реально префиксы, а не диапазоны...
Всё вышесказанное - фтопку.Почему же? Имхо, Ваш вариант тоже вполне имеет право на жизнь. По крайней мере в рамках текущей задачи.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 16:00:52
    #38401705
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
miksoftAkinaБлин... это ж МАКи, а не IPv6, т.е. реально префиксы, а не диапазоны...
Всё вышесказанное - фтопку.Почему же? Имхо, Ваш вариант тоже вполне имеет право на жизнь. По крайней мере в рамках текущей задачи.Согласен, да и поиск по двум int быстрее, чем по varchar(23) (=6*3+5)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 16:10:34
    #38401732
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
Cygapb-007да и поиск по двум int быстрее, чем по varchar(23) (=6*3+5)Не понял откуда цирфы. Имхо, надо сравнивать 2*bigint и 6*2+5+1 (6 пар цифр, 5 двоеточий, байт длины), т.е. 16<18. При максимальном заполнении разница в пользу 2*bigint, но незначительная. При продемонстрированном заполнении разница явно будет в пользу строк.

Если уж минимизировать расходы, то хранить префикс в bigint и отдельно байт длины. Будет всего 9 байт.
И это еще не предел...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.09.2013, 16:25:14
    #38401757
Cygapb-007
Cygapb-007
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Подскажите, как организовать БД (таблицу) для запросов с like
miksoftCygapb-007да и поиск по двум int быстрее, чем по varchar(23) (=6*3+5)Не понял откуда цирфы. Имхо, надо сравнивать 2*bigint и 6*2+5+1 (6 пар цифр, 5 двоеточий, байт длины), т.е. 16<18. При максимальном заполнении разница в пользу 2*bigint, но незначительная. При продемонстрированном заполнении разница явно будет в пользу строк.

Если уж минимизировать расходы, то хранить префикс в bigint и отдельно байт длины. Будет всего 9 байт.
И это еще не предел...FF:FF:FF:FF:FF:FF => 255:255:255:255:255:255 - максимальное значение
Ну, плюс байт длины, забыл :) =6*3+5+1=24
Но он в varchar неявный, потому varchar(23)

А хотя да, where @mac between mac_from and mac_to вынужденно будет читать все mac_from <= @mac
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Подскажите, как организовать БД (таблицу) для запросов с like / 25 сообщений из 32, страница 1 из 2
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 363ms
Закрыть
start [/forum/topic.php?fid=47&mobile=1&tid=1836014]:
 0ms
get settings:
 8ms
get forum list:
 19ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 48ms
get topic data:
 12ms
get forum data:
 3ms
get page messages:
 69ms
get tp. blocked users:
 1ms
others: 197ms
total:  363ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]