VPF::MYSQL: Индексы какие создать - Форум программистов

EntityFx

Дата 19.10.2010, 18:35 (ссылка)

(нет голосов)

Загрузка ...

Новичок

Профиль
Группа: Участник
Сообщений: 27
Регистрация: 30.3.2009

Репутация: нет
Всего: нет

Код


CREATE TABLE QUKI.SITE_USERS(
  id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT 'Уникальный идентификатор пользователя',
  mail VARCHAR(100) NOT NULL COMMENT 'Почта',
  `password` BINARY(32) NOT NULL COMMENT 'Пароль',
  ip INT(11) UNSIGNED NOT NULL COMMENT 'IP-адрес регистрации',
  register_date DATE NOT NULL COMMENT 'Дата регистрации',
  name VARCHAR(25) NOT NULL COMMENT 'Имя человека',
  second_name VARCHAR(50) NOT NULL COMMENT 'Фамилия',
  gender TINYINT(1) NOT NULL DEFAULT 1,
  burthday DATE DEFAULT NULL,
  photo VARCHAR(255) DEFAULT NULL,
  country TINYINT(4) UNSIGNED DEFAULT NULL COMMENT 'Страна',
  region INT(11) UNSIGNED DEFAULT NULL COMMENT 'Регион',
  city INT(11) UNSIGNED DEFAULT NULL COMMENT 'Город',
  street VARCHAR(100) DEFAULT NULL COMMENT 'Улица',
  `utc_time` TINYINT(2) DEFAULT 0 COMMENT 'Часовой пояс',
  state TINYINT(2) UNSIGNED DEFAULT 0 COMMENT 'Статус: забанен/активен/не активен',
  update_time INT(10) UNSIGNED DEFAULT NULL,
  online TINYINT(1) DEFAULT 0 COMMENT 'ONLINE ли пользователь',
  permissions SMALLINT(6) DEFAULT 1792 COMMENT 'Права доступа к пользователю',
  PRIMARY KEY (id),
  INDEX INDX_NAME (name),
  UNIQUE INDEX mail (mail),
  FULLTEXT INDEX photo (photo)
)
ENGINE = MYISAM

Выборка может происходить по следующим полям (любая комбинация):

name
second_name
gender
burthday

К пример,ЗАПРОСЫ:

Код



SELECT `id` FROM `SITE_USERS` WHERE `name` LIKE '%Артём%' AND `second_name` LIKE '%Жириновский%' AND `gender` = 0 AND `burthday` BETWEEN '1985-10-20' AND '1989-10-19' ORDER BY `name` ASC LIMIT 0,30

SELECT `id` FROM `SITE_USERS` WHERE `name` LIKE '%Артём%' ORDER BY `name` ASC LIMIT 0,30


SELECT `id` FROM `SITE_USERS` WHERE `name` LIKE '%Артём%' AND `second_name` LIKE '%Жириновский%' ORDER BY `name` ASC LIMIT 0,30


SELECT `id` FROM `SITE_USERS` WHERE `second_name` LIKE '%Жириновский%' ORDER BY `name` ASC LIMIT 0,30

SELECT `id` FROM `SITE_USERS` WHERE `burthday` BETWEEN '1985-10-20' AND '1989-10-19' ORDER BY `name` ASC LIMIT 0,30

Какие типы индексов создать посоветуете создать.
К примеру 1,000,000 записей выполняются:

Без индексов - 5 секунд
Если слева убрать % - 0,5 секунд
С индексами (на кажде поле отдельный) - 0,7 секунд
Если слева убрать % и есть индексы - 0,04 секунд

Хочется чтоб в LIKE и слева и справа были %

Это сообщение отредактировал(а) EntityFx - 19.10.2010, 18:44

Akina

Дата 19.10.2010, 21:25 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 45
Всего: 454

Fulltext index?

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

skyboy

Дата 19.10.2010, 22:02 (ссылка)

(нет голосов)

Загрузка ...

неОпытный

Профиль
Группа: Модератор
Сообщений: 9820
Регистрация: 18.5.2006
Где: Днепропетровск

Репутация: 15
Всего: 260

поиск по части текста для миллионов записей(если это не учебная задача) никогда не делается ни по substring, ни по, упаси Господь, like(тем более - Regexp/rlike - это уже вообще безумие). для всех запросов, кроме тех, которые запрашивают текст "с краю"(например, like "a%") обычные текстовые индексы применить невозможно. потому для поиска совпадения внутри текстового блока(такой поиск называют полнотекстовым) либо используют специальный тип ключей и специальную структуру запроса(Akina именно на это и дает наводку - ищи "fulltext index" на dev.mysql.com), либо же внешнюю программу, которая производит индексирование и поиск(рекомендую sphinx)

Zloxa

Дата 20.10.2010, 09:28 (ссылка)

(голосов:1)

Загрузка ...

Чо?

Профиль
Группа: Завсегдатай
Сообщений: 3473
Регистрация: 12.9.2008

Репутация: 53
Всего: 161

Думаю, следует отметить, что fulltext будет искать с начала слова. Т.е. по 'Жир*' найдет Жироновского, а по '*ириновский' - нет.

--------------------

Достоверно известно, что 89% людей доверяют статистике взятой с потолка smile

1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| Составление SQL-запросов \| Следующая тема »