Как ускорить запрос к базе данных MySQL?

Question

[jemanov]

$sql = "SELECT * FROM `1otsev` WHERE `out_keys` IS NULL";
$res = mysql_query($sql, $db);
while($row = mysql_fetch_assoc($res))
	{
	//помечаем, что взято:
	$sql = "UPDATE `1otsev` SET `out_keys`=1 WHERE `id`='".$row['id']."'";
	mysql_query($sql, $db);	
	
	//добавляем ключи:
	$mass = explode(";", $row['keys']);
	foreach($mass as $key=>$val)
		{
		//проверка есть ли:
		$sql = "SELECT * FROM `keys` WHERE `frase`='".mysql_real_escape_string($val)."'";
		$resu = mysql_query($sql, $db);
		if(mysql_num_rows($resu) == 0)
			{
			$sql = "INSERT INTO `keys` (`frase`) VALUES ('".mysql_real_escape_string($val)."')";
			mysql_query($sql, $db);
			}
		}
	}

Алгоритм:
1. берем строку из базы
2. помечаем в базе строку которую взяли
3. разделяем значение одного из столбцов этой строки на массив (30-40 элементов)
4. проходимся по массиву, если его значений нету в другой таблице то записываем.

Проблема:
В исходной таблице `1otsev`: 71476 строк.
В среднем за час, представленный выше код обрабатывает 130 строк из нее.
Получается что все будет готово только через 23 дня.
Хотелось бы быстрее.

Comments

[landergate]

> В среднем за час, представленный выше код обрабатывает 130 строк из нее.

Это не ошибка?
Это слишком мало.

[jemanov]

landergate: нет, это именно так.

Answer 1

[Adamos]

Видимо, таким образом, медленно и печально, идет процесс нормализации базы - превращения этого чудища со столбцом на 30-40 элементов в отдельную таблицу фраз и таблицу связей между 1otsev и фразами.
Но вы маетесь ерундой, дергая таблицы по одной и делая запросы в цикле.

1. Добавьте UNIQUE индекс на `frase`, и используйте INSERT IGNORE, после этого база сама не даст вставить дубль.

, как совершенно справедливо посоветовал Rsa97.
2. Прочитайте ВСЮ таблицу 1otsev и составьте массив фраз, которые должны быть в таблице key, в самом скрипте - не мучайте зря базу, уникальные значения и РНР отличать умеет.
3. А уже потом этот массив ОДНИМ запросом запихните в таблицу key.
4. Ну, и после этого можно будет прочесть таблицу key, спокойно пройтись еще раз по 1otsev, для каждой строчки составить данные для таблицы соответствий и опять-таки ОДНИМ запросом записать их. И еще одним - удалить этот дурацкий столбец с перечислением.
Вряд ли это займет больше часа...

Answer 2

[ThunderCat]

$sql = "SELECT * FROM `1otsev` WHERE `out_keys` IS NULL";

IS NULL затратная операция, замените все NULL на 0, станет работать быстрее гораздо.
запустите из пхпмайадмин 1 раз:

UPDATE  `1otsev` set `out_keys` = 0 WHERE `out_keys` IS NULL

а тут...

$sql = "SELECT * FROM `keys` WHERE `frase`='".mysql_real_escape_string($val)."'";

Пропустим момент того что код был впервые использован еще при царе Горохе, а благополучно забыт еще при Ельцине, относительно таблицы - индекс на frase есть? Вообще везде где вы делаете выборку - нужно индексы.
Вообще неплохо было бы замерить время которое уходит на запрос, а так же план каждого запроса посмотреть, уж очень страшно у вас получается, должно максимум пару минут отрабатывать.

Comments

[jemanov]

Индекса нет, как это сделать? Именно на таблицу куда я записываю его ставить? Или откуда беру? что нажимать в phpMyAdmin?

[ThunderCat]

jemanov: индекс ставится на ПОЛЕ ТАБЛИЦЫ, в структуре таблицы напротив каждого поля есть кнопочка "индекс". понажимайте везде где поле будет использоваться в запросе

[jemanov]

ThunderCat: #1170 - BLOB/TEXT column 'frase' used in key specification without a key length

[ThunderCat]

jemanov: это значит что у вас тип поля - text или blob - это значит что там может хранится БОЛЬШОЙ кусок текста, если у вас там короткие фразы (до 200-240 символов) поменяйте тип поля на varchar и сделайте индекс.

Answer 3

[Rsa97]

1. Добавьте UNIQUE индекс на `frase`, и используйте INSERT IGNORE, после этого база сама не даст вставить дубль.
2. Не используйте mysql. Используйте mysqli или PDO с подготовленными запросами. Это позволит серверу не компилировать запрос при каждом вызове.
3. Поймайте того, кто придумал запихивать в одну колонку 30-40 значений и поменяйте ему местами руки и ноги.