Форум пользователей MySQL

Да Вы оптимально делаете вполне...
Создание таблицы:

mysql> create table t_6230(id int primary key auto_increment, start int);
Query OK, 0 rows affected (0.19 sec)

mysql> insert into t_6230(start) values (2),(5),(10),(11),(20),(21);
Query OK, 6 rows affected (0.00 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t_6230;
+----+-------+
| id | start |
+----+-------+
|  1 |     2 |
|  2 |     5 |
|  3 |    10 |
|  4 |    11 |
|  5 |    20 |
|  6 |    21 |
+----+-------+
6 rows in set (0.00 sec)

mysql> create index start_index on t_6230(start);
Query OK, 6 rows affected (0.12 sec)
Records: 6  Duplicates: 0  Warnings: 0

Теперь анализ запроса:

mysql> EXPLAIN extended SELECT * FROM `t_6230` WHERE `start`<=13 ORDER BY `start`  DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_6230
         type: range
possible_keys: start_index
          key: start_index
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

Это Ваш вариант запроса. Почему Вы пишете, что индекс не используется?

Можно улучшить вот так (если нужно только значение start, без id ). В этом случае будет использоваться только индекс.

mysql> EXPLAIN extended SELECT start FROM `t_6230` WHERE `start`<=13 ORDER BY `start`  DESC LIMIT 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_6230
         type: range
possible_keys: start_index
          key: start_index
      key_len: 5
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

я собственно вот про что хочу сказать.

Отредактированно Roin (10.11.2012 03:20:49)

Roin написал:

я собственно вот про что хочу сказать.

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

заметил особенность - если искать значения в первых записях ну скажем 10ое или 15ое число - то индекс используется. но если ищем например 70 001ое то не используется
т.е например 70 001 это число 1547444223, я ищу 1547444224.

Приведенные вами запросы (без сортировки) это совсем другая история. Если вы добавите ко второму запросу order limit, то увидите, что индекс будет использоваться.
В вашем примере он не используется, так как по условию `(start`<=2959187969) выбирается большая часть таблицы и быстрее будет последовательно перебрать всю таблицу, чем прыгать с индекса на данные в каждой строке (речь идет о перемещении головки диска между разными областями).

Roin написал:

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

Чем вас не устраивает вариант приведенный вами в первом сообщении? Никакого просмотра всех строк там нет.

Roin написал:

И вопрос заодно:
поиск по бинарному полю в 16 байт будет быстрее поиска по символьному полю в 2000 байт при использовании индекса в любом из случаев?

Длина ключа влияет на производительность, чем короче ключ, тем лучше.

Roin написал:

