Помогите оптимизировать SQL запрос

[eyeland]

самый простой вариант:
SELECT id FROM table WHERE mapid3 IN (SELECT mapid3 FROM table WHERE url LIKE '%url1%')

можно еще с JOINами хитро завернуть, но я неприпомню как там оно в мухле само с собой джоинится или не джоинится

 

[Explore]

ну наконец-то
спасибо

 

[efreet]

eyeland, имхо один фиг в два прохода запрос выполняться будет. в первом случае перебор всех и сравнение url по like, а во втором случае сравнение с подмножеством в in.
имхо, тормоза еще те будут.
если индексов по mapid нет, либо значений mapid мало, то два прямых перебора вроде вырисовываются. что не есть гут.


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

PS
спец по БД из меня не слишком серьезный, но последнее время приходится ковыряться с оптимизацией запросов и индексов в postgresql. авось этот маленький опыт будет полезным.

/*подумав еще чуток*/
хотя если таблица не слишком большая, то и прямой перебор будет выполняться достаточно быстро.

 

[eyeland]

индексы полюбому должны быть, и по хорошему там должно быть как минимум 2 таблицы...

 

[efreet]

индексы полюбому должны быть, и по хорошему там должно быть как минимум 2 таблицы...

поддерживаю.

 

[Explore]

про индексы напишите подробнее. куда и как их ставить

суть такова.
утрируя - есть страны есть города в них.
все в одной таблице и ID соотвенно по порядку.

есть урл на страну, нужно получить список городов.

из урла на страну я достаю уникальную для страны часть, по нему найдя через objurl строку страны получаю ID этой карты, а по нему получаю список городов на ней.

предполагаемое кол-во записей 2000-3000 тысячи. думаете на таком кол-ве записей будет заметно ускорение?

 

[eugene_kr]

Исходя из всего вышеприведенного:
objurl - это текстовая ссылка на город, который находится на карте mapid3 и идентифицируется полем id.

Скорее всего поле id - содержит уникальные значения. Учитывая то, что objurl - уникальное текстовое поле и (можно предположить исходя из названия) используется для идентификации города можно посоветовать в objurl закодировать mapid3, например "URI://CityName.com&mapid3=XXX&etc" Тогда, разобрав (средствами php) строку objurl можно получить mapid3 и запрос останется только один:

$query = 'SELECT id FROM #__mapsfo WHERE mapid3='.$m_id;

Этому запросу, естественно, поможет индекс по полю mapid, и еще больше может помочь индекс одновременно по двум полям mapid3, id (именно в такой последовательности, возможно, запрос выглядит примерно так - "create index IX_#__mapsfo on #__mapsfo(mapid3, id)").

 

[Explore]

хм... мысль кажется дельная.
на выходных я ее покурю

 

[romziki]

утрируя - есть страны есть города в них.
все в одной таблице и ID соотвенно по порядку

разнеси страны и города в разные таблицы...

 

[efreet]

разнеси страны и города в разные таблицы...

+1

из урла на страну я достаю уникальную для страны часть, по нему найдя через objurl строку страны получаю ID этой карты, а по нему получаю список городов на ней.

можешь вынести эту уникальную для страны часть в отдельное поле?
я так понимаю это что-то в духе "ru" , "ua", "us", "ee" и т.п.?
если предположение верное, то сравнение на равенство будет выполняться быстрее чем сравнение по like.

т.е. предварительно структура будет выглядеть так

страны - country
pid - автоинкремент - внутренний идентификатор страны.
url - строковый - урл по которому ты искал.
uni_url - уникальная для страны часть урла

города city
pid - автоинкремент
country_id - идентификатор страны в которой расположен город - foreign key на country.pid
name - строковое название города.

потом делаешь запрос вида
select ct.* from city ct join country cnt on ct.country_id=cnt.pid where cnt.uni_url=?

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

предполагаемое кол-во записей 2000-3000 тысячи. думаете на таком кол-ве записей будет заметно ускорение?

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

PS
eugene_kr чуть раньше уже сказал другими словами.
суть в том, чтобы вытянуть из урла подстроку по которой будет выполняться поиск и уложить ее отдельно.

 

[eugene_kr]

разнеси страны и города в разные таблицы...

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

В данном случае отсутствие отдельного запроса для выяснения идентификатора карты, join'а и наличие индекса для разрешения запроса получения идентификаторов городов по коду карты - будет оптимальнее.

 

[eyeland]

С точки зрения оптимизаци данный совет - сомнительное улучшение, так как #__mapsfo, скорее всего, и есть таблица "городов".
Вообще совет "разнести таблицы" (привести таблицы к более высокой нормальной форме) на практике приводит к действиям, которые противоположны тем (действиям), которые предпринимают с целью "оптимизации (улучшения производительности) запросов по извлечению данных".

тоесть хотите сказать что если ТС разнесет данные на 2 таблицы то все начнет тормозить? тормозить оно не начнет, а нормальная форма будет более высокая, и это ок.

В данном случае отсутствие отдельного запроса для выяснения идентификатора карты, join'а и наличие индекса для разрешения запроса получения идентификаторов городов по коду карты - будет оптимальнее.

спасибо капитан очевидность

 

[eugene_kr]

тоесть хотите сказать что если ТС разнесет данные на 2 таблицы то все начнет тормозить? тормозить оно не начнет, а нормальная форма будет более высокая, и это ок.
...

