Правильный запрос для сайта знакомств (оптимизация индексов)

Делаю сайт знакомств - соответсвенно одна из основных нагрузок будет использование поиска по людям (это вообще на главной странице будет) - по этому очень важно максимально наладить индексы т.к. пользователи будут добавляться в 1000 раз реже чем будут использоваться возможностью поиска.


Я сделал такую структуру -
site_users - информация логинах пользователей, в основном для авторизации. но тут есть три важных для сортировки поля - Дата регистрации, Последняя активность, и ID

person - анкета о пользователе - вес, рост, ореентация ☻ и т.д.
Привязанна к пользователю по ID

Что нужно реализовать -
1) возможность фильтров по всем полям.
2) сортировка - по последнему посещени, регистрации, возрасту.
3) Пагинация

Site_users

Код (Text):

1. CREATE TABLE `site_users` (
2.     `blocked_by_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
3.     `id` INT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
4.     `password` CHAR(255) NOT NULL COLLATE 'utf8_bin',
5.     `user_name` VARCHAR(100) NOT NULL,
6.     `user_email` VARCHAR(255) NOT NULL,
7.     `isactivated` TINYINT(1) NOT NULL DEFAULT '1',
8.     `ban_reason` VARCHAR(100) NULL DEFAULT NULL,
9.     `ibanned` TINYINT(1) NOT NULL DEFAULT '0',
10.     `user_contacts_skype` VARCHAR(100) NOT NULL,
11.     `user_adress` VARCHAR(255) NOT NULL,
12.     `user_adress_city` VARCHAR(100) NOT NULL,
13.     `user_adress_region` VARCHAR(100) NOT NULL,
14.     `user_adress_country` VARCHAR(100) NOT NULL,
15.     `user_adress_state` VARCHAR(100) NOT NULL,
16.     `user_registration_ip` VARCHAR(50) NOT NULL,
17.     `user_registration_ip_if_proxy` VARCHAR(50) NOT NULL,
18.     `user_carma` INT(11) NOT NULL,
19.     `user_activation_request` VARCHAR(100) NOT NULL,
20.     `user_change_password_request` VARCHAR(100) NOT NULL,
21.     `user_contacts_phone` VARCHAR(100) NOT NULL,
22.     `user_contacts_phone2` VARCHAR(100) NOT NULL,
23.     `user_contacts_phone3` VARCHAR(100) NOT NULL,
24.     `user_contacts_icq` VARCHAR(100) NOT NULL,
25.     `user_contacts_jabber` VARCHAR(100) NOT NULL,
26.     `user_contacts_facebook` VARCHAR(100) NOT NULL,
27.     `user_contacts_instagram` VARCHAR(100) NOT NULL,
28.     `user_contacts_twitter` VARCHAR(100) NOT NULL,
29.     `user_contacts_addit_info` TEXT NOT NULL,
30.     `user_contacts_www` TEXT NOT NULL,
31.     `user_bio` TEXT NOT NULL,
32.     `user_img` VARCHAR(255) NOT NULL,
33.     `user_ip_creation` VARCHAR(255) NOT NULL,
34.     `user_ip_last_active` VARCHAR(50) NOT NULL,
35.     `user_last_active_date` TIMESTAMP NOT NULL,
36.     `user_registration_date` TIMESTAMP NOT NULL,
37.     `group_id` INT(9) UNSIGNED NOT NULL,
38.     `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
39.     `tries_with_wrong_password` TINYINT(3) UNSIGNED NOT NULL DEFAULT '0',
40.     `last_time_wrong_pass_unix_tsmp` INT(10) UNSIGNED NOT NULL DEFAULT '0',
41.     `blocked_up_to_date` INT(10) UNSIGNED NOT NULL DEFAULT '0',
42.     PRIMARY KEY (`id`),
43.     UNIQUE INDEX `user_name` (`user_name`),
44.     UNIQUE INDEX `user_email` (`user_email`),
45.     INDEX `FK_site_users_user_groups` (`group_id`),
46.     CONSTRAINT `FK_site_users_user_groups` FOREIGN KEY (`group_id`) REFERENCES `user_groups` (`id`)
47. )
48. COLLATE='utf8_general_ci'
49. ENGINE=InnoDB
50. AUTO_INCREMENT=9144
51. ;

person Она может потом немного поменяться дальше в процессе разработки - но суть все равно такая же.

Код (Text):

