VPF::Оптимизация таблиц и запроса - Форум программистов

seeruyy

Дата 14.12.2011, 00:32 (ссылка)

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

Загрузка ...

Новичок

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

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

Всем доброго времени суток. Нужна помощь по оптимизации sql запроса и таблиц. Имеются вот такие таблицы:

Код


CREATE TABLE IF NOT EXISTS `qpv_city` (
  `city_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `country_id` int(11) unsigned NOT NULL DEFAULT '0',
  `region_id` int(10) unsigned NOT NULL DEFAULT '0',
  `name_ru` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`city_id`),
  KEY `country_id` (`country_id`),
  KEY `region_id` (`region_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15789521;

CREATE TABLE IF NOT EXISTS `qpv_country` (
  `country_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name_ru` varchar(128) NOT NULL DEFAULT '',
  PRIMARY KEY (`country_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7716094 ;

CREATE TABLE IF NOT EXISTS `qpv_profile_info` (
  `profile_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `user_name` varchar(64) NOT NULL,
  `avatar` varchar(64) NOT NULL,
  `about_me` text NOT NULL,
  `want_find` text NOT NULL,
  KEY `profile_id` (`profile_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `qpv_profile_search` (
  `profile_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `community_id` int(11) NOT NULL,
  `language_id` int(11) NOT NULL,
  `status` tinyint(1) NOT NULL,
  `status_visibility` tinyint(1) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `looking_for` varchar(96) NOT NULL,
  `im_seeking` int(5) NOT NULL,
  `birthday` date NOT NULL
  KEY `profile_id` (`profile_id`), 
  KEY `user_id` (`user_id`), 
  KEY `ind1` (`im_seeking`, `gender`, `looking_for`, `community_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `qpv_user` (
  `user_id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(96) NOT NULL,
  `password` varchar(60) NOT NULL,
  `old_password` varchar(60) NOT NULL,
  `login` varchar(255) NOT NULL,
  `newslatter` enum('0','1') NOT NULL,
  `status` enum('0','1') NOT NULL,
  `ip` varchar(20) NOT NULL,
  `date_create` int(11) NOT NULL,
  `approve` enum('0','1') NOT NULL,
  `token` varchar(50) NOT NULL,
  `vip` enum('0','1') NOT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=100000;

CREATE TABLE IF NOT EXISTS `qpv_user_info` (
  `user_id` int(11) NOT NULL,
  `language_id` smallint(2) NOT NULL,
  `time_zone_id` smallint(5) NOT NULL,
  `gender` tinyint(1) NOT NULL,
  `birthday` date NOT NULL,
  `city_id` int(11) NOT NULL,
  `user_name` varchar(32) NOT NULL
  KEY `user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

По этим таблицам ведется поиск пользователей на сайте, сейчас в таблицах profile_..., user_... по 100000 записей

Вот один из возможных запросов поиска, время выполнения его от 1.1774, и это на локалке с одним подключением к mysql, если лимит установлен с нуля, то все хорошо, 0.0012, а чем дальше в лес, тем больше дров (. Даже не знаю что с этим всем делать,

Код


SELECT u.user_id, u.login, pi.user_name, pi.avatar, ps.birthday, ps.gender, c.name_ru AS city, co.name_ru AS country, ps.looking_for
FROM qpv_profile_search ps
INNER JOIN qpv_user_info ui ON ps.user_id = ui.user_id
INNER JOIN qpv_profile p ON ps.profile_id = p.profile_id
INNER JOIN qpv_profile_info pi ON p.profile_id = pi.profile_id
INNER JOIN qpv_user u ON ui.user_id = u.user_id
INNER JOIN qpv_city c ON ui.city_id = c.city_id
INNER JOIN qpv_country co ON c.country_id = co.country_id
WHERE ps.im_seeking =74
AND ps.gender = '1'
AND ps.looking_for LIKE "%-49-%"
AND ps.community_id =1
AND ps.language_id =1
AND ps.status = '1'
AND ps.status_visibility = '1'
ORDER BY ps.profile_id
LIMIT 5700 , 25

Если кто может чем-то помочь, буду благодарен.

Akina

Дата 14.12.2011, 07:54 (ссылка)

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

Загрузка ...

Советчик

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

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

1) Explain в студию.
2) Какова суммарная селективность отборов условий во WHERE по таблице qpv_profile_search ?

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

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

seeruyy

Дата 14.12.2011, 23:16 (ссылка)

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

Загрузка ...

Новичок

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

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

id     select_type      table     type       possible_keys                     key               key_len    ref                                  rows     Extra

1      SIMPLE            ps         ref          profile_id,user_id,ind1          ind1              5              const,const                    13398   Using where; Using filesort
1      SIMPLE            ui          ref          city_id,user_id                      user_id         4              smilemur.ps.user_id       1
1      SIMPLE            c           eq_ref    PRIMARY,country_id              PRIMARY       4               smilemur.ui.city_id         1           Using where
1      SIMPLE            u           eq_ref    PRIMARY                               PRIMARY       4               smilemur.ps.user_id      1
1      SIMPLE            co         eq_ref    PRIMARY                               PRIMARY       4               smilemur.c.country_id    1
1      SIMPLE            pi          ref          profile_id                              profile_id      4               smilemur.ps.profile_id    1
1      SIMPLE            p           eq_ref    PRIMARY                               PRIMARY       4               smilemur.ps.profile_id    1           Using index

Akina

Дата 15.12.2011, 07:53 (ссылка)

(голосов:1)

Загрузка ...

Советчик

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

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

А так?

Код


SELECT u.user_id, u.login, pi.user_name, pi.avatar, ps.birthday, ps.gender, c.name_ru AS city, co.name_ru AS country, ps.looking_for
FROM 
(
SELECT pss.birthday, pss.gender, pss.looking_for, pss.user_id, pss.profile_id 
FROM qpv_profile_search  pss
WHERE pss.im_seeking =74
AND pss.gender = '1'
AND pss.looking_for LIKE "%-49-%"
AND pss.community_id =1
AND pss.language_id =1
AND pss.status = '1'
AND pss.status_visibility = '1'
) ps
INNER JOIN qpv_user_info ui ON ps.user_id = ui.user_id
INNER JOIN qpv_profile p ON ps.profile_id = p.profile_id
INNER JOIN qpv_profile_info pi ON p.profile_id = pi.profile_id
INNER JOIN qpv_user u ON ui.user_id = u.user_id
INNER JOIN qpv_city c ON ui.city_id = c.city_id
INNER JOIN qpv_country co ON c.country_id = co.country_id
ORDER BY ps.profile_id
LIMIT 5700 , 25

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

seeruyy

Дата 15.12.2011, 12:13 (ссылка)

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

Загрузка ...

Новичок

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

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

Цитата(Akina @ 15.12.2011, 07:53)

А так?

Код


SELECT u.user_id, u.login, pi.user_name, pi.avatar, ps.birthday, ps.gender, c.name_ru AS city, co.name_ru AS country, ps.looking_for
FROM 
(
SELECT pss.birthday, pss.gender, pss.looking_for, pss.user_id, pss.profile_id 
FROM qpv_profile_search  pss
WHERE pss.im_seeking =74
AND pss.gender = '1'
AND pss.looking_for LIKE "%-49-%"
AND pss.community_id =1
AND pss.language_id =1
AND pss.status = '1'
AND pss.status_visibility = '1'
) ps
INNER JOIN qpv_user_info ui ON ps.user_id = ui.user_id
INNER JOIN qpv_profile p ON ps.profile_id = p.profile_id
INNER JOIN qpv_profile_info pi ON p.profile_id = pi.profile_id
INNER JOIN qpv_user u ON ui.user_id = u.user_id
INNER JOIN qpv_city c ON ui.city_id = c.city_id
INNER JOIN qpv_country co ON c.country_id = co.country_id
ORDER BY ps.profile_id
LIMIT 5700 , 25

Запрос выполняется в среднем за 2,2... секунды, проблема в том, что таблица которая формируется в WHERE ps имеет больше 6000 записей, и mysql перебирает ее всю до 5700 значения и лишь потом берет нужные 25 и дает результат, так же получалось и в предыдущем запросе, судя из данных EXPLAIN я так понял с индексами и их использованием все ок, запрос обрабатывается быстро, но вот как заставить работать так же быстро с большими цифрами в LIMIT ?

Нашел один вариант решения, но он не универсален, суть в том что бы добавлять в WHERE последнее значение поля по которому сортировался предыдущий запрос (WHERE ps.profile_id > 1233). Для классической постраничной навигации это не подходит smile

Только что немного поменял запрос на:

Код


SELECT u.user_id, u.login, pi.user_name, pi.avatar, ps.birthday, ps.gender, c.name_ru AS city, co.name_ru AS country, ps.looking_for
FROM (

SELECT pss.birthday, pss.gender, pss.looking_for, pss.user_id, pss.profile_id
FROM qpv_profile_search pss
WHERE pss.im_seeking =74
AND pss.gender =  '1'
AND pss.looking_for LIKE  "%-49-%"
AND pss.community_id =1
AND pss.language_id =1
AND pss.status =  '1'
AND pss.status_visibility =  '1'
ORDER BY pss.profile_id
LIMIT 5100 , 25
)ps
INNER JOIN qpv_user_info ui ON ps.user_id = ui.user_id
INNER JOIN qpv_profile p ON ps.profile_id = p.profile_id
INNER JOIN qpv_profile_info pi ON p.profile_id = pi.profile_id
INNER JOIN qpv_user u ON ui.user_id = u.user_id
INNER JOIN qpv_city c ON ui.city_id = c.city_id
INNER JOIN qpv_country co ON c.country_id = co.country_id

Время выполнения с 2.2 до 0,1235, вопрос в том можно ли Это считать нормальным результатом?

Это сообщение отредактировал(а) seeruyy - 15.12.2011, 12:43

Akina

Дата 15.12.2011, 12:54 (ссылка)

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

Загрузка ...

Советчик

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

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

Цитата(seeruyy @ 15.12.2011, 13:13

)

Время выполнения с 2.2 до 0,1235, вопрос в том можно ли Это считать нормальным результатом?

Думаю, да... но только хочу сообщить, что этот запрос совершенно неэквивалентен предыдущему.

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

seeruyy

Дата 15.12.2011, 13:15 (ссылка)

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

Загрузка ...

Новичок

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

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

Akina,
Большое Спасибо, Вы очень помогли smile

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