Главный вопрос - для чего будет "ок" эта "более высокая" нормальная форма? Это будет "ок" для устранения т.н. аномалий обновления и (или) избыточности данных. В приведенной ТС схеме избыточности не наблюдается и, вполне возможно, что степень "нормализованности" здесь выше, чем кажется некоторым ;-).
О наличии/отсутствии проблем обновления данных в проведеной схеме - в данный момент ничего не известно.
В случае разнесения данных на 2 таблицы - во-первых, объём данных увеличится (в сравнении с изначальным объёмом исходной "неизбыточной" таблицы) и, как следствие, вычислительные затраты возрастут (факт); во-вторых - операция объединения таблиц (sql join) имеет гораздо больше возможностей неоптимального выполнения, чем операция выборки данных из одной таблицы (тоже факт).

 

[eyeland]

Главный вопрос - для чего будет "ок" эта "более высокая" нормальная форма? Это будет "ок" для устранения т.н. аномалий обновления и (или) избыточности данных.

это будет ок для простоты. следуйте принципам KISS (keep it simple, stupid) или как говорится в import this -- Simple is better than complex. Complex is better than complicated.

В приведенной ТС схеме избыточности не наблюдается и, вполне возможно, что степень "нормализованности" здесь выше, чем кажется некоторым ;-).

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

О наличии/отсутствии проблем обновления данных в проведеной схеме - в данный момент ничего не известно.
В случае разнесения данных на 2 таблицы - во-первых, объём данных увеличится (в сравнении с изначальным объёмом исходной "неизбыточной" таблицы) и, как следствие, вычислительные затраты возрастут (факт);

не факт. объем увеличится, но затраты на выборку не возрастут.

во-вторых - операция объединения таблиц (sql join) имеет гораздо больше возможностей неоптимального выполнения, чем операция выборки данных из одной таблицы (тоже факт).

не факт. даже с одной таблицей будет 2 поиска. если не верите -- посмотрите explain

 

[eugene_kr]

это будет ок для простоты. следуйте принципам KISS (keep it simple, stupid) или как говорится в import this -- Simple is better than complex. Complex is better than complicated.

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

не факт. объем увеличится, но затраты на выборку не возрастут.

не факт. даже с одной таблицей будет 2 поиска. если не верите -- посмотрите explain

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

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

Теоретические постулаты - не учитывают специфику конкретного приложения, поэтому применять их нужно с умом.

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

Предлагаю дождаться ТС.

 

[efreet]

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

поддерживаю предложение дождаться ТС и предложение сравнить оба решения.

 

[Explore]

Вот и я.
Пока я решил оставить как есть, разве что добавил индекс по mapid3

Честно говоря разделение на 2 страницы грозит мне кучей гемора и переписыванием сотен строчек кода. Мне сейчас только этого и не хватает

 

[eugene_kr]

Вот и я.
Пока я решил оставить как есть, разве что добавил индекс по mapid3

Честно говоря разделение на 2 страницы грозит мне кучей гемора и переписыванием сотен строчек кода. Мне сейчас только этого и не хватает

Если запрос остался в виде подобном "SELECT id FROM table WHERE mapid3 IN (SELECT mapid3 FROM table WHERE url LIKE '%url1%')", (поиск по mapid3 и вывод только поля id во "внешнем" запросе) то специализированный индекс по двум столбцам (mapid3, id) будет полезнее, так как позволяет выполнить запрос с помощью одного полного сканирования таблицы и сканирования ограниченного диапазона индекса. Если же индекс только по mapid3, то еще дополнительно добавляется индексный доступ к строкам таблицы, время выполения которого зависит от состояния данных.

p.s. Созданием специализированных многостолбцовых индексов следует "заморачиваться", если есть действительная потребнось разобраться, как сделать приложение более эффективным при определённых условиях использования бд.

 

[eyeland]

Две таблицы - это сложнее чем одна, так как две сущности это две сущности, а не одна.

2 таблицы проще чем одна, т к это знатный шаблон применяемый повсеместно, в то время как одна таблица с таким вот полем это нестандартно, и может вызвать проблемы при изменении схемы БД.

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

какая разница 2 раза осуществить поиск в одном индексе, или по одному поиску в 2х индексах?
лучше приведите пример как должен выглядеть запрос по-вашему?

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

Какое такое множество? Вы о чем? Это факт? Откуда он высосан? Где эти приложения? Что за голословие в треде? Где модератор? лол

Если запрос остался в виде подобном "SELECT id FROM table WHERE mapid3 IN (SELECT mapid3 FROM table WHERE url LIKE '%url1%')", (поиск по mapid3 и вывод только поля id во "внешнем" запросе) то специализированный индекс по двум столбцам (mapid3, id) будет полезнее

Вы ошибаетесь, в этом запросе индекс по id вообще не используется, это же простейший запрос, очевидно что поиск осуществляется по mapid3 и url. Хотя в случае с url он неработает, т к аргумент LIKE начинается с %, в доках по мухле это описано, если не верите -- посмотрите explain.

 

[eugene_kr]

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

В любой обстоятельной литературе посвящённой реляционной теории практически всегда есть раздел "нормализация vs производительность". Обычно, он небольшого объёма и размещён в конце, так что многие, возможно, даже не подозревают о его существовании.

Для разрешения запроса по таблице, который результат ограничен по полю mapid3 и проектируется только поле id - достаточно обратиться только к данным индекса по полям (mapid3, id) без обращения к самой таблице. Документацию можно посмотреть здесь - dev точка mysql точка com/doc/refman/5.0/en/mysql-indexes.html

Моё предложение ТС - озвучено выше для одного из вариантов запроса (одного сканирования таблицы ему не избежать, а вот "индексный доступ к таблице" можно исключить, оставив только сканирование диапазона индекса).

p.s. Ответ на вопрос "Какая разница?", дам как и раньше - "возьмите да проверьте, если не верите в теорию и логику" ;-)

P.p.s. Надеюсь, что тс в данной дискуссии что-нибудь найдёт полезным.