Оптимизация запроса

Coagulant · 3 Мар 2005

Оптимизация запроса

Имеется запрос (слегка переделанный из форума phpbb), который выполняется достаточно медленно и хотелось бы его ускорить:

PHP:

SELECT u.user_id, u.username, u.user_allow_viewonline, u.user_level, s.session_logged_in, s.session_time, s.session_page, s.session_ip, i.country
FROM phpbb_users u, phpbb_sessions s, phpbb_ip2country i
WHERE u.user_id = s.session_user_id
AND s.session_time >=1109870034
AND s.session_ip >= i.ip_from
AND s.session_ip <= i.ip_to
ORDER  BY u.username ASC , s.session_ip ASC

Вот структура 3-х таблиц, из которых делается выборка:

PHP:

CREATE TABLE `phpbb_users` (
  `user_id` mediumint(8) NOT NULL default '0',
  `username` varchar(25) NOT NULL default '',
  `user_session_time` int(11) NOT NULL default '0',
  `user_level` tinyint(4) default '0',
  `user_allow_viewonline` tinyint(1) NOT NULL default '1',
  PRIMARY KEY  (`user_id`),
  KEY `user_session_time` (`user_session_time`)
) TYPE=MyISAM;

CREATE TABLE `phpbb_sessions` (
  `session_id` char(32) NOT NULL default '',
  `session_user_id` mediumint(8) NOT NULL default '0',
  `session_start` int(11) NOT NULL default '0',
  `session_time` int(11) NOT NULL default '0',
  `session_ip` char(8) NOT NULL default '0',
  `session_page` int(11) NOT NULL default '0',
  `session_logged_in` tinyint(1) NOT NULL default '0',
  PRIMARY KEY  (`session_id`),
  KEY `session_user_id` (`session_user_id`),
  KEY `session_id_ip_user_id` (`session_id`,`session_ip`,`session_user_id`)
) TYPE=MyISAM;

CREATE TABLE `phpbb_ip2country` (
  `ip_from` varchar(8) NOT NULL default '',
  `ip_to` varchar(8) NOT NULL default '',
  `country_short` char(2) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`ip_from`,`ip_to`)
) TYPE=MyISAM;

Фанат · 3 Мар 2005

для ускорения запросов служит команда EXPLAIN после которой пишется текст запроса.

Coagulant · 3 Мар 2005

Explain выдает следующее:

PHP:

table  type    possible_keys    key      key_len  ref                rows  Extra  
i      ALL     PRIMARY          NULL     NULL     NULL               54502 Using temporary; Using filesort 
s      ALL     session_user_id  NULL     NULL     NULL               66    Using where 
u      eq_ref  PRIMARY          PRIMARY  3        s.session_user_id  1

chira · 4 Мар 2005

сколько всего строк в каждой из таблиц?

Coagulant · 4 Мар 2005

phpbb_users - 4
phpbb_sessions - 66
phpbb_ip2country - 54502

chira · 4 Мар 2005

а планируется?
для существующих данных, попробуй добиться порядка соединения таблиц u,s,i и сообщи результат ...

Coagulant · 4 Мар 2005

В принципе в phpbb_users планируется несколько тысяч записей максимум, остальные таблицы приблизительно такие, как есть сейчас.

для существующих данных, попробуй добиться порядка соединения таблиц u,s,i и сообщи результат ...

Прошу прощения, что имеется в виду? Можно по-подробнее.

chira · 4 Мар 2005

как будет работать с таким фромом:
1.
...
FROM phpbb_users u STRAIGHT_JOIN phpbb_sessions s STRAIGHT_JOIN phpbb_ip2country i
...

2.
...
FROM phpbb_sessions s STRAIGHT_JOIN phpbb_users u STRAIGHT_JOIN phpbb_ip2country i
...

Coagulant · 4 Мар 2005

1) 17 секунд

PHP:

table  type  possible_keys   key  key_len  ref  rows  Extra  
u      ALL   PRIMARY         NULL NULL     NULL 4     Using temporary; Using filesort 
s      ALL   session_user_id NULL NULL     NULL 66    Using where 
i      ALL   PRIMARY         NULL NULL     NULL 54502 Range checked for each record (index map: 1)

2) 19 секунд

PHP:

table  type  possible_keys   key  key_len  ref  rows  Extra  
s      ALL   session_user_id NULL NULL     NULL 66    Using where; Using temporary; Using filesort 
u      ALL   PRIMARY         NULL NULL     NULL 4     Using where 
i      ALL   PRIMARY         NULL NULL     NULL 54502 Range checked for each record (index map: 1)

