[ Поиск ] - [ Пользователи ] - [ Календарь ]
Полная Версия: Помогите ускорить SELECT COUNT(*)
Форум PHP программистов > PHP для начинающих
GET
14.11.2011 - 14:34
Здравствуйте, помогите разобраться с причиной медленного SELECT COUNT(*)'a

<select name='ff'>

//Вот запрос из первой таблицы, в ней около 500 строк, положительным 
//результатом выборки будет около 300:  
		$m=mysql_query("SELECT `id_m`,`md` FROM `tab1` WHERE `k_id`='".$nom."' AND `access`=1 AND `id_m`!='".(int)$mod."'");
		if (mysql_num_rows($m))
		{
			while($m_i=mysql_fetch_assoc($m))
			{
// это второй запрос в этой таблице 1 001 000				
$n=mysql_query("SELECT COUNT(*) FROM `tab2` WHERE `k_id`='".$nom."' AND `pole_m`='".$m_i['id_m']."' AND (`ps`=1 OR `ps`=2)");
				$n_i=mysql_fetch_row($n);
				if ($n_i[0]>0)
				{?>
					<option value="<?php echo $m_i['id_m'];?>">
					               <?php echo $m_i['md']; ?>
					</option><?php
				}
			}
</select>


т.е. 300 раз выполняется SELECT COUNT(*) для таблицы из миллиона строк в случаи нахождения строки рисуется значение в список.

Поиск длится очень долго (около 90 секунд). Обе таблицы проиндексированны, все поля по которым ведется поиск (int). Почему так не могу понять. По моим расчетам первую выборку т.е. из первой таблицы где 500 строк можно в расчет вообще не брать, но SELECT COUNT(*) тоже не должен так тормозить.

Еще хочу отметить, что в миллионе "насильно" вбитых строк второй таблицы поля k_id и ps одинаковые (в смысле у все строк один и тот же k_id и ps, разкмеется в реальной таблице они будут разные).

Спасибо.



Спустя 2 минуты, 52 секунды (14.11.2011 - 13:37) sharki написал(а):
а если 
COUNT(`id`)

Спустя 4 минуты, 34 секунды (14.11.2011 - 13:42) GET написал(а):
sharki

113 сек.

Спустя 9 минут, 34 секунды (14.11.2011 - 13:51) alex12060 написал(а):
Ну во первых, лучше расставить индексы. Во вторых, делать ресурсоемкий такой запрос в цикле ооочень не надо.

Да и вообще, тут можно сделать 1 запрос, с объединением. Не знаю, получится ли с первого раза, но как-то так:


SELECT a.id_m, a.md FROM `tab1` a
LEFT JOIN `tab2` b ON a.id_m = b.pole_m
WHERE a.k_id = $nom AND a.access = 1 AND a.id_m != '(int)$mod' AND (b.ps = 1 OR b.ps = 2)


Как-то так

Спустя 7 минут, 6 секунд (14.11.2011 - 13:58) Guest написал(а):
alex12060
спасибо...буду думать дальше

А.В.С. (выхожу с мобильного)

Спустя 3 минуты, 47 секунд (14.11.2011 - 14:02) SlavaFr написал(а):
CREATE INDEX index_count1
ON tab2 ( `k_id`, `pole_m`,`ps`)

Спустя 1 минута, 49 секунд (14.11.2011 - 14:04) SlavaFr написал(а):
покажи

show create table tab2;

Спустя 27 минут, 4 секунды (14.11.2011 - 14:31) GET написал(а):
CREATE TABLE `tab2` (
  `id` int(11) NOT NULL auto_increment,
  `ps` enum('1','2','3') NOT NULL,
  `k_id` smallint(5) NOT NULL,
  `is` mediumint(9) NOT NULL,
  `lg` varchar(15) NOT NULL,
  `date` datetime NOT NULL,
  `i` varchar(16) NOT NULL,
  `pole_m` smallint(3) NOT NULL,
  `p_110` tinyint(2) NOT NULL,
  `p_111` tinyint(2) default NULL,
  `p_112` tinyint(2) default NULL,
  `p_113` tinyint(2) default NULL,
  `p_114` tinyint(2) default NULL,
  `p_220` tinyint(2) NOT NULL,
  `p_330` tinyint(2) NOT NULL,
  `p_20` varchar(8) NOT NULL,
  `p_50` varchar(18) NOT NULL,
  `cn` float(10,2) NOT NULL,
  `op` varchar(250) default NULL,
  `pb` enum('1','2') NOT NULL,
  `ft` enum('1','2') NOT NULL,
  `counter` mediumint(9) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `ps` (`ps`,`k_id`,`i`,`pole_m`,`p_110`,`p_111`,`p_112`,`p_113`,`p_114`,`p_220`,`p_330`,`cn`,`ft`)
) ENGINE=MyISAM AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8 AUTO_INCREMENT=1000001 ;

Спустя 16 минут, 45 секунд (14.11.2011 - 14:48) SlavaFr написал(а):
....(b.ps = '1' OR b.ps ='2')

К стсате из за того что ты забыл кавички оно не только медленее работает, но и дает неправилный результат

не за бутдь индексы зделать

Спустя 55 секунд (14.11.2011 - 14:49) GET написал(а):
SlavaFr

индексы же сделаны

Спустя 4 минуты, 5 секунд (14.11.2011 - 14:53) SlavaFr написал(а):
Цитата (A.B.C. @ 14.11.2011 - 11:49)
SlavaFr

индексы же сделаны

да, но не для конкретного запроса


кавычки поставил?

Спустя 38 секунд (14.11.2011 - 14:53) GET написал(а):
SlavaFr

да...да...87 стало секунд, может так и должно быть?

Спустя 54 секунды (14.11.2011 - 14:54) GET написал(а):
SlavaFr

покажи пример индексов для кокретного запроса?

Спустя 35 минут, 4 секунды (14.11.2011 - 15:29) Placido написал(а):
Запрос:
$n=mysql_query("SELECT COUNT(*) FROM `tab2` WHERE `k_id`='".$nom."' AND `pole_m`='".$m_i['id_m']."' AND (`ps`=1 OR `ps`=2)");

Индексы:
CREATE INDEX `ixK_id` ON `tab2` (`k_id`);
CREATE INDEX `ixPole_m` ON `tab2` (`pole_m`);
CREATE INDEX `ixPs` ON `tab2` (`ps`);

Спустя 5 минут, 5 секунд (14.11.2011 - 15:34) GET написал(а):
Placido

непойму, а это (из дампа таблицы выше):

"KEY `ps` (`ps`,`k_id`,`i`,`pole_m`,`p_110`,`p_111`,`p_112`,`p_113`,`p_114`,`p_220`,`p_330`,`cn`,`ft`)
)"

