|
MySQL.RU - Webboard
 Вернуться
 До-о-олгая выборка (Георгий) 18/08/2004 - 13:19:38
 Re: До-о-олгая выборка (простохуй) 18/08/2004 - 14:20:23
Re: До-о-олгая выборка (Георгий) 18/08/2004 - 15:56:24
Re: До-о-олгая выборка (простохуй) 18/08/2004 - 16:17:45
From: Георгий - 18/08/2004 - 13:19:38
Subject:До-о-олгая выборка
-----------------
Столкнулся я с проблемой, решить которую мне пока, увы, не удалось. Был бы чрезвычайно благодарен за подсказки в каких направлениях копать...
В тексте сообщения приводятся куски кода, позволяющие сгенерировать тестовые записи. Если вдруг кому не лень будет...
Ситуация:
Имеются некие записи (например, статьи). Каждая запись принадлежит одной или более рубрик. Рубрик мало (несколько десятков), записей много (сотня-другая тысяч). Запись имеет поле "дата создания". Некоторые рубрики имеют очень мало записей, другие, наоборот, много.
Задача:
Запихать записи и рубрики в базу и написать запросы, которые бы возвращали несколько десятков последних по дате создания записей для указанной рубрики. То бишь - получить для каждой рубрики список последних статей.
Единственный пришедший мне в голову вариант, как это организовать в базе:
Таблица записей:
CREATE TABLE a_item (
id int(10) NOT NULL default '0',
date datetime NOT NULL default '0000-00-00 00:00:00',
title char(255) NOT NULL default '',
UNIQUE KEY id (id),
KEY main (id,date)
) TYPE=InnoDB;
Таблица рубрик:
CREATE TABLE a_rubric (
id int(10) NOT NULL auto_increment,
title char(255) NOT NULL default '',
PRIMARY KEY (id)
) TYPE=InnoDB;
Связывающая их таблица:
CREATE TABLE a_itemrubric (
idItem int(10) NOT NULL default '0',
idRubric int(10) NOT NULL default '0',
KEY idItem (idItem),
KEY idRubric (idRubric)
) TYPE=InnoDB;
Если есть мысли как сформировать таблицы по-другому - буду рад их выслушать.
Заполняем таблицу рубрик:
#!/usr/bin/perl
use strict;
use DBI;
my $pdb = DBI->connect ( "DBI:mysql:db_name", 'login', 'password' );
my $i; for ( $i = 1; $i <= 20; $i++ ) {
$pdb->do( "INSERT INTO a_rubric VALUES($i,'Заголовок номер $i')" );
}
$pdb->disconnect;
Заполняем таблицу записей и связывающую таблицу. Не забываем о том, что для одних рубрик записей мало, а для других - много.
------------
#!/usr/bin/perl
use strict;
use DBI;
my $pdb = DBI->connect ( "DBI:mysql:db_name", 'login', 'password' );
my $idItem; for ( $idItem = 1; $idItem <= 100000; $idItem++ ) {
print "$idItem\n" if ( ( $idItem % 500 ) == 0 );
# Случайная дата
my $day = sprintf "%02d", int (rand(27.1) + 1);
my $mon = sprintf "%02d", int (rand(11.1) + 1);
my $yea = sprintf "%04d", int (rand(2.1) + 2002);
my $hou = sprintf "%02d", int (rand(23.1));
my $min = sprintf "%02d", int (rand(59.1));
my $sec = sprintf "%02d", int (rand(59.1));
my $date = "${yea}-${mon}-${day} ${hou}:${min}:${sec}";
$pdb->do( "INSERT INTO a_item VALUES('$idItem', '$date','Заголовок номер $idItem')" );
# Для каждой записи - от одной до 4х рубрик
# Если разобраться - количество записей для рубрик получается действительно
# неравномерным. Разбираться необязательно :)
my $nRubric = int ( rand(3.1) + 1); # Количество рубрик (от 1 до 4х)
my $j; for ( $j = 0; $j < $nRubric; $j++ ) {
my $rnd = int rand ( 4.1 ); # Случайное число от 1 до 4х
my $idRubric = $j*5 + $rnd + 1; # Число от 1 до 20
$pdb->do( "INSERT INTO a_itemrubric VALUES('$idItem', '$idRubric')" );
}
}
$pdb->disconnect;
------------
Замечательно. Для рубрики номер 20 - 60 записей, для рубрики номер 1 - без малого 25000.
Первый запрос для выборки, пришедший мне в голову ('1' - тут задаем идентификатор рубрики):
SELECT i.id,i.date,i.title
FROM a_item i, a_rubric r, a_itemrubric ir
WHERE
r.id=1
AND ir.idRubric=r.id
AND ir.idItem=i.id
ORDER BY i.date DESC
LIMIT 20
Для рубрики 20 время выполнения запроса - 0.02 секунды. Для рубрики 1 - около 2х секунд. Т.е. при большом количестве записей для данной рубрики время выборки недопустимо велико.
EXPLAIN показывает необходимость filesort, который, похоже, и замедляет процесс. Если убрать ORDER BY - filesort исчезает. Т.е., если я не ошибаюсь, налицо описанная в документации особенность - неспособность MySQL обрабатывать ORDER BY по индексу, если индекс - не по полям первой связываемой таблицы.
table type possible_keys key key_len ref rows Extra
r const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
ir ref idItem,idRubric idRubric 4 const 46278
i eq_ref id,main id 4 ir.idItem 1
Второй запрос. На первое место выводим таблицу a_item, чтоб ORDER BY мог обработаться по индексу:
SELECT i.id,i.date,i.title
FROM a_item i
LEFT JOIN a_itemrubric ir ON i.id=ir.idItem
LEFT JOIN a_rubric r ON ir.idRubric=r.id
WHERE
r.id=20
ORDER BY i.date DESC
LIMIT 20
Получаем время запроса порядка 0.8 секунды для первой рубрики (т.е. примерно в два раза быстрее). И... 15 секунд для рубрики 20 (с малым числом записей).
Как видим, несмотря ни на что, filesort сохранился.
table type possible_keys key key_len ref rows Extra
i ALL NULL NULL NULL NULL 98109 Using filesort
ir ref idItem idItem 4 i.id 1
r eq_ref PRIMARY PRIMARY 4 ir.idRubric 1 where used; Using index
Если для a_item добавить ключ на поля date,id время запроса сокращается до 6 секунд.
SELECT i.id, i.date, i.title
FROM a_item i
USE INDEX ( main )
LEFT JOIN a_itemrubric ir ON i.id = ir.idItem
LEFT JOIN a_rubric r ON ir.idRubric = r.id
WHERE r.id = 20
ORDER BY i.date DESC
LIMIT 20
При этом от filesort мы, правда, избавились, но сильно легче, увы, не стало.
EXPLAIN говорит следующее (ключ был создан как PRIMARY):
table type possible_keys key key_len ref rows Extra
i index NULL PRIMARY 12 NULL 93315
ir ref idItem idItem 4 i.id 1
r eq_ref PRIMARY PRIMARY 4 ir.idRubric 1 where used; Using index
Попытка поменять таблицы местами и начинать присоединение с rubric ни к чему хорошему не приводит - упираемся в тот же filesort - см. первый запрос, который без joinов. Время запроса для рубрик с большим количеством записей - от 4х секунд.
SELECT i.id, i.title, r.id, r.title
from a_rubric r
left join a_itemrubric ir ON r.id=ir.idRubric
LEFT JOIN a_item i ON ir.idItem=i.id
WHERE
r.id=1
ORDER BY i.date DESC;
table type possible_keys key key_len ref rows Extra
r const PRIMARY PRIMARY 4 const 1 Using temporary; Using filesort
ir ref idRubric idRubric 4 const 372
i eq_ref id,main id 4 ir.idItem 1
На этом фантазия пока иссякла.
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
16701
|
|