Предложенный мной в первом посте запрос выполняется 1.2 секунды.

chira · 6 Мар 2005

а если привести к одному типу поля:
CREATE TABLE `phpbb_sessions` (
.....
`session_ip` char(8) NOT NULL default '0',
......

CREATE TABLE `phpbb_ip2country` (
`ip_from` varchar(8) NOT NULL default '',
`ip_to` varchar(8) NOT NULL default '',
....
PRIMARY KEY (`ip_from`,`ip_to`)
....

для таблицы i должен использоваться индекс PRIMARY

-~{}~ 06.03.05 18:13:

если можешь скинь dump, тогда можно более конкретнее говорить ...

Coagulant · 7 Мар 2005

В таблице phpbb_sessions поменял `session_ip`на varchar(8), но эффекта не достиг. А вот в phpbb_ip2country
не получилось привести ip_from и ip_to к char(8), мускул по-прежнему считает, что эти поля - varchar(8) :confused:

Вот дампы 3-х таблиц.

chira · 7 Мар 2005

есть ли возможность добавить поле country в таблицу phpbb_sessions и заполнять его согласно IP?
тогда отпадёт необходимость связывать таблицу phpbb_ip2country ...

Coagulant · 7 Мар 2005

Да, это вариант. Но непонятно, почему не используется составной индекс PRIMARY KEY (`ip_from`,`ip_to`)

chira · 7 Мар 2005

убери из селект i.country, будет использовать ...
ещё вместо
AND s.session_ip >= i.ip_from
AND s.session_ip <= i.ip_to
прставь BETWEEN
AND s.session_ip BETWEEN i.ip_from AND i.ip_to

-~{}~ 07.03.05 18:40:

у меня лучший результат получился для такого селекта:

Код:

SELECT u.user_id, u.username, u.user_level, s.session_logged_in, s.session_time, s.session_page, s.session_ip, i.country
FROM phpbb_sessions s STRAIGHT_JOIN phpbb_ip2country i  STRAIGHT_JOIN  phpbb_users u 
WHERE u.user_id = s.session_user_id 
AND s.session_time >=1109870034 
AND s.session_ip BETWEEN i.ip_from AND i.ip_to 
ORDER  BY u.username ASC , s.session_ip ASC
;

ONK · 7 Мар 2005

Coagulant, добавь простой индекс на ip_to и приведи EXPLAIN

Coagulant · 7 Мар 2005

chira
Спасибо, время сократилось до 0.9 секунд при вашем запросе.

Код:

table  type   possible_keys   key     key_len  ref               rows  Extra  
s      ALL    session_user_id NULL    NULL     NULL              65    Using where; Using temporary; Using filesort 
i      ALL    NULL            NULL    NULL     NULL              54502 Using where 
u      eq_ref PRIMARY         PRIMARY 3        s.session_user_id 1

ONK
Добавил индекс

Код:

ALTER TABLE `phpbb_ip2country` ADD INDEX ( `ip_to` )

. Получается:

Код:

table  type   possible_keys   key     key_len  ref               rows  Extra  
i      ALL    PRIMARY,ip_to   NULL    NULL     NULL              54502 Using temporary; Using filesort 
s      ALL    session_user_id NULL    NULL     NULL              65    Using where 
u      eq_ref PRIMARY         PRIMARY 3        s.session_user_id 1

ONK · 7 Мар 2005

Похоже что избавиться от полного сканирования таблицы phpbb_ip2country при такой стуктуре не получится, нужно изобретать другой способ хранения диапазонов ip.

Coagulant · 7 Мар 2005

Автор оригинала: ONK
Похоже что избавиться от полного сканирования таблицы phpbb_ip2country при такой стуктуре не получится, нужно изобретать другой способ хранения диапазонов ip.

Есть какие-нибудь предложения? Добавлять поле country в таблицу phpbb_sessions не подходит, т.к. придется добавлять для каждой записи еще один запрос.

ONK · 7 Мар 2005

На сколько я помню, близкую проблему решал nagash, но его что-то давно не видно.

chira · 8 Мар 2005

Добавлять поле country в таблицу phpbb_sessions не подходит, т.к. придется добавлять для каждой записи еще один запрос.

и что в этом плохого?

Оптимизация запроса

Новичок

oncle terrible

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Новичок

Пассивист PHPСluba

Новичок

Пассивист PHPСluba

Новичок

Пассивист PHPСluba

Новичок