Спустя 24 минуты, 44 секунды (14.11.2011 - 15:59) imbalance_hero написал(а):
Индекс должен быть по:
1. k_id
2. pole_m
3. ps

Ни более. Один общий составной индекс.

Спустя 15 минут, 33 секунды (14.11.2011 - 16:15) Placido написал(а):
Не знаю. Вообще, такой индекс полезен в случае, если отбор нужно сделать сразу по всем этим полям (например, WHERE `ps` = ... and `k_id`... and `i`... and ... and ...). В данной ситуации такой индекс бесполезен, насколько я понимаю.

З.Ы. Не претендую на истину в последней инстанции - могу и ошибаться.

Спустя 4 минуты, 40 секунд (14.11.2011 - 16:19) Placido написал(а):
Согласен с imbalance_hero, для данного конкретного запроса лучше сделать один составной индекс (`k_id`, `pole_m`, `ps`).

Спустя 1 минута, 12 секунд (14.11.2011 - 16:21) imbalance_hero написал(а):
Ну посмотрим, подождем ТС, что скажет.

Спустя 9 минут, 11 секунд (14.11.2011 - 16:30) SlavaFr написал(а):
Цитата (imbalance_hero @ 14.11.2011 - 12:59)
Индекс должен быть по:
1. k_id
2. pole_m
3. ps

Ни более. Один общий составной индекс.

Цитата (SlavaFr @ 14.11.2011 - 10:58)
CREATE INDEX index_count1
ON tab2 ( `k_id`, `pole_m`,`ps`)


Спустя 11 минут, 8 секунд (14.11.2011 - 16:41) SlavaFr написал(а):
SELECT `tab1`.`id_m`,`tab1`.`md` ,
 (SELECT COUNT(*) FROM `tab2`
       WHERE `k_id`=`tab1`.`k_id` AND `pole_m`=`tab1`.`id_m` AND (`ps`='1'  OR `ps`='2')) as cnt
FROM `tab1`
 WHERE `tab1`.`k_id`='".$nom."' AND `tab1`.`access`='1' AND `tab1`.`id_m`!='".(int)$mod."'"



к тем индехсам что я уже предложил делаеш индех на tab1.id_m,tab1.k_id i na access

Спустя 2 минуты (14.11.2011 - 16:43) imbalance_hero написал(а):
SlavaFr
Не думаю, что подзапрос в итоге выйдет быстрее smile.gif Я вот у себя один раз запустил ради теста в цикле, и что... производительность выросла в 2 раза smile.gif

Спустя 30 минут, 56 секунд (14.11.2011 - 17:14) SlavaFr написал(а):
Цитата (imbalance_hero @ 14.11.2011 - 13:43)
SlavaFr
Не думаю, что подзапрос в итоге выйдет быстрее smile.gif Я вот у себя один раз запустил ради теста в цикле, и что... производительность выросла в 2 раза smile.gif