только я несмог создать на url индекс (#1071 - Specified key was too long; max key length is 1000 bytes ) само поле varchar(1000) почему непонятно

Ограничение длины ключа - 1000 байт. Максимальный размер поля varchar(1000) - 1000 символов.
Если кодировка не однобайтная, например utf-8, то максимальная длина ключа будет 333 символа, а вы пытаетесь построить ключ на 1000 символов.Отсюда и ошибка.

В таких случаях делают префиксный ключ на часть поля, достаточную для того, чтобы совпадений было не много.
index(`имя поля`(кол-во символов))
например
index(`url`(20))

В предыдущем посте забыл сказать спасибо.
Посчет второго вопроса - а как бы Вы посоветовали хранить/искать урлы ?

Воопще конечно 1000 символов для урла эта тоже редкость. хотя ограничения в разных браузерах и хттп-серверах поболее, но в реале думаю такое редко, да, и я буду обрезать такие урлы перед запросом в бд. Нам такое счастье ненадо в 1000 символов поэтому думаю обрезать ну хотя бы до 500 например. т.е поле будет varchar(500), но, даже 500 это много. в среднем думаю урлы занимают где то до 100 символов. Это покрывает большинство. Поэтому может быть действительно как Вы и советуете - создать префиксный индекс например на 100 байт для этого поля.
Т.е большая часть урлов будет при поиске укладываться в индекс и много процентов селектов будут быстрыми. Или же поиск с приминением префиксного индекса иной - идет поиск первых 100 байт по индексу, а потом уже по отфильтрованнмоу ишется остальная часть поля (т.е 400 байт) уже по диску ? или же учитывается длина поступившей строки на поиск ? т.е например если поле  varchar(500) и индекс 100 и пришел запрос на поиск http://yandex.ru/page1 в 23 байта то будет поиск по всем 500 байтам(100 по индексу и из отсеившихся 400 по диску) ?  или же наш запрос найдется только по индексу ? плохо понимаю этот момент

Или же проще найти мд5 и поискать по binary(16) ? но ведь на вычисление мд5 тоже нужны такты. а алгоритм мд5 не из легкийх...
заранее благодарен за помощ

Отредактированно Roin (11.11.2012 02:18:12)

vasya написал:

Roin написал:

Не устраивает тем, что затрагивается слишком много строк для меня, а именно

http://screenshotuploader.com/i/01/hney6v6r9.png

В моем алгоритме на файле - затрагивается всегда 17 записей структур

План выполнения запроса составляется до выполнения запроса. И rows показывает оценочное число строк, которое серверу предстоит перебрать. Основывается на том сколько приблизительно строк удовлетворяют условию stat<=2959187968. Учитывая order by limit 1 реально поиск завершится на первом же совпадении.

завершится на первом же совпадении написал:

а если не будет точного совпадения? ну нет такого числа 2959187968, есть число 2959187967. то будут прочитаны все записи ?

vasya написал:

Roin написал:

Т.е большая часть урлов будет при поиске укладываться в индекс и много процентов селектов будут быстрыми. Или же поиск с приминением префиксного индекса иной - идет поиск первых 100 байт по индексу, а потом уже по отфильтрованнмоу ишется остальная часть поля (т.е 400 байт) уже по диску ? или же учитывается длина поступившей строки на поиск ? т.е например если поле  varchar(500) и индекс 100 и пришел запрос на поиск http://yandex.ru/page1 в 23 байта то будет поиск по всем 500 байтам(100 по индексу и из отсеившихся 400 по диску) ?  или же наш запрос найдется только по индексу ? плохо понимаю этот момент

идет поиск первых 100 байт по индексу, а потом уже по отфильтрованнмоу проверяется остальная часть поля (т.е 400 байт) по диску.

Понятно. А если искомая строка меньше 100 байт - то поиск все арвно будет 100+400 ? или же поиск будет только в пределах индексов ?

Roin написал:

Понятно. А если искомая строка меньше 100 байт - то поиск все арвно будет 100+400 ? или же поиск будет только в пределах индексов ?

Только по индексу, имхо.

Скажите а Вы бы как решали мою задачу быстрого хранения и поиска урлов ?

vasya написал:

Roin написал:

Или же проще найти мд5 и поискать по binary(16) ? но ведь на вычисление мд5 тоже нужны такты. а алгоритм мд5 не из легкийх...
заранее благодарен за помощ

Индекс на колонке мд5урл можно будет использовать только для поиска точного равенства (ни для диапазонов, ни для сортировки он работать не будет).
Затраты на вычисление мд5 будут у вашего приложения (например, php), а не базы. Если узким местом является база и нужно только точное равенство, то оправдано будет использовать доп колонку и мд5урл.

Но преждевременная оптимизация зло, такие вещи лучше проверять на практике.

Затраты на мд5 будут у моего приложения это верно. Но и приложение, и сервер мускул - сидят на одном процессоре - количество тиков процессора одно для всех разницы большой не будет что мускул вычислит мд5 что пхп - ну плюс минус пару десятков/сотен тактов разницы вычисления мд5 в пхп и в мускуле - погоды не изменят.
Воопще мне нужен поиск точного равенства.
Проверить на практике к сожалению не могу, реальной нагрузки нету, и как имулировать нагрузку тоже незнаю, поэтому все на теории

По теории, если Вы утверждаете что поиск строки меньшей 100 байт только по префиксным индексам в 100байт - то в 90+% у меня будет попадать на индексы.
Ну даже пусть будет нетак. пусть будет ситуация где индекс занимает все 500 байт.
1. при варианте с мд5 идут нехилые затрты на его вычисление, 128 бит это весьма сложное вычисление + кстати затраты на вычисление хеша(mysql'ем) для поиска индекса тоже есть. Зато индекс всего 16 байт.
2.varchar.
отличия от мд5:
и там и там вычисляется хеш искомой строки для определения индекса.
при мд5 еще затраты на вычисление мд5.
при варчар - длинный индекс- поиск по нему медленнее

что выигрывает ?

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

А почему чем длиннее индекс тем дольше поиск ? опять из за долгого хода иглы винчестера ?

Roin написал:

А почему чем длиннее индекс тем дольше поиск ? опять из за долгого хода иглы винчестера ?

Чтобы поиск по индексу работал быстро, он должен полностью помещаться в кэш индексов в оперативной памяти.

Roin написал:

vasya написал:

Roin написал:

Не устраивает тем, что затрагивается слишком много строк для меня, а именно

http://screenshotuploader.com/i/01/hney6v6r9.png

В моем алгоритме на файле - затрагивается всегда 17 записей структур

План выполнения запроса составляется до выполнения запроса. И rows показывает оценочное число строк, которое серверу предстоит перебрать. Основывается на том сколько приблизительно строк удовлетворяют условию stat<=2959187968. Учитывая order by limit 1 реально поиск завершится на первом же совпадении.

Извините, но я так и не понял сколько же в действительности будет прочитано структур с диска? одна или неизвесно количество ? количество фиксирвоанное или зависит от положения числа в таблице ?

По индексу будет найдена граница диапазона (это зависит от положения числа в таблице) и от неё первое значение вниз.