1. CREATE TABLE `person` (
2.     `id` INT(9) UNSIGNED NOT NULL,
3.     `height` TINYINT(3) UNSIGNED NOT NULL COMMENT 'Height of a person',
4.     `weight` TINYINT(3) UNSIGNED NOT NULL COMMENT 'weight of a person',
5.     `children` TINYINT(3) UNSIGNED NOT NULL COMMENT 'amount of children the person currently have',
6.     `sex` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sex of a person',
7.     `sexual_orientation` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
8.     `relationship` TINYINT(3) UNSIGNED NOT NULL COMMENT 'the relationship a person currently have',
9.     `education` TINYINT(3) UNSIGNED NOT NULL COMMENT 'education level of a person',
10.     `employment` TINYINT(3) UNSIGNED NOT NULL COMMENT 'employment status of a person',
11.     `smoke` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
12.     `alcohol` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
13.     `sport` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
14.     `health` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
15.     `virus_hiv` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
16.     `virus_hepatitis_c` TINYINT(3) UNSIGNED NOT NULL COMMENT 'sexual orientation of a person',
17.     PRIMARY KEY (`id`),
18.     CONSTRAINT `FK_person_user_id` FOREIGN KEY (`id`) REFERENCES `site_users` (`id`)
19. )
20. COLLATE='utf8_general_ci'
21. ENGINE=InnoDB
22. ;

На текущий момент использую такой вот запрос для доставания данных

Код (Text):

1.  
2. select * from
3. (SELECT person.id
4. FROM person
5. left join site_users on person.id=site_users.id
6. where
7. sex =1
8. and sexual_orientation =1
9. and relationship =1
10. and employment = 1
11. and smoke = 1
12. and alcohol =1
13. and sport = 1
14. and health = 1
15. and virus_hiv =1
16. and virus_hepatitis_c = 1
17. and (height BETWEEN 110 and 180)
18. and (weight BETWEEN 50 and 250)
19. and education > 1
20. order by site_users.user_registration_date
21. Limit 50 offset 0) as t
22. join person on  t.id=person.id
23. join site_users on t.id = site_users.id;

Вот в чем вопрос - как правильно сделать индексы?
Просто тут особенность в том, что насколько я понимаю MYSQL очень плохо работает с индексами которые в RANGE (как например - between)

Что я понял - что в начале нужно сделать отбор по полям по которым нам нужно только равно.
Особенно важен SEX - так как это сразу половину полей отсекает.

Но как правильно сделать ORDER BY и пагинацию?

Есть ли смысл их вынести вообще в отдельную таблицу
Например отдельная таблица - для user_register_date - user_last_activity?

Как себя ведет индекс после join? (он вообще работает в таких случаях?)
Данные по результатам EXPLAIN скину чуть позже как доберусь на компе на котором это все тестирую, а то у меня там добавляется 300.000 записей достаточно долго.

 

Ну да очепятка☻
В процессе разработке ☻

Ну а как их меньше сделать?) Данных то меньше ведь никак не сделаешь если они нужны)

 

Или в плане что лучше их разбивать на много маленьких?

 

И нужно ли ставить в конце композитного индекса ID?

И как заставить MYSQL использовать индекс выражениях BETWEE или var >1 and var <10 и уж темболее в когда граница не определена - как например в var >10
--- Добавлено ---
И как одним запросом получить число найденных строк (и при этом используя limit)?

 

Спойлер: Картинка - список полей для индекса

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

Код (Text):

1. explain select SQL_NO_CACHE * from
2. (SELECT person.id
3. FROM person
4. where
5. sex =1
6. and sexual_orientation =1
7. and relationship =1
8. and employment =1
9. and smoke =1
10. and alcohol =1
11. and sport =1
12.  
13. order by person.user_registration_date
14. Limit 50 offset 0) as t
15. join person on  t.id=person.id
16. join site_users on t.id = site_users.id;

Const- const - шикарное зрелище☻ радует глаз

Также понял что в 10000 раз быстрее если добавить в таблицу PESON - дату создания пользователя и дату последней активности, а не джоинить другую таблицу

Правда я так и не понял - работает ли индекс для сортировки? Особенно для DESC ???




Однако все ломается если добавить хотябы один знак - < > BETWEEN

Код (Text):

1.  
2. explain select SQL_NO_CACHE * from
3. (SELECT person.id
4. FROM person
5. where
6. sex =1
7. and sexual_orientation =1
8. and relationship =1
9. and employment =1
10. and smoke =1
11. and alcohol =1
12. and sport >1
13. and weight > 150
14. and (height BETWEEN 110 and 180)
15. order by person.user_registration_date
16. Limit 50 offset 0) as t
17. join person on  t.id=person.id
18. join site_users on t.id = site_users.id;

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



Как можно заставить MYSQL использовать индекс для BETWEEN? или только делать поиск по =?

 

А чего это должно изменить?)

 

Сортировка у меня по - user_registration_date
--- Добавлено ---

А само поле по которому идет сортировка должно быть в индексе?
--- Добавлено ---

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