Модераторы: skyboy, MoLeX, Aliance, ksnk

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Выборка записей по ID на большом объеме данных, тест производительности временных таблиц 
V
    Опции темы
Sunvas
Дата 26.7.2007, 11:12 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Соль и сахар
****


Профиль
Группа: Участник
Сообщений: 3388
Регистрация: 12.3.2006
Где: Тосно

Репутация: 2
Всего: 89
Есть таблица в которой ~1000000 записей. В ПХП есть одномерный массив, содежащий ~1000 чисел. Как за минимальное количество запросов вытащить из таблицы все записи ИДы которых содержаться в массиве?
 smile  smile 


Это сообщение отредактировал(а) mishaSL - 12.9.2007, 15:48


--------------------
Воспитывая детей по своему образу и подобию, родители почему-то надеются, что они будут лучше их.
PM MAIL   Вверх
GZep
Дата 26.7.2007, 11:35 (ссылка) | (голосов:5) Загрузка ... Загрузка ... Быстрая цитата Цитата


участник Винграда
***


Профиль
Группа: Завсегдатай
Сообщений: 1528
Регистрация: 7.7.2006
Где: Москва

Репутация: -1
Всего: 32
Код

for ($i = 1, $tmp = $ids[0]; $i < sizeof($ids); $i++)
    $tmp .= " or " . $ids[$i];
mysql_query("SELECT ... WHERE `id` = " . $tmp)

что-то типа такого...


--------------------
user posted imageuser posted image
PM MAIL WWW ICQ Skype GTalk   Вверх
Diesel Draft
Дата 26.7.2007, 12:03 (ссылка) | (голосов:3) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
Код

SELECT *
FROM table
WHERE id IN (value1, value2, value2)


Пример ясен?


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
Mal Hack
Дата 26.7.2007, 13:51 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Мудрый...
****


Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261
GZep, синтаксически неверно.

Вариант Diesel Draft, приемлим, рационален.
PM ICQ   Вверх
sTa1kEr
Дата 26.7.2007, 16:32 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


9/10 программиста
***


Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146
Конструкция"id IN (value1, value2, value2)" работает достаточно медленно, тем более с таким количеством элементов. По этому я бы предложил в данном случае использовать временную таблицу.
Код

$ids = array(1, 2, 3, 4, 5, 6, 7);

