Форум пользователей MySQL

lpa · 15.08.2009 14:02:53

Спрашивал об этом на многих англоязычных форумах, но никто не может ответить, как решить эту проблему. Может здесь есть умные люди...

Мне нужен следующий результат:

$result = mysql_query("SELECT s.viewer_id, s.vtime, u.nick 

FROM stats_profileviewers s, usertable u 

WHERE s.viewer_id = u.id and s.profile_id='$id' 

order by s.vtime DESC limit $start, $step") or die();

while(list($tid, $vtime, $nick) = mysql_fetch_row($result)) {

  $result1 = mysql_query("SELECT user FROM portalsession where user='$tid'") or die();

  $count=mysql_num_rows($result1);

    if($count == 1) {

    $online = 'online.gif';

    } else {

    $online = 'transparent.gif';

    }

  echo "$nick - $vtime - <img src=$online>";  

}

Этого я пытаюсь достичь следующим спрсобом:

explain SELECT s.viewer_id, s.vtime, u.nick

    FROM  stats_profileviewers s

    JOIN  usertable u ON s.viewer_id = u.id

    LEFT JOIN  portalsession ps ON s.viever_id = ps.user

    WHERE  s.profile_id='$id'

    order by  s.vtime DESC

    limit  $start, $step;

Индексы есть на:
stats_profileviewers.viewer_id,
stats_profileviewers.profile_id,
stats_profileviewers.vtime,
usertable.id
portalsession.user

Пробовал поставить индекс на:
(profile_id, vtime, viewer_id)

Ничего не помогает, результат всегда почти одинаковый:

+----+-------------+-------+--------+--------------------------------------+------------+---------+-------------------------+------+---------------------------------+

| id | select_type | table | type   | possible_keys                        | key        | key_len | ref                     | rows | Extra                           |

+----+-------------+-------+--------+--------------------------------------+------------+---------+-------------------------+------+---------------------------------+

|  1 | SIMPLE      | s     | ref    | profile_id,viewer_id,viewer_id_2     | profile_id | 4       | const                   |   11 | Using temporary; Using filesort |

|  1 | SIMPLE      | ps    | index  | PRIMARY                              | PRIMARY    | 17      | NULL                    |  294 | Using index                     |

|  1 | SIMPLE      | u     | eq_ref | PRIMARY                              | PRIMARY    | 4       | databas5.s.viewer_id    |    1 |                                 |

+----+-------------+-------+--------+--------------------------------------+------------+---------+-------------------------+------+---------------------------------+

Существует ли какой-то способ, как получить этот результат без FILESORT?

Спасибо!

paulus · 17.08.2009 01:16:10

Теоретически должно хватать индекса на (profile_id, vtime), с ним точно
не работает? Можете показать полные структуры табличек, чтобы можно
было поиграть с ними?

Кстати, в Вашем запросе табличка с сессиями совсем лишняя — объединение
с ней левое (а значит количество строк не ограничивается), в списке
полей и в WHERE она не участвует. Поэтому ее можно смело выкинуть в
тартарары

SELECT s.viewer_id, s.vtime, u.nick

FROM  stats_profileviewers s

JOIN  usertable u ON s.viewer_id = u.id

WHERE  s.profile_id='$id'

ORDER BY  s.vtime DESC

LIMIT  $start, $step;

lpa · 19.08.2009 13:11:01

Спасибо за Ваш ответ.

Таблица с сессиями не лишняя - в ней проверяется он-лайн ли пользователи на данный момент, которые находятся в stats_profileviewers.
У меня похожих ситуаций, где вовлечены 3 таблицы (usertable+portalsession+еще 1 таблица), много, поэтому очень важно найти оптимальное решение.

Таблицы:

CREATE TABLE `portalsession` (

  `user` varchar(15) NOT NULL,

  `time` int(11) NOT NULL,

  `host_addr` varchar(15) NOT NULL,

  `guest` int(1) NOT NULL default '0',

  PRIMARY KEY  (`user`),

  KEY `time` (`time`),

  KEY `guest` (`guest`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `stats_profileviewers` (

  `profile_id` int(11) NOT NULL default '0',

  `profile_nick` varchar(16) NOT NULL,

  `viewer_id` int(11) NOT NULL default '0',

  `viewer_nick` varchar(16) NOT NULL,

  `vtime` datetime NOT NULL default '0000-00-00 00:00:00',

  `expiration` int(11) NOT NULL default '0',

  KEY `profile_id` (`profile_id`),

  KEY `viewer_id` (`viewer_id`),

  KEY `expiration` (`expiration`),

  KEY `vtime` (`vtime`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `usertable` (

  `id` int(11) NOT NULL auto_increment,

  `nick` varchar(16) NOT NULL default '',

  `email` varchar(255) NOT NULL default '',

  `ip` varchar(255) NOT NULL default '',

  `last_login` datetime NOT NULL default '0000-00-00 00:00:00',

  `activated` tinyint(1) NOT NULL default '0',

  `show_mail` tinyint(1) NOT NULL default '0',

  `counter` int(11) NOT NULL default '0',

  `avatar` int(11) NOT NULL default '0',

  `sex` tinyint(1) NOT NULL default '0',

  `main_image` tinyint(1) NOT NULL default '0',

  `main_image_name` varchar(255) NOT NULL default '',

  `password` varchar(255) NOT NULL default '',

  `birthdate` date NOT NULL default '0000-00-00',

  `profile` text NOT NULL,

  `rating` int(11) NOT NULL default '0',

  `rating_total` int(11) NOT NULL default '0',

  `messages_number` int(11) NOT NULL default '0',

  `skype` varchar(90) NOT NULL default '',

  PRIMARY KEY  (`id`),

  KEY `activated` (`activated`),

  KEY `nick` (`nick`),

  KEY `sex` (`sex`),

  KEY `birthdate` (`birthdate`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=10656

Еще ситуация с 2 из этих таблиц:

SELECT u.nick, b.user

FROM usertable u LEFT JOIN portalsession b on u.id = b.user WHERE u.id = '60' LIMIT 1;

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra       |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

|  1 | SIMPLE      | u     | const | PRIMARY       | PRIMARY | 4       | const |    1 |             |

|  1 | SIMPLE      | b     | index | PRIMARY       | PRIMARY | 17      | NULL  |  261 | Using index |

+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+

Если используется индекс, то почему просматривается вся таблица (261 rows), чтобы найти 1 строчку?

Спасибо!

Отредактированно lpa (19.08.2009 13:12:08)

paulus · 20.08.2009 13:42:39

Это все LEFT JOIN виноват Отношение «может быть, а может не быть» не позволяет
так легко бегать по ключу, как это происходит с обычным JOIN. Впрочем, 200 строк
особой роли не играют.

lpa · 20.08.2009 16:22:36

В этом примере 200 строк, но есть и тысячи, при том их количество увеличивается.
Похоже, что никто не знает способа, как решить проблему в моих примерах. Даже не знаю, что лучше - оставить все, как есть - с FILESORT или делать, как в моем первом сообщении:

while(list($tid, $vtime, $nick) = mysql_fetch_row($result)) {

  $result1 = mysql_query("SELECT user FROM portalsession where user='$tid'") or die();

  $count=mysql_num_rows($result1);

}

Если кто-то знает способ, как получить тотже результат без FILESORT:

SELECT s.viewer_id, s.vtime, u.nick

    FROM  stats_profileviewers s

    JOIN  usertable u ON s.viewer_id = u.id

    LEFT JOIN  portalsession ps ON s.viever_id = ps.user

    WHERE  s.profile_id='$id'

    ORDER BY  s.vtime DESC

    LIMIT  $start, $step;

я готов заплатить.

paulus · 20.08.2009 17:19:35

Ну, я по прежнему придерживаюсь мнения, что нужно попробовать добавить
ключик на (profile_id, vtime) и выбросить ненужную таблицу с левым объединением

lpa · 20.08.2009 18:43:16

Если я выброшу таблицу portalsession, то в результатах не смогу показать, которые пользователи он-лайн, а это важно.

paulus · 20.08.2009 19:05:49

Ну, тем не менее, в текущем запросе она не участвует даже если присутствует.
Но все равно индекс стоит создать, даже если Вы мне не верите по поводу таблицы

lpa · 21.08.2009 18:31:21

Создал индекс на (profile_id, vtime). Убрал 3 таблицу. Вот, что получилось:

SELECT

sp.viewer_id, sp.vtime, u.nick

FROM stats_profileviewers sp

join usertable u on sp.viewer_id = u.id 

WHERE sp.profile_id='60'

order by sp.vtime DESC

limit 0, 20;

+----+-------------+-------+--------+---------------------------+--------------+---------+------------------+------+-------------+

| id | select_type | table | type   | possible_keys             | key          | key_len | ref              | rows | Extra       |

+----+-------------+-------+--------+---------------------------+--------------+---------+------------------+------+-------------+

|  1 | SIMPLE      | sp    | ref    | viewer_id,profile_id_2    | profile_id_2 | 4       | const            |    2 | Using where |

|  1 | SIMPLE      | u     | eq_ref | PRIMARY                   | PRIMARY      | 4       | db5.sp.viewer_id |    1 |             |

+----+-------------+-------+--------+---------------------------+--------------+---------+------------------+------+-------------+

Проверить он-лайн ли пользователь, решил следующим способом:

SELECT  

sp.viewer_id, sp.vtime, u.user

FROM stats_profileviewers sp

join portalsession u on sp.viewer_id = u.user

WHERE sp.profile_id='60'

order by sp.vtime DESC

limit 0,20;

$resultarray = array();

while ($row = mysql_fetch_assoc($result1))

{

    $resultarray[] = $row['viewer_id'];

} 

if (in_array($tid,$resultarray))

  {

  $online = '1';

  }

но увы:

+----+-------------+-------+-------+---------------------------+--------------+---------+-------+------+---------------------------------+

| id | select_type | table | type  | possible_keys             | key          | key_len | ref   | rows | Extra                           |

+----+-------------+-------+-------+---------------------------+--------------+---------+-------+------+---------------------------------+

|  1 | SIMPLE      | sp    | ref   | viewer_id,profile_id_2    | profile_id_2 | 4       | const |    2 | Using temporary; Using filesort |

|  1 | SIMPLE      | u     | index | PRIMARY                   | PRIMARY      | 17      | NULL  |    4 | Using where; Using index        |

+----+-------------+-------+-------+---------------------------+--------------+---------+-------+------+---------------------------------+

Ничего не понимаю, почему здесь опять появляется Using temporary; Using filesort, хотя запрос идентичен первому - меняется только JOIN таблица, но user в таблице portalsession - primary key, также, как id в таблице usertable.

paulus · 21.08.2009 19:45:55

Ну, крышу ему во втором случае сносит, потому что в одной таблице у Вас INT,
а во второй — VARCHAR. Из-за этого он их пытется сравнить «в смысле строк» —
вытащить оба значения и попытаться понять — однаковые они или нет, пытаясь
преобразовать одно к другому. Что приводит к полному сканированию таблички
и всем остальным неприятным последствиям.

К слову сказать, если я правильно Вас понял, то Вы хотите, чтобы в этом же
запросе появлялся флажок «есть ли строчки, соответствующие этому пользователю,
в таблице portalsession». После того, как поменяете тип данных, его стоит добавить
третьей табличкой (так, как Вы делали, но выводя нужную информацию):

SELECT

sp.viewer_id, sp.vtime, u.nick, ps.user as is_online

FROM stats_profileviewers sp

join usertable u on sp.viewer_id = u.id

left join portalsession ps ON ps.user = sp.videwer_id

WHERE sp.profile_id='60'

order by sp.vtime DESC

limit 0, 20;

lpa · 21.08.2009 19:59:07

Ой, точно - во второй таблице VARCHAR. Я совсем упустил этот факт. Большое спасибо Вам - попробую поменять.

Форум пользователей MySQL

#1 15.08.2009 14:02:53

Помогите избавиться от FILESORT

#2 17.08.2009 01:16:10

Re: Помогите избавиться от FILESORT

#3 19.08.2009 13:11:01

Re: Помогите избавиться от FILESORT

#4 20.08.2009 13:42:39

Re: Помогите избавиться от FILESORT

#5 20.08.2009 16:22:36

Re: Помогите избавиться от FILESORT

#6 20.08.2009 17:19:35

Re: Помогите избавиться от FILESORT

#7 20.08.2009 18:43:16

Re: Помогите избавиться от FILESORT

#8 20.08.2009 19:05:49

Re: Помогите избавиться от FILESORT

#9 21.08.2009 18:31:21

Re: Помогите избавиться от FILESORT

#10 21.08.2009 19:45:55

Re: Помогите избавиться от FILESORT

#11 21.08.2009 19:59:07

Re: Помогите избавиться от FILESORT

Board footer