Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > MySQL > Правильная индексация полей


Автор: Fally 5.3.2008, 15:06
Итак, здравствуйте. Есть таблица с информацией о пользователях вида:
Код

CREATE TABLE `user_info` (
  `user_info_id` int(11) unsigned NOT NULL auto_increment,
  `user_info_rname` char(25) NOT NULL,
  `user_info_rsurname` char(25) NOT NULL,
  `user_info_born_date` date NOT NULL,
  `user_info_addr_path` char(50) NOT NULL,
  `user_info_sex` enum('MALE','FEMALE') NOT NULL,
  `user_info_icq` char(11) NOT NULL,
  `user_info_find_work` enum('NO','YES') NOT NULL default 'NO',
  `user_info_end_university` year(4) default NULL,
  `user_info__user_id` int(11) unsigned NOT NULL default '0',
  `user_info__now_city__city_id` int(11) unsigned NOT NULL default '0',
  `user_info__country_id` tinyint(4) unsigned NOT NULL default '0',
  `user_info__university_id` int(11) unsigned NOT NULL default '0',
  `user_info__facultet_id` int(11) unsigned NOT NULL default '0',
  `user_info__specialization_id` int(11) unsigned NOT NULL default '0',
  `user_info__born_city__city_id` int(11) unsigned NOT NULL default '0',
  `user_info__religion_id` tinyint(4) unsigned NOT NULL default '0',
  `user_info__polit_mean_id` tinyint(4) unsigned NOT NULL default '0',
  PRIMARY KEY  (`user_info_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

На сайте есть форма поиска по многим параметрам.
Некоторые из этих параметров располагаются явно в таблице, другие располагаются в других таблицах.
В зависимости от введённых в форму данных, PHP автоматически генерирует запрос, например такой:
Код

SELECT 
`user_info`.`user_info_id` , `user_info`.`user_info_rname` ,  
`user_info`.`user_info_rsurname` , `university`.`university_name` ,  
`user_info`.`user_info_addr_path` , `facultet`.`facultet_name`
FROM `user_info` , `university` , `facultet`
WHERE `user_info`.`user_info_rname` = 'fsd'
AND `user_info`.`user_info_rsurname` = 'fsgbdd'
AND `user_info`.`user_info_end_university` = '2011'
AND `user_info`.`user_info_born_date` = '11-04-1989'
AND `university`.`university_name` = 'fsvxcvzxd'
AND `facultet`.`facultet_name` = 'fcfsvxcvzxd'

Вся проблема в том, что я даже не могу представить себе, как расставить индексы именно в таблице user_info для наиболее оптимального выполнения запроса.
Проблема в том, что имена полей, которые участвуют в запросе могут меняться в зависимости от введённых данных, также может меняться и их количество. Таким образом изменяемыми могут быть:
user_info_rname, user_info_rsurname, user_info_born_date, user_info_sex, user_info_find_work, user_info_end_university.
Если кто знает, способ решения, пожалуйста поделитесь (:

Заранее благодарен.

P.S. Обязательных полей в запросе нет, т.е. если пользователь не заполнить форму поиска, то будет проведена выборка из таблицы без условий.

Автор: SelenIT 7.3.2008, 17:27
Имхо, если добавление юзеров происходит существенно реже выборок, есть смысл проиндексировать по отдельности всё, "что движется" - кроме enum-ов.
Цитата(Fally @  5.3.2008,  15:06 Найти цитируемый пост)
`user_info_icq` char(11) NOT NULL,

Почему не int (unsigned)? Имхо, и места займет меньше, и выборка по индексу быстрее...

Автор: Fally 8.3.2008, 00:35
SelenIT:
1) Так, проблема как раз в том, что неизвестно, насколько часто будет производиться добавление. А хотелось бы сделать наиболее оптимальную структуру индекса, чтобы на любом запросе использовалось как можно большее количество индексов. Хотя, что-то мне подсказывает, что не получиться так сделать.. Как вариант вести статистику запросов (какие чаще выполняются, а какие реже) и на основе этой статистики строить наиболее подходящий индекс. Но статистика вещь изменяемая к сожалению..
2) есть такое очень маленькое неприятное "но": дело в том, что есть пользователи (и я в их числе) которые указывают номер своего ICQ с дефисами и хотят потом увидеть тоже самое с дефисами. Именно по этой причине и приходиться идти на уступки. Хотя возможно пересмотрю своё решение.

Автор: skyboy 8.3.2008, 01:13
Цитата(Fally @  7.3.2008,  23:35 Найти цитируемый пост)
есть такое очень маленькое неприятное "но": дело в том, что есть пользователи (и я в их числе) которые указывают номер своего ICQ с дефисами и хотят потом увидеть тоже самое с дефисами. Именно по этой причине и приходиться идти на уступки. Хотя возможно пересмотрю своё решение.

но что тебе мешает удалять дефисы при вводе(чтоб получить целое беззнаковое), а при выводе - разбивать на триплеты, как это делается с телефонным номером? и будет людям "с дефисами"...
Цитата(Fally @  7.3.2008,  23:35 Найти цитируемый пост)
А хотелось бы сделать наиболее оптимальную структуру индекса, чтобы на любом запросе использовалось как можно большее количество индексов.
ты хочешь - не "оптимальную", а "универсальную" структуру. такого не бывает. панацеи не существует. если посоздаешь по индексу на каждое поле - при любом запросе должны будут эти индексы использоваться, но при вставке/удалении будут шерстриться индексы(соотвественно, падает скорость вставки/удаления/изменения). если это понимаешь, то тогда вопрос стоит следующим образом: что происходит чаше - вставки/удаления или выборка?

Автор: Fally 10.3.2008, 19:28
Выборка чаще и следовательно я не должен тыкать всякое поле в индекс... всё-таки придётся сначала ставить индекс на user_info_rname и user_info_rsurname и вести статистику запросов, и следуя статистике уже расставлять индекс.. (но не нравиться мне это решение)...

Автор: SelenIT 10.3.2008, 21:28
Цитата(Fally @  10.3.2008,  19:28 Найти цитируемый пост)
Выборка чаще и следовательно я не должен тыкать всякое поле в индекс...

Почему? Если выборки по всем полям в первом приближении равновероятны? 

Автор: Fally 13.3.2008, 17:47
не так высказался (:
Дело в том, что если взять каждое поле в отдельный индекс, то при любом запросе будет использоваться только один индекс, даже если в запросе будут участвовать два или более полей. + сейчас известно что выборки будут часты, но пока ничего не известно о том, как часто будет производиться изменение данных в таблице, и если окажется что и изменение будет проводиться тоже часто, то на модификацию индексов будет уходить много времени.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)