спекуляция

все зависет от многих факторов включая версию базы, правельные индехы и количество строк

личный опыт показывал, что от один суб-запрос не замедляет работу базы.

Спустя 18 минут, 49 секунд (14.11.2011 - 17:33) GET написал(а):
Парни спасибо за помощь. Буду пробывать с составным индексом завтра доложу, глаза закрываются уже и с мобильника тяжело код набирать. Всем огромное спаибо.

Спустя 8 часов, 8 секунд (15.11.2011 - 01:33) GET написал(а):
Хочу поделиться результатами испытаний составного индекса ( `k_id`, `pole_m`,`ps`): предложенного SlavaFr и imbalance_hero , производительность возросла в 17 раз (!) с 3,5 секунды до 0,2.

laugh.gif спасииибо

Спустя 12 минут, 50 секунд (15.11.2011 - 01:46) dadli написал(а):
A.B.C.
скажи пожалуиста какои метод ти исползуеш для вичисленя времени запроса

Спустя 7 минут, 55 секунд (15.11.2011 - 01:54) GET написал(а):
В начале скрипта:

include'timer.class.php';
$timer = new timer();
$z = 0;
$timer->start_timer();


....
....
сам скрипт
....
....

В конце скрипта:
$firstTime = $timer->end_timer();
echo "<br><span style='background-color: #000000; color: #CCCC00;'>Время работы скрипта составило=".$firstTime."сек.</span><br>";



сам класс timer.class.php:

set_time_limit(0);

class timer
{
	private $start_time;

	private function get_time()
	{
		list($usec, $seconds) = explode(" ", microtime());
		return ((float)$usec + (float)$seconds);
	}
	
	function start_timer()
	{
		$this->start_time = $this->get_time();
	}
	
	function end_timer()
	{
		return ($this->get_time() - $this->start_time);
	}
}

Спустя 35 минут, 20 секунд (15.11.2011 - 02:29) GET написал(а):
Хотел бы еще спросить по сути составного индекса:( `k_id`, `pole_m`,`ps`)

в случае если сортировки, скажем по полю `pole_m` нет, то индекс работать уже не будет точнее будет только по `k_id`??? а по `ps` уже нет так как цепочка оборвалась, если я правильно понял?

И если так, то будет ли выходом задать для `pole_m` искусственную сортировку ну что-то типа `pole_m`!=0 т.е. заведома зная, что значение 0 точно отсутствет???

И вот еще ПМА пишет: More than one INDEX key was created for column `k_id`после создания 3-х составных индексов, это связанно с тем, что фильтрация может быть только по одному фильтру, а может быть по нескольким одновременно и во всех присутствует `k_id` является ли это серьезной ошибкой?

вынес `k_id` в отдельный индекс решил использовать в запросе связку USE INDEX (`index_kid`,`index_m`)

Спустя 50 минут, 46 секунд (15.11.2011 - 03:20) dadli написал(а):
A.B.C.
оффтоп
Свернутый текст
вот почему спрашиваю, мне бил недавно вопрос: как посчитат время виполненя скрипта: но сами верни ответ не принимал, но такои варянт, очен банални, по моему, ничего, са идиот:

$a = microtime(true);
// здес скрипт какоита
$b =  microtime(true);

echo $b - $a;



я сеичас проверял примерно так: на тваиом скрипте, вместо: сам скрипт, поставил такои цикл:

$i = 0;
while ($i < 50000000) {
	$i = $i + 1;
}


и потом проверял еше так

$a = microtime(true);
$i = 0;
while ($i < 50000000) {
	$i = $i + 1;
}
$b =  microtime(true);

echo $b - $a;


резултати били такои: иногда твои варянт бил болше иногда "мои" (он не совсем мои), они били где то 3.278.... секунд
не знаю, может стоит такои примитивни варянт исползоват, резултат почти одно те же.


Спустя 24 минуты, 8 секунд (15.11.2011 - 03:44) GET написал(а):
dadli

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

Спустя 7 часов, 59 минут, 56 секунд (15.11.2011 - 11:44) imbalance_hero написал(а):
A.B.C.
Каждый индекс - лишнее место физическое на сервере + замедляет операции insert,update,delete, но ускоряет select.

Если хочешь ещё ускорить, можешь тип Таблицы сменить на InnoDB, это ещё в 2, или 2,5 раза быстрее будет. Но это так же замедлит остальные операции, но ускорит select.

Ну а остальное я так и не понял, что ты там мутил smile.gif


_____________
Не тот велик, кто не падал, а тот кто падал и поднимался.
Быстрый ответ:
Powered by dgreen

 Графические смайлики |  Показывать подпись
Здесь расположена полная версия этой страницы.
Invision Power Board © 2001-2025 Invision Power Services, Inc.