Объединение 2-х запросов выборки в 1

alpine · 3 Ноя 2006

угу, т.е. ваши запросы с временной таблицей медленнее предложенного мной с той же временной таблицей. при том что в случае с одним запросом исключается время на передачу данных. нюню...

its depends of

zerkms · 3 Ноя 2006

it depends of (или on вместо of)

)))

alpine · 3 Ноя 2006

zerkms

(или on вместо of)

Ага, - from

-~{}~ 03.11.06 18:17:

Вот провел тесты на похожей задаче на своих данных.

----------
Задача: Выбрать 3 категории в которых больше всего товаров в порядке убывания.
----------
Цель: Исследовать производительность двух простых запросов и одного аналогичного с JOIN в поставленной задаче.
----------
Решение:
----------

Код:

mysql> SELECT VERSION();
+---------------------+
| VERSION()           |
+---------------------+
| 5.0.21-community-nt |
+---------------------+
1 row in set (0.02 sec)
mysql> SELECT COUNT(*) FROM categories;
+----------+
| COUNT(*) |
+----------+
|     4518 |
+----------+
1 row in set (0.02 sec)

mysql> SELECT COUNT(*) FROM models;
+----------+
| COUNT(*) |
+----------+
|   184810 |
+----------+
1 row in set (0.00 sec)

Проводим анализ запросов с помошью встроенного инструмента EXPLAIN

Код:

mysql> EXPLAIN SELECT categories_id, COUNT(*) as cnt FROM `models`GROUP BY categories_id ORDER BY cnt DESC LIMIT 3;
+----+-------------+--------+-------+---------------+------------+---------+------+--------+----------------------------------------------+
| id | select_type | table  | type  | possible_keys | key        | key_len | ref  | rows   | Extra                                        |
+----+-------------+--------+-------+---------------+------------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | models | index | NULL          | categories | 4       | NULL | 184810 | Using index; Using temporary; Using filesort |
+----+-------------+--------+-------+---------------+------------+---------+------+--------+----------------------------------------------+
1 row in set (0.00 sec)

Код:

mysql> EXPLAIN SELECT * FROM categories WHERE categories_id IN (1849,142,168);
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | categories | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

Код:

mysql> EXPLAIN SELECT c . * , COUNT( * ) AS cnt 
FROM `models` AS m INNER JOIN categories AS c ON ( m.categories_id = c.categories_id ) 
GROUP BY m.categories_id ORDER BY cnt DESC LIMIT 3;
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref                      | rows | Extra                           |
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
|  1 | SIMPLE      | c     | ALL  | PRIMARY       | NULL       | NULL    | NULL                      | 4518 | Using temporary; Using filesort |
|  1 | SIMPLE      | m     | ref  | categories    | categories | 4       | metamarket.c.categories_id |   79 | Using where; Using index        |
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
2 rows in set (0.00 sec)
В наличии есть такие таблицы:

Код:

mysql> SHOW CREATE TABLE categories;

