|
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
> Original message text:
> 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
>
> На этом фантазия пока иссякла.
>
From: простохуй - 18/08/2004 - 16:17:45
Subject:До-о-олгая выборка
-----------------
ну а чтобы не нарушать логику объектов надо ввести две даты :) -- дату написания статьи и пусть она будет там где и была и дату размещения статьи в рубрике, тогда дата в таблице отношений выглядит вполне уместно, а при поиске или где там используются данные запросы по умолчанию предлагать 20 последних статей опубликованных в данной рубрике. а уж если кто то хочет получить 20 последних написанных статей, то по той же самой логике он их получит но уже без использования таблицы отношений, потому как сама по себе статья как объект может существовать совершенно безо всякой рубрики.
[Это сообщение - спам!]
Последние сообщения из форума
Уважаемые посетители форума MySQL.RU!
Убедительная просьба, прежде чем задавать свой вопрос в этом форуме, обратите внимание на разделы:
- ответы на наиболее часто задаваемые вопросы - FAQ
- раздел документация
- раздел поиск по сообщениям форума и документации
Также, старайтесь наиболее подробно указывать свою ситуацию (версию операционной системы, версию MySQL,
версию программного обеспечения, по которому возникает вопрос, текст возникающих ошибок, и др.)
Помните, чем конкретнее Вы опишете ситуацию, тем больше шансов получить реальную помощь.
16701
|
|
