Скорост на sql заявка. Оптимизиране

[azsym]

Имам една не толкова сложна колкото дълга заявка.
Търсенето е от 2 таблици и по-голямата част е в една с 1.6 милиона записа.

SELECT name, sex FROM people where active = 1 and zip in (SELECT zip FROM zips WHERE people >= 1 AND x >= (SELECT x-(0.009*$dist) FROM zips WHERE zip = '$zip') AND x <= (SELECT x+(0.009*$dist) FROM zips WHERE zip = '$zip') AND y >= (SELECT y-(0.009*$dist) FROM zips WHERE zip = '$zip') AND y <= (SELECT y+(0.009*$dist) FROM zips WHERE zip = '$zip';

най-важната част е проверка на едно число от голямата таблица дали е в рамките на други 2 числа, който са пак от там но с добавка. Дано е ясно.

Та въпроса е дали да разделя заявката на две като проверката на числата я правя в php.

В момента отнема около 5 сек за изпълнение.

 

[BornToDrink]

Re: Скорост на sql заявка. Оптимизиране

Имаш ли индекси на колоните, по които селектваш и в двете таблици?

За пример:
select id from table where title = 'some string';
Отнема 4-5 секунди от таблица без индекс и 0.005 от таблица с индекс.

 

[azsym]

Re: Скорост на sql заявка. Оптимизиране

Имаш ли индекси на колоните, по които селектваш и в двете таблици?

За пример:
select id from table where title = 'some string';
Отнема 4-5 секунди от таблица без индекс и 0.005 от таблица с индекс.

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

 

[bg-info]

Re: Скорост на sql заявка. Оптимизиране

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

От phpMyAdmin се вижда всичко.

 

[human]

Re: Скорост на sql заявка. Оптимизиране

В pma дай на структура на таблиците, които ползваш и в последните полета някъде има иконка за създаване на индекс. Но НЕ слагай на всички полета индекси, а само на тези, които ползваш най-често, защото при грешно сложени индекси става обратен ефект и започва да бави повече.

 

[nikoladd]

Re: Скорост на sql заявка. Оптимизиране

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

 

[azsym]

Re: Скорост на sql заявка. Оптимизиране

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

мислих го но това не ми се вижда възможно за 1.6 мил точки и 12 вида дистанции.

 

[BornToDrink]

Re: Скорост на sql заявка. Оптимизиране

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

use myDatabase;
show create table myTable;
//тук накрая трябва да ти извади нещо от сорта:
KEY `myColumn` (`myColumn`),

Ако нямаш, добавяш си индекс така:
alter table myTable add index (`myColumn`);

Виж в нета за повече инфо относно индексите, понеже има разни опции.

 

[bubatalazi]

Re: Скорост на sql заявка. Оптимизиране

Може ли да обясниш все пак каква е целта на цялото упражнение в първата вложена заявка и структурата на таблиците zip и person (или поне полетата в заявката). Искаш да намериш всички зип кодове, които са на дадена дистанция от даден зип?

 

[nikoladd]

Re: Скорост на sql заявка. Оптимизиране

Пробвай да изкараш координатните интервали първо

$zip_row = get_from_sql_function("select x,y from zips where zip = '$zip'");
$zip_x = $zip_row['x'];
$zip_y = $zip_row['y'];
$zip_x_start = $zip_x-(0.009*$dist);
$zip_x_end = $zip_x+(0.009*$dist);
$zip_y_start = $zip_y-(0.009*$dist);
$zip_y_end = $zip_y+(0.009*$dist);

И после това би трябвало да е по-бързо:

SELECT name, sex FROM people 
	where active = 1 
		and zip in (
			SELECT zip FROM zips 
				WHERE people >= 1 
					AND x BETWEEN $zip_x_start AND $zip_x_end
					AND y BETWEEN $zip_y_start AND $zip_y_end
					);

индекси трябва да имаш по:
people.zip
zips.x
zips.y

и за първата заявка:
zips.zip

П.С. Това последното може да се ускори с няколко порадъка чрез поддръжка на междинни интервали за координатите с олекотен индекс. Това обаче е извън лиценза на безплатната версия 😉

 

[azsym]

Re: Скорост на sql заявка. Оптимизиране

use myDatabase;
show create table myTable;
//тук накрая трябва да ти извади нещо от сорта:
KEY `myColumn` (`myColumn`),

Ако нямаш, добавяш си индекс така:
alter table myTable add index (`myColumn`);

Виж в нета за повече инфо относно индексите, понеже има разни опции.

мерси много.
така стана от 10 сек на 0.2 сек. За сега е добре.

@никола: това ще го пробвам ако в бъдеще почне да се бави при натоварване. По принцип за това дето го искам има пространствени функции (постгре, оракъл и др), но за mysql точно това го няма.

@бубата: да, това е.

 

[mlazarov]

Re: Скорост на sql заявка. Оптимизиране

За такива неща има специални бази, които при това са безплатни🙂

 

[azsym]

Re: Скорост на sql заявка. Оптимизиране

За такива неща има специални бази, които при това са безплатни🙂

🙂
колко от тия са на всеки споделен хостинг.
и кои са.

 

[Sag]

Re: Скорост на sql заявка. Оптимизиране

Съветите на Никола са златни в случая, аз бих ги приложил дори и да не получа реално ускорение – все пак ще знаеш, че алгоритъма ти е добър.