$query = "
   CREATE TEMPORARY TABLE `tmp` (
      `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
   PRIMARY KEY (`tempid`))";
mysql_query($query);

$query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")";
mysql_query($query);

$r = mysql_query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid");
while ($row = mysql_fetch_assoc($r))
{
   // ...
}

Имхо, это будет намного производительнее. Таблица создается в памяти на время коннекта к базе и эффективность INNER JOIN должна оправдать два лишних запроса.
PM MAIL   Вверх
Diesel Draft
Дата 26.7.2007, 17:21 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
А если одновременно 2 запита пойдут smile Ты ж не защитил


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
sTa1kEr
Дата 26.7.2007, 17:35 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


9/10 программиста
***


Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146
Цитата(Diesel Draft @  26.7.2007,  17:21 Найти цитируемый пост)
А если одновременно 2 запита пойдут smile Ты ж не защитил 

CREATE TABLE Syntax
Цитата

From MySQL 3.23 on, you can use the TEMPORARY  keyword when creating a table. A TEMPORARY  table is visible only to the current connection, and is dropped automatically when the connection is closed. This means that two different connections can use the same temporary table name without conflicting with each other or with an existing non-TEMPORARY table of the same name. (The existing table is hidden until the temporary table is dropped.) From MySQL 4.0.2 on, to create temporary tables, you must have the CREATE TEMPORARY TABLES privilege.

Другими словами, временная таблица создается для одного коннекта и видима только в пределах этого коннекта, по этому можно не опасатся конфликтов.
PM MAIL   Вверх
Diesel Draft
Дата 26.7.2007, 17:44 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
А я не увидел, думал ты обычную хочешь впихнуть. 

Но все ровно подумай, операция записи в БД медленней работает чем выборка данных. Если ты считаешь что IN будет тормозить то можно через or делают, но это вовсе БД убьет вовсе  smile 


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
sTa1kEr
Дата 26.7.2007, 17:56 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


9/10 программиста
***


Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146
Цитата(Diesel Draft @  26.7.2007,  17:44 Найти цитируемый пост)
Но все ровно подумай, операция записи в БД медленней работает чем выборка данных. 

Таблица находится в памяти и вставка 1000 строк с одним столбцом одним запросом будет моментальная, а вот выборка через IN будет намного медленнее, чем через JOIN.
PM MAIL   Вверх
Diesel Draft
Дата 26.7.2007, 17:57 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
Почему? Ну вот ты так сказал, а ты на что то опирается?



П.С. Кстати 1000000 записей в Мускуле? Это немного жестоко получается. наверно уже притормаживает

Это сообщение отредактировал(а) Diesel Draft - 26.7.2007, 17:58


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
Mal Hack
Дата 26.7.2007, 18:20 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Мудрый...
****


Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261
Столько шума из ничего... Ну создадите вы временную таблицу, а толку? Выборку-то все равно надо делать...
PM ICQ   Вверх
Diesel Draft
Дата 26.7.2007, 18:23 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
А где это ты будешь юзать?


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
Mal Hack
Дата 26.7.2007, 18:51 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Мудрый...
****


Профиль
Группа: Участник Клуба
Сообщений: 9926
Регистрация: 15.2.2004

Репутация: 11
Всего: 261
Временную таблицу? Когда выборка будет многоступенчатой...
PM ICQ   Вверх
sTa1kEr
Дата 26.7.2007, 22:57 (ссылка) | (голосов:4) Загрузка ... Загрузка ... Быстрая цитата Цитата


9/10 программиста
***


Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146
Цитата(Mal Hack @  26.7.2007,  18:20 Найти цитируемый пост)
Столько шума из ничего...

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

Цитата(Mal Hack @  26.7.2007,  18:20 Найти цитируемый пост)
Ну создадите вы временную таблицу, а толку? Выборку-то все равно надо делать... 

Смысл временной таблицы я изложил выше.

Написал маленький тестик для сравнения производительности.
PHP 5.2.2
MySQL 5.0.38

Тестовая таблица:
Код

CREATE TABLE `test` (
`id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`data` VARCHAR( 50 ) NOT NULL ,
`timestamp` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
) ENGINE = MYISAM ;


Наполнение таблицы:
Код

$db = new mysqli("localhost", "root", "", "test") or die($db->error);

// Вставка 10 млн строк. При выборке из первого млн. строк производительность такая же,
// как если бы было просто 1 млн. строк. Если кто не верит, то можете сначала заполнить
// только 1 млн, а затем дописать еще 9.
for ($i = 0; $i < 10000; $i++) 
{
   $query = "INSERT INTO test (`data`, `timestamp`) VALUES ('".rand(0, 10000000)."', NOW())";
   for ($j = 1; $j < 1000; $j++)
   {
      $query .= ", ('".rand(0, 10000000)."', NOW())"; // Рандомные данные
   }
   $db->query($query) or die($db->error);
}

echo "Ok\n";


Тестиование.
Код

function generateIds($numIds, $numRows)
{
   echo "--- Testing for $numIds IDs and $numRows rows ---\n";
   $i = 0;
   $ids = array();
   while ($i < $numIds)
   {
      $id = rand (1, $numRows);
      if (!in_array($id, $ids))
      {
         $ids[] = $id;
         $i++;
      }
   }
   return $ids;
}

function test_1($db, $ids)
{
   $time = microtime(true);
   $query = "SELECT * FROM `test` WHERE id IN (".implode(", ", $ids).")";
   $r = $db->query($query);
   echo "Test 1: ".(microtime(true) - $time)." sec\n";
}