CREATE TABLE `categories` (
  `categories_id` int(10) unsigned NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL default '0',
  `image_size` int(11) NOT NULL default '2',
  `leftcat` int(11) NOT NULL default '0',
  `rightcat` int(11) NOT NULL default '0',
  `rootid` int(11) NOT NULL default '0',
  `level` int(11) NOT NULL default '0',
  `title` varchar(255) NOT NULL default '',
  `syn_id` int(11) NOT NULL default '0',
  `template` varchar(255) NOT NULL default 'type vendor - name',
  `kolitem` int(11) NOT NULL default '0',
  `priority` int(10) unsigned NOT NULL default '0',
  `subnum` int(10) unsigned default '0',
  PRIMARY KEY  (`categories_id`),
  KEY `cat` (`leftcat`,`rightcat`),
  KEY `rightcat` (`rightcat`),
  KEY `parent` (`parent_id`),
  KEY `leftcat` (`leftcat`),
  KEY `item` (`kolitem`),
  KEY `level` (`level`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251

Код:

mysql> SHOW CREATE TABLE models;

CREATE TABLE `models` (
  `model_id` int(10) unsigned NOT NULL auto_increment,
  `products_id` int(11) default NULL,
  `vendor_id` int(11) NOT NULL default '0',
  `categories_id` int(11) NOT NULL default '0',
  `model_name` varchar(255) NOT NULL default '',
  `picture` text NOT NULL,
  PRIMARY KEY  (`model_id`),
  KEY `model_name` (`model_name`),
  KEY `vendor` (`vendor_id`),
  KEY `categories` (`categories_id`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251

Как проводилось тестирование

PHP:

<?php
include './time.inc.php';

mysql_connect('localhost', 'test', 'test');
mysql_select_db('metamarket');

$num_iterations = 10;

for ( $i=0; $i<$num_iterations; $i++ ) {
timestart('Test 2 query');
$sSQL = 'SELECT categories_id, COUNT(*) as cnt
	FROM `models`
	GROUP BY categories_id
	ORDER BY cnt DESC
	LIMIT 3 ';
// echo $sSQL.'<br />';
$rRes = mysql_query($sSQL);

$aCategoriesIds = array();
while ( $aRow = mysql_fetch_row($rRes) ) {
	$aCategoriesIds[] = $aRow[0];
}

$sSQL = 'SELECT *
	FROM categories
	WHERE categories_id
	IN ('.implode(',', $aCategoriesIds).')';
// echo $sSQL.'<br />';
$rRes = mysql_query($sSQL);

$aCategories = array();	
while ( $aRow = mysql_fetch_row($rRes) ) {
	$aCategories[] = $aRow;
}
timestop('Test 2 query');
}

for ( $i=0; $i<$num_iterations; $i++ ) {
timestart('Test 1 query');
$sSQL = 'SELECT c . * , COUNT( * ) AS cnt
FROM `models` AS m
INNER JOIN categories AS c ON ( m.categories_id = c.categories_id )
GROUP BY m.categories_id
ORDER BY cnt DESC
LIMIT 3';
// echo $sSQL.'<br />';

$rRes = mysql_query($sSQL);

$aCategories = array();	
while ( $aRow = mysql_fetch_row($rRes) ) {
	$aCategories[] = $aRow;
}

timestop('Test 1 query');
}

// print_r($aCategories);

timeprint();
?>

Результаты тестирования:

Код:

счетчик 	кол-во вызовов 	общее сpеднее
Test 2 query	10	       1.1624	0.1162
Test 1 query	10	       4.4241	0.4424
вся пpогpамма pаботала 5.6119 сек
все внутpенные вызовы заняли 5.5866 сек (20 pаз)
остаток вpемени 0.0254 сек

itprog · 3 Ноя 2006

а если сначала выполнить цикл "Test 1 query", а уже потом "Test 2 query" ?

Также хотелось бы увидеть результаты 2-3 запусков данного теста.

alpine · 3 Ноя 2006

itprog

а если сначала выполнить цикл "Test 1 query", а уже потом "Test 2 query" ?

Код:

Test 1 query	10	4.3218	0.4322
Test 2 query	10	1.1389	0.1139

Также хотелось бы увидеть результаты 2-3 запусков данного теста.

Если в браузере нажать два-три раза ctrl+F5 устроит?

Код:

Test 1 query	10	4.3356	0.4336
Test 2 query	10	1.1346	0.1135

Test 1 query	10	4.3083	0.4308
Test 2 query	10	1.1426	0.1143

Test 1 query	10	4.3200	0.4320
Test 2 query	10	1.1457	0.1146

Результат устойчив.

-~{}~ 03.11.06 20:18:

Разница видна уже в EXPLAIN-ах - кол-во затронутых рядов и фулскам индекса при двух запросах и фулскан таблицы при одном.

sage · 4 Ноя 2006

спасибо за тесты. т.е. получается, что при INNER JOIN для кажой строке из 4518 анализируется 79 из другой таблицы? т.е. 4518 * 79 = 356 922 строк? или я ошибаюсь?

chira · 5 Ноя 2006

alpine

ещё один тест можешь сделать, для полноты картины, SQL:

Код:

SELECT c . *
FROM (SELECT categories_id, COUNT(*) as cnt
    FROM `models`
    GROUP BY categories_id
    ORDER BY cnt DESC
    LIMIT 3) AS m
   , categories AS c
WHERE m.categories_id = c.categories_id

alpine · 6 Ноя 2006

----------------------------------
Исследование производительности запроса с подзапросом в той же задаче.
----------------------------------
EXPLAIN:

Код:

mysql> EXPLAIN SELECT c . *
    ->     FROM (SELECT categories_id, COUNT(*) as cnt
    ->         FROM `models`
    ->         GROUP BY categories_id
    ->         ORDER BY cnt DESC
    ->         LIMIT 3) AS m
    ->        , categories AS c
    ->     WHERE m.categories_id = c.categories_id;
+----+-------------+------------+--------+---------------+------------+---------+-----------------+--------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key        | key_len | ref             | rows   | Extra         |
+----+-------------+------------+--------+---------------+------------+---------+-----------------+--------+----------------------------------------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL       | NULL    | NULL            |      3 |         |
|  1 | PRIMARY     | c          | eq_ref | PRIMARY       | PRIMARY    | 4       | m.categories_id |      1 | Using where         |
|  2 | DERIVED     | models     | index  | NULL          | categories | 4       | NULL            | 184810 | Using index; Using temporary; Usingfilesort |
+----+-------------+------------+--------+---------------+------------+---------+-----------------+--------+----------------------------------------------+
3 rows in set (0.11 sec)

PHP код:

PHP:

for ( $i=0; $i<$num_iterations; $i++ ) {
	timestart('Test 3 using subquery');
	$sSQL = 'SELECT c . *
	FROM (SELECT categories_id, COUNT(*) as cnt
	    FROM `models`
	    GROUP BY categories_id
	    ORDER BY cnt DESC
	    LIMIT 3) AS m
	   , categories AS c
	WHERE m.categories_id = c.categories_id';
	// echo $sSQL.'<br />';
	
	$rRes = mysql_query($sSQL);
	
	$aCategories = array();	
	while ( $aRow = mysql_fetch_row($rRes) ) {
		$aCategories[] = $aRow;
	}
	timestop('Test 3 using subquery');
}

Результаты:

Код:

счетчик 	вызовов общее сpеднее
Test 1 query	10	4.6128	0.4613
Test 2 query	10	1.1505	0.1150
Test 3 using subquery	10	1.1379	0.1138
вся пpогpамма pаботала 6.9062 сек
все внутpенные вызовы заняли 6.9011 сек (30 pаз)
остаток вpемени 0.0051 сек

sage · 7 Ноя 2006

большое спасибо за тесты. может кто-нить на мой вопросик ответить? )

т.е. получается, что при INNER JOIN для кажой строке из 4518 анализируется 79 из другой таблицы? т.е. 4518 * 79 = 356 922 строк? или я ошибаюсь... тогда почему такая разница во времени выполнения?

chira · 7 Ноя 2006

sage

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

sage · 7 Ноя 2006

chira
вы немного не поняли моего вопроса. про то, что вы написали, я знаю... мне интересно следующее: почему INNER JOIN выполняется в 4 раза медленее 2-х запросов либо запроса с подзапросом? несмотря на то, что кол-во строк, анализируемых MySQL при джойне меньше...

chira · 7 Ноя 2006

sage

что быстрее объединить 4518 зиписей и взять из них три
или объединить всего три записи?
что быстрее и во сколько раз на конкретных данных показывают примеры ...

zerkms · 8 Ноя 2006

alpine

Код:

mysql> EXPLAIN SELECT c . * , COUNT( * ) AS cnt 
FROM `models` AS m INNER JOIN categories AS c ON ( m.categories_id = c.categories_id ) 
GROUP BY m.categories_id ORDER BY cnt DESC LIMIT 3;
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key        | key_len | ref                      | rows | Extra                           |
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
|  1 | SIMPLE      | c     | ALL  | PRIMARY       | NULL       | NULL    | NULL                      | 4518 | Using temporary; Using filesort |
|  1 | SIMPLE      | m     | ref  | categories    | categories | 4       | metamarket.c.categories_id |   79 | Using where; Using index        |
+----+-------------+-------+------+---------------+------------+---------+----------------------------+------+---------------------------------+
2 rows in set (0.00 sec)
В наличии есть такие таблицы:

ответь честно, тебя не смущает что условие объединения не использует индекс????
меня - смущает и очень
попробуй сначала добиться чтобы заработал индекс (оптимайз или аналайз) и ТОЛЬКО потом проводи тесты. а то ты показал свои "качественные" запросы с индексами и очернил репутацию объединений плохим запросом без индексов (ай какой молодец)
посему - результаты тестов можно считать весьма и весьма посредственными
очень ждём результаты запросов с работающими индексами

alpine · 8 Ноя 2006

zerkms

ответь честно, тебя не смущает что условие объединения не использует индекс????

Не смущает.

очень ждём результаты запросов с работающими индексами

Угу, ждем теперь ваши запросы и ваши тесты с работающими индексами.

-~{}~ 08.11.06 12:39:

PS. Я предоставил структуру таблиц, индексы вы можете увидить из нее, предложите запрос(или рекомендации по созданию индексов) с правильным использованием индексов я его протестирую.

itprog · 8 Ноя 2006

предложите запрос(или рекомендации по созданию индексов)

(оптимайз или аналайз)

OPTIMIZE TABLE `models`;
OPTIMIZE TABLE `categories`;

ANALYZE TABLE `models`;
ANALYZE TABLE `categories`;

alpine · 8 Ноя 2006

itprog
Была не оптимазеная только таблица models. После оптимайза результаты остались идентичными предыдущим.

zerkms · 9 Ноя 2006

[...]

Объединение 2-х запросов выборки в 1

alpine

Новичок

zerkms

TDD infected

alpine

Новичок

itprog

Cruftsman

alpine

Новичок

sage

Новичок

chira

Новичок

alpine

Новичок

sage

Новичок

chira

Новичок

sage

Новичок

chira

Новичок

zerkms

TDD infected

alpine

Новичок

itprog

Cruftsman

alpine

Новичок

zerkms

TDD infected