Сортировка и выборка в MySQL

Question

[M03G]

Добрый день. Возможно, тема вопроса сформулирована не совсем правильно, но ниже я постараюсь разъяснить.
Я делаю выборrу из таблицы SELECT * FROM history WHERE (`dir` = 1 OR `dir` = 3) и получаю около 25 записей. Далее, я работаю с каждой записью, проверяя чем равен dir. Если =3, идёт приращение одной переменной. Если dir=1, то мне необходимо найти в этой же таблице последнюю по времени запись у которой dir=3 и номер позиции равен тому, который у той, что я нашёл с dir=1. (чтобы было чуть понятней, это таблица приходов/выдачи со склада. 1 = выдана позиция. 3 = принята на склад. в данном случая я ищу цену, по которой в последний раз пришла позиция, выдаваемая мной сейчас).
Запрос SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3 ORDER BY id DESC LIMIT 1 загружает MySQL чуть ли не намертво.
Посему я делаю полную выборку и уже в ней ищу максимальную цену обычным условием.

$costs = mysql_query("SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3");
while ($cost = mysql_fetch_array($costs)){
   if($testid<$cost[id]){
      $testid = $cost[id];
   }
}

Таким образом я получаю нужное мне значение достаточно быстро (не более 5сек).

Вопрос, почему так? И как лучше? Как оптимизировать?

Comments

[M03G]

В таком виде код выполняется за 0.38512396812439

$counthiss = mysql_query("SELECT * FROM history WHERE (`dir` = 1 OR `dir` = 3) $st $end LIMIT 500"); 
	while($counthis = mysql_fetch_array($counthiss)){
	if ($counthis[dir]==1){
		$testid=0;
		$costs = mysql_query("SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3");	
		while ($cost = mysql_fetch_array($costs)){
			if($testid<$cost[id]){
				$testid = $cost[id];
			}
		}
		$cost = mysql_query("SELECT doc_cost FROM history WHERE `id` = $testid");
		$cost = mysql_fetch_array($cost);

		$uh+=$counthis[count]*$cost[doc_cost];
	}
	if ($counthis[dir]==3){
		$pr+=$counthis[count]*$counthis[doc_cost];
	}
}

В таком виде код выполняется за 1.4529831409454

$counthiss = mysql_query("SELECT * FROM history WHERE (`dir` = 1 OR `dir` = 3) $st $end LIMIT 500"); 
	while($counthis = mysql_fetch_array($counthiss)){
	if ($counthis[dir]==1){
		$cost = mysql_query("SELECT id, doc_cost FROM history WHERE `posid` = $counthis[posid] AND `dir`=3 ORDER BY id DESC LIMIT 1");	
		$cost = mysql_fetch_array($cost);

		$uh+=$counthis[count]*$cost[doc_cost];
	}
	if ($counthis[dir]==3){
		$pr+=$counthis[count]*$counthis[doc_cost];
	}
}

Это всё с учётом того, что в начале я ограничиваю кол-во записей до 500. Если ограничения убрать — смерть…

[gaelpa]

А можно нескромный вопрос? Как вы так смогли опечататься в слове «выборrу» (я проверил даже: «r» — это «к» на клавиатуре, а дальше идет кириллическая «у»)?

[Sayonji]

Punto Switcher психанул, наверное. Он часто так.

Answer 1

[Stdit]

Скорее всего запускается фулскан, потому что не находит правильного индекса. Что говорят EXPLAIN запросов? И это, я надеюсь, $counthis[posid] эскейпится перед вставкой в строку запроса, а то выглядит опасно.

Comments

[rakeev]

Раз мусукл вешается при добавлении order by, то дело именно в нем. Но на id обычно primary вешают, потому не понятно как сортировка по нему может тормозить.
Вообще странный какой-то вопрос, выборка из таблицы с 25 записями не будет тормозить даже с fullscan и filesort.

Можно попробовать создать индекс на (posid, dir, id DESC) но это пальцем в небо при таких исходных.

[Stdit]

Логично, что дело в id, возможно он не объявлен как ключ. И выборка, как я понял, из огромной таблицы, просто фильтр проходят 25 записей.

[M03G]

Да, выглядит опасно, но тут не переживайте :) Примем за факт, что с передаваемыми данными всё ок.

[M03G]

Таблица не особо огромна (41600 записей).
индексы на id(primary), posid, dir

[Stdit]

Вообще довольно странно, таблица на 40 тысяч записей не должна мертво вешаться даже при полной сортировке. Может это какое-то нарушение таблицы mysql, или дикая фрагментация, попробуйте optimize table, repair table. И еще попробуйте заюзать составной индекс на posid, dir, id.

[Stdit]

А еще проверьте, сколько памяти выделено в mysql для буферов сортировок.

Answer 2

[M03G]

А можно нескромный вопрос? Как вы так смогли опечататься в слове «выборrу» (я проверил даже: «r» — это «к» на клавиатуре, а дальше идет кириллическая «у»)?

всё просто, сначала было выбору. Попытался исправить на выборку, но когда это увидел, стояла английская раскладку. И вместо К получилось R…

Answer 3

[Alexander Kouznetsov]

Я как-то не вижу жуткого криминала в ваших запросах.
Первое — постройте индексы по полям участвующим в запросах.

Второе, поскольку мускуль не поддерживает нормально вьюхи (или уже поддерживает? — вопрос в зал) создайте дополнительную таблицу типа:
pos_id int primary,
pos_last_recived DateTime,
pos_last_price float

И при получении на склад помимо history пишите значение в неё (insert on duplicate update)

Тогда простым запросом (через LEFT JOIN) сможете одновременно получать последнюю цену и дату прихода позиции.