function test_2($db, $ids)
{
   $time = microtime(true);
   $query = "
      CREATE TEMPORARY TABLE `tmp` (
      `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
      PRIMARY KEY (`tempid`))";
   $db->query($query);
   $query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")";
   $db->query($query);
   $r = $db->query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid");
   echo "Test 2: ".(microtime(true) - $time)." sec\n";
   $db->query("DROP TABLE `tmp`"); // Уничтожаем таблицу только для чистоты эксперимента, а так ее уничтожать не нужно.
}

$db = new mysqli("localhost", "root", "", "test");

$ids = generateIds(1000, 1000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(1000, 10000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(10000, 1000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(10000, 10000000);
test_1($db, $ids);
test_2($db, $ids);


Результаты теста
Код

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.013659954071 sec
Test 2: 0.00576281547546 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 0.64941906929 sec
Test 2: 0.00584292411804 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 0.143054962158 sec
Test 2: 0.0594811439514 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 1.94255399704 sec
Test 2: 0.0605499744415 sec


Как видите, при условиях Sunvas (1ый тест) временные таблицы выигрывают по времени в 2 раза. Однако, при увеличении количества строк в 10 раз, время при использовании IN увеличивается в 15 раз!, а INNER JOIN выполняется за то-же самое время. 
PM MAIL   Вверх
Diesel Draft
Дата 27.7.2007, 00:05 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 876
Регистрация: 18.1.2005
Где: Lviv, Ukraine

Репутация: нет
Всего: 5
sTa1kEr, ты герой  smile 


Просто мы делам в 3 раза больше операций


--------------------
НЕДОМА в маси 
PM MAIL WWW ICQ GTalk   Вверх
FractalizeR
Дата 15.1.2008, 13:56 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 273
Регистрация: 27.12.2007
Где: Россия/Москва

Репутация: 2
Всего: 4
Ну, и что, до сих пор никто не нашел ошибку sTa1kEr? Тогда позвольте мне....

Обратите внимание на вот эту строчку:
Код

$r = $db->query("SELECT * FROM table AS t INNER JOIN tmp AS tm ON t.id = tm.tempid");


Откуда у нас взялась таблица table? Мы ее не создавали! У нас есть только tmp (временная) и test (исходная). В результате этот запрос всегда НЕ ВЫПОЛНЯЕТСЯ, что создает ИЛЛЮЗИЮ того, что эта сумасшедшая комбинация действительно быстрее, чем IN конструкция...
Если исправить этот table на test, как и должно быть, результаты теста будут совсем другими: 
Код

C:\temp>php test.php
--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.056794881820679 sec
Test 2: 0.14567685127258 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 0.06480598449707 sec
Test 2: 0.15046095848083 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 0.48508286476135 sec
Test 2: 0.64331102371216 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 0.65538215637207 sec
Test 2: 0.86895084381104 sec


Отсюда мораль: всегда проверяйте результат выполнения запроса. sTa1kEr использует не слишком хорошо написанный класс по работе с базой данных, который, почему-то не выбрасывает ни ошибки, не исключения при ошибке в синтаксисе запроса....

Мой вариант PHP файла, на котором проводился тест, с использованием стандартных функций MySQL:
Код

<?php
$db = mysql_connect('localhost', 'root', 'root');
if (!$db) {
   die('Could not connect: ' . mysql_error());
}

mysql_select_db('testin');

function generateIds($numIds, $numRows)
{
   echo "--- Testing for $numIds IDs and $numRows rows ---\n";
   $i = 0;
   $ids = array();
   while ($i < $numIds)
   {
      $id = rand (1, $numRows);
      if (!in_array($id, $ids))
      {
         $ids[] = $id;
         $i++;
      }
   }
   return $ids;
}
function test_1($db, $ids)
{
   $time = microtime(true);
   $query = "SELECT * FROM `test` WHERE id IN (".implode(",", $ids).")";
   $r = mysql_query($query) or die('Test 1 query error!');
   echo "Test 1: ".(microtime(true) - $time)." sec\n";
}
function test_2($db, $ids)
{
   $time = microtime(true);
   $query = "
      CREATE TEMPORARY TABLE `tmp` (
      `tempid` INTEGER UNSIGNED NOT NULL DEFAULT NULL AUTO_INCREMENT,
      PRIMARY KEY (`tempid`))";
   mysql_query($query) or die('Test 2-1 query error!');
   $query = "INSERT INTO tmp (`tempid`) VALUES (".implode('), (', $ids).")";
   mysql_query($query)or die('Test 2-2 query error!');
   mysql_query("SELECT * FROM test AS t INNER JOIN tmp AS tm ON t.id = tm.tempid")or die('Test 2-3 query error!');
   mysql_query("DROP TABLE `tmp`")or die('Test 2-4 query error!'); // Уничтожаем таблицу только для чистоты эксперимента, а так ее уничтожать не нужно.
   echo "Test 2: ".(microtime(true) - $time)." sec\n";
}

$ids = generateIds(1000, 1000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(1000, 10000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(10000, 1000000);
test_1($db, $ids);
test_2($db, $ids);
$ids = generateIds(10000, 10000000);
test_1($db, $ids);
test_2($db, $ids);

?>


Это сообщение отредактировал(а) FractalizeR - 15.1.2008, 13:57


--------------------
Чтобы поблагодарить или наоборот поругать участника форума лучше пользоваться значками "+" и "-", изменяющими репутацию. Они находятся слева от поста под именем пользователя.
PM MAIL   Вверх
Sunvas
Дата 15.1.2008, 14:02 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Соль и сахар
****


Профиль
Группа: Участник
Сообщений: 3388
Регистрация: 12.3.2006
Где: Тосно

Репутация: 2
Всего: 89
Хм, видимо придется поднять старую темку.


--------------------
Воспитывая детей по своему образу и подобию, родители почему-то надеются, что они будут лучше их.
PM MAIL   Вверх
SelenIT
Дата 15.1.2008, 19:17 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


баг форума
****


Профиль
Группа: Завсегдатай
Сообщений: 3996
Регистрация: 17.10.2006
Где: Pale Blue Dot

Репутация: 9
Всего: 401
Во-первых, FractalizeRу плюс за глазастость!

Но ситуация несколько сложнее. Я тестировал оба варианта (с mysqli и mysql_xxx) на двух машинах - стареньком Duron-1200 с 512 "мозгами" (одной планкой) и ноутбуке с Core2Duo T5500 (1.66 ГГц) и гигагабайтом памяти (двухканальной), оба под WinXP (к сожалению). Результаты вот:

Duron-1200:
Цитата
mysqli (вариант sTa1kErа с исправленной ошибкой):

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 3.36059403419 sec
Test 2: 0.0949058532715 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 14.3087098598 sec
Test 2: 0.0993778705597 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 4.78998088837 sec
Test 2: 0.497647047043 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 89.8976659775 sec
Test 2: 0.560079097748 sec

То же, закомменчено удаление временной таблицы:

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.602582931519 sec
Test 2: 0.0892629623413 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 11.9096381664 sec
Test 2: 0.0678989887238 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 1.6547908783 sec
Test 2: 0.141529083252 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 87.7105460167 sec
Test 2: 0.170500993729 sec


Классический mysql (вариант FractalizeRа):

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.594168901443 sec
Test 2: 0.100327014923 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 11.9726989269 sec
Test 2: 0.102813005447 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 1.57574701309 sec
Test 2: 0.53061914444 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 75.2531871796 sec
Test 2: 0.555109977722 sec

Core2Duo T5500:
Цитата
mysqli (вариант sTa1kErа с исправленной ошибкой):

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.0285499095917 sec
Test 2: 0.101317882538 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 0.0348329544067 sec
Test 2: 0.114646911621 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 0.195830821991 sec
Test 2: 0.326441764832 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 0.245867013931 sec
Test 2: 0.439071893692 sec

То же, закомменчено удаление временной таблицы:

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.0287158489227 sec
Test 2: 0.0863630771637 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 0.0342700481415 sec
Test 2: 0.0620341300964 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 0.193419933319 sec
Test 2: 0.280586004257 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 0.246789932251 sec
Test 2: 0.233731985092 sec


Классический mysql (вариант FractalizeRа):

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.0272779464722 sec
Test 2: 0.188948154449 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 0.0334341526031 sec
Test 2: 0.1519510746 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 0.19315290451 sec
Test 2: 0.380733013153 sec
--- Testing for 10000 IDs and 10000000 rows ---
Test 1: 0.247782945633 sec
Test 2: 0.466258049011 sec

Поражают как тормоза варианта с IN на старой одноядерной машине, так и быстрота варианта с джойном (без принудительного удаления времянки) на ней же. При хорошем запасе памяти и ресурсов проца, безусловно, вариант с IN (особенно при небольшом диапазоне) выглядит куда лучше, но вариант с времянкой, похоже, менее зависим от этих "мелочей". Надо бы протестить в более реальных условиях - под *NIX, под нагрузкой...

Добавлено через 5 минут и 34 секунды
Цитата(FractalizeR @  15.1.2008,  13:56 Найти цитируемый пост)
sTa1kEr использует не слишком хорошо написанный класс по работе с базой данных

Это не класс, это улучшенное (по сравнению со старой php_mysql) расширение PHP5. Поддерживаются несколько запросов за вызов и новые возможности самой базы, но "волшебства" для перехвата ошибок в нем, увы, нет.


--------------------
Осторожно! Данный юзер и его посты содержат ДГМО! Противопоказано лицам с предрасположенностью к зонеризму!
PM MAIL   Вверх
FractalizeR
Дата 16.1.2008, 00:46 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 273
Регистрация: 27.12.2007
Где: Россия/Москва

Репутация: 2
Всего: 4
Меня вообще-то поражает не это.
Цитата

То же, закомменчено удаление временной таблицы:

--- Testing for 1000 IDs and 1000000 rows ---
Test 1: 0.602582931519 sec
Test 2: 0.0892629623413 sec
--- Testing for 1000 IDs and 10000000 rows ---
Test 1: 11.9096381664 sec
Test 2: 0.0678989887238 sec
--- Testing for 10000 IDs and 1000000 rows ---
Test 1: 1.6547908783 sec
Test 2: 0.141529083252 sec


Тестирование 1.000 тысячи записей с IN заняло 11.9 секунд, а тот же самый тест с IN но для 10.000 (в 10 раз больше) записей занял 1.6 секунды (в 10 раз меньше). Вам это не кажется странным?
В общем, я думаю, вы просто что-то сделали неправильно. IN со скалярными значениями всегда быстрее любых других вариантов должен быть.

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 00:47


--------------------
Чтобы поблагодарить или наоборот поругать участника форума лучше пользоваться значками "+" и "-", изменяющими репутацию. Они находятся слева от поста под именем пользователя.
PM MAIL   Вверх
SelenIT
Дата 16.1.2008, 17:40 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


баг форума
****


Профиль
Группа: Завсегдатай
Сообщений: 3996
Регистрация: 17.10.2006
Где: Pale Blue Dot

Репутация: 9
Всего: 401
Цитата(FractalizeR @  16.1.2008,  00:46 Найти цитируемый пост)
Тестирование 1.000 тысячи записей с IN заняло 11.9 секунд, а тот же самый тест с IN но для 10.000 (в 10 раз больше) записей занял 1.6 секунды (в 10 раз меньше). Вам это не кажется странным?

Не в 10, а в 7, если быть совсем точным smile. Нет, не удивляет - объем данных, перелопаченных сервером, в 10 раз меньше, как ни крути...

Цитата(FractalizeR @  16.1.2008,  00:46 Найти цитируемый пост)
IN со скалярными значениями всегда быстрее любых других вариантов должен быть.

Не могу за это поручиться, когда речь идет о MySQL - у нее, насколько я помню, "наследственные проблемы" с оптимизацией множественных OR в условии WHERE (а IN, насколько я понимаю - частный случай этого по сути). В каких-то версиях доходило до того, что UNION отдельных запросов с единственным значением в каждом оказывался быстрее. Нужно будет протестить как следует - чисто базу, без PHP, посмотреть EXPLAIN, желательно под никсами... постараюсь сделать ближе к вечеру, если кто сделает раньше/параллельно - буду только рад.


--------------------
Осторожно! Данный юзер и его посты содержат ДГМО! Противопоказано лицам с предрасположенностью к зонеризму!
PM MAIL   Вверх
FractalizeR
Дата 16.1.2008, 17:54 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 273
Регистрация: 27.12.2007
Где: Россия/Москва

Репутация: 2
Всего: 4
Цитата

Не в 10, а в 7, если быть совсем точным smile. Нет, не удивляет - объем данных, перелопаченных сервером, в 10 раз меньше, как ни крути...

Не в 10 раз меньше, а в 10 раз больше. Посмотрите внимательно на результаты теста. Тысяча строк отобрана за двенадчать секунд, а десять тысяч - за две секунды!

Цитата

у нее, насколько я помню, "наследственные проблемы" с оптимизацией множественных OR в условии WHERE (а IN, насколько я понимаю - частный случай этого по сути)

Почитайте мануал MySQL по использованию конструкции IN и вам все станет понятно.


--------------------
Чтобы поблагодарить или наоборот поругать участника форума лучше пользоваться значками "+" и "-", изменяющими репутацию. Они находятся слева от поста под именем пользователя.
PM MAIL   Вверх
SelenIT
Дата 16.1.2008, 18:39 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


баг форума
****


Профиль
Группа: Завсегдатай
Сообщений: 3996
Регистрация: 17.10.2006
Где: Pale Blue Dot

Репутация: 9
Всего: 401
Цитата(FractalizeR @  16.1.2008,  17:54 Найти цитируемый пост)
Тысяча строк отобрана за двенадчать секунд, а десять тысяч - за две секунды!

Но 10 тыс. отобраны из одного миллиона, а 1 тыс. - из десяти миллионов! Посмотрите внимательно на условия теста ;). Есть же разница для сервера, имхо...

Цитата(FractalizeR @  16.1.2008,  17:54 Найти цитируемый пост)
Почитайте мануал MySQL по использованию конструкции IN и вам все станет понятно. 

Вот относительно недавний FAQ, первый же вопрос которого полностью совпадает с тем, что некогда крепко засело в моем мозгу (причем автор утверждает, что и ORы, и IN с константами используют бинарный поиск - судя по комментам здесь, начиная с 4-й версии). Но ведь все равно, насколько я понимаю, этот бинарный поиск будет делаться для каждой строки исходной таблицы...

Насчет UNOIN'а, признаю, прогнал (судя по тем же комментам).


--------------------
Осторожно! Данный юзер и его посты содержат ДГМО! Противопоказано лицам с предрасположенностью к зонеризму!
PM MAIL   Вверх
FractalizeR
Дата 16.1.2008, 19:01 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 273
Регистрация: 27.12.2007
Где: Россия/Москва

Репутация: 2
Всего: 4
Цитата

Но 10 тыс. отобраны из одного миллиона, а 1 тыс. - из десяти миллионов! Посмотрите внимательно на условия теста ;). Есть же разница для сервера, имхо...


Количество строк в таблице никто не менял. Откуда там один миллион и десять миллионов? Просто опять же автор теста допустил ошибку и тут. А я ее за ним скопировал. Нолик не дописан.

Цитата

Вот относительно недавний FAQ, первый же вопрос которого полностью совпадает с тем, что некогда крепко засело в моем мозгу

О том, как работает IN написано в мануале по MySQL.

О ветке 3.x MySQL давно уже пора забыть. 

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 19:07


--------------------
Чтобы поблагодарить или наоборот поругать участника форума лучше пользоваться значками "+" и "-", изменяющими репутацию. Они находятся слева от поста под именем пользователя.
PM MAIL   Вверх
SelenIT
Дата 16.1.2008, 19:23 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


баг форума
****


Профиль
Группа: Завсегдатай
Сообщений: 3996
Регистрация: 17.10.2006
Где: Pale Blue Dot

Репутация: 9
Всего: 401
Цитата(FractalizeR @  16.1.2008,  19:01 Найти цитируемый пост)
Откуда там один миллион и десять миллионов? ... Вы невнимательно прочли условия теста и код, который создавал таблицу

Цитата(sTa1kEr @  26.7.2007,  22:57 Найти цитируемый пост)
При выборке из первого млн. строк производительность такая же,
// как если бы было просто 1 млн. строк. Если кто не верит, то можете сначала заполнить
// только 1 млн, а затем дописать еще 9.

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

Цитата(FractalizeR @  16.1.2008,  19:01 Найти цитируемый пост)
О том, как работает IN написано в мануале по MySQL.

Читал. Сортирует, потом бинарный поиск. Написано, что это very quick, если в списке одни константы одного типа. Но вряд ли при этом предполагаются десятитысячные диапазонища... 

Цитата(FractalizeR @  16.1.2008,  19:01 Найти цитируемый пост)
О ветке 3.x MySQL давно уже пора забыть.

Пора, не спорю. Не всегда получается ;)


--------------------
Осторожно! Данный юзер и его посты содержат ДГМО! Противопоказано лицам с предрасположенностью к зонеризму!
PM MAIL   Вверх
sTa1kEr
Дата 16.1.2008, 19:58 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


9/10 программиста
***


Профиль
Группа: Завсегдатай
Сообщений: 1553
Регистрация: 21.2.2007

Репутация: 9
Всего: 146
Извините, не имею тех тестов под рукой, надо будет поискать их. Но каюсь, что пред отправкой сообщения, я менял имена таблиц (и потом еще и редактировал пост с тем же намеренем), т.ч. сейчас уже не вспомню, толи это моя опечатка тут, толи в реальном тесте. Но

1. Количество строк действительно было разное, что я и написал в посте.
2. С корректными запросами я точно проводил тест.
3. Это было неоднократно замечено и испробовано на рабочих, реальных данных. Причем не только в MySQL но и в MSSQL, результат один - IN всегда проигрывает временной таблице, хранящейся в памяти(важно, что именно в памяти, т.к. можно и на диске создать временную). А на особо сложных запросах, это настолько особо критично.
PM MAIL   Вверх
FractalizeR
Дата 16.1.2008, 20:05 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 273
Регистрация: 27.12.2007
Где: Россия/Москва

Репутация: 2
Всего: 4
Хотелось бы, чтобы вы привели образцы скриптов, где IN медленнее, чтобы я смог провести тестирование на своем компьютере. Мне с трудом верится, что конструкция IN может быть настолько неоптимальной. Конечно, в MySQL 3.23 ее использование растягивалось в цепь OR и индекс не использовался. Но 3.23 пора забыть, как страшный сон smile

Что касается количества рядов - то ведь оно касалось не таблицы, а функции, которая генерирует случайные id от нуля до переданного ей количества строк в таблице. Но сама таблица не менялась.

Это сообщение отредактировал(а) FractalizeR - 16.1.2008, 20:30


--------------------
Чтобы поблагодарить или наоборот поругать участника форума лучше пользоваться значками "+" и "-", изменяющими репутацию. Они находятся слева от поста под именем пользователя.
PM MAIL   Вверх
SelenIT
Дата 17.1.2008, 06:26 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


баг форума
****


Профиль
Группа: Завсегдатай
Сообщений: 3996
Регистрация: 17.10.2006
Где: Pale Blue Dot

Репутация: 9
Всего: 401
FractalizeR, попробуйте Ваш же скрипт на таблице с еще на порядок большим (100 млн.) числом записей. Свои результаты запощу позже - "для чистоты эксперимента".


--------------------
Осторожно! Данный юзер и его посты содержат ДГМО! Противопоказано лицам с предрасположенностью к зонеризму!
PM MAIL   Вверх
Страницы: (2) [Все] 1 2 
 Ответ в темуСоздание новой темы Создание опроса
0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | PHP: Базы Данных | Следующая тема »

Подписаться на тему | Подписка на этот форум | Скачать/Распечатать тему


 




[ Время генерации скрипта: 0.1280 ]   [ Использовано запросов: 22 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
 По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.