سوال از اساتید php و mysql

[r.miri19]

سلام
من می خواهم توی نرم افزارم(اندروید) به وسیله php از دیتابیس mysql کوئری بگیریم به این شکل:
که x و y گوشی رو به php بفرستم و بعد توسط php از جدولی به نام location با x و y های دیگر کاربران مقایسه کنم و افرادی که کمتر از 10 کیلومتر بودن رو نشون بده.
می خواستم ببینم کوئری چطور بگیرم که به سرور فشار وارد نشود.احتمالا 10,000 کاربر داشته باشم.
من از کد زیر توی php استفاده کردم .

$result = mysql_query("SELECT id,((ACOS(SIN(location_lat * PI() / 180) * SIN($lat * PI() / 180) + COS(location_lat * PI() / 180) * COS($lat * PI() / 180) * COS((location_long - $long) * PI() / 180)) * 180 / PI()) * 60 * 1.1515 * 1.609344) as distance
FROM location
GROUP BY id
HAVING distance <= 10000

لطفا بهترین راه حل رو بگید.ممنونم

[H:Shojaei]

اگر درخواست ها همزمان نباشه ده هزار رکورد اصلا چیزی نیست...
شرایط ارسال کوئری رو هم بگید مثلا این که چطور توسط چند نفر در چه زمانهایی اجرا میشه؟ تا بشه راهنمایی کرد...

[arenaw]

یه راه که به نظرم میرسه، اینه که 10 کیلومتر رو دقیق اندازه نگیرید. به جای اینکه mySql رو درگیر سینوس کسینوس بکنید، میتونید شخص درخواست کننده رو مرکز یه مربع با ضلع ۱۰ کیلومتر بگیرید و افراد داخل اون بخش رو جدا کنید.
درسته دقیق نیست (افرادی که کنج مربع هستند فاصلشون از ۱۰ کیلومتر یکم بیشتره) ولی خب خیلی سبک تره. تازه نباید انتظار داشته باشید که لوکیشنی که کاربر به شما میفرسته، خیلی دقیق باشه.
اگر هم فک میکنید خیلی مهمه که دقیقا ۱۰ کیلومتر باشه، میتونید اون دیتا رو بفرستید به کاربر و سمت کلاینت اضافه هاشو کنار بذارید.

[r.miri19]

یه راه که به نظرم میرسه، اینه که 10 کیلومتر رو دقیق اندازه نگیرید. به جای اینکه mySql رو درگیر سینوس کسینوس بکنید، میتونید شخص درخواست کننده رو مرکز یه مربع با ضلع ۱۰ کیلومتر بگیرید و افراد داخل اون بخش رو جدا کنید.
درسته دقیق نیست (افرادی که کنج مربع هستند فاصلشون از ۱۰ کیلومتر یکم بیشتره) ولی خب خیلی سبک تره. تازه نباید انتظار داشته باشید که لوکیشنی که کاربر به شما میفرسته، خیلی دقیق باشه.
اگر هم فک میکنید خیلی مهمه که دقیقا ۱۰ کیلومتر باشه، میتونید اون دیتا رو بفرستید به کاربر و سمت کلاینت اضافه هاشو کنار بذارید.

نه داداش زیاد مهم نیست 10 کیلومتر باشه حوالی 10 و12 و 15 باشه مهم نیست ، می شه کدشو واسم بفرستی، مربع رو می گم در sql
اما آخرش distance رو بم بده که توی نرم افزار اندروید نشون بدم که مثلا فلان کاربر 5 کیلومتر فاصله داره.ممنونم

[r.miri19]

اگر درخواست ها همزمان نباشه ده هزار رکورد اصلا چیزی نیست...
شرایط ارسال کوئری رو هم بگید مثلا این که چطور توسط چند نفر در چه زمانهایی اجرا میشه؟ تا بشه راهنمایی کرد...

ممکنه 1000 درخواست در ثانیه باشه ، آخه کاربرا همش می خوان افراد اطراف خودشونو پیدا کنند.

[arenaw]

این کوئری

SELECT
id,
location_lat,
location_long
FROM
location
WHERE
location_lat BETWEEN $latMin AND $latMax
AND location_long BETWEEN $longMin AND $longMax;


اینم متغیرهایی که داخلش هست:


$latMin = $lat - 5000;
$latMax = $lat + 5000;
$longMin = $long + 5000;
$longMax = $long + 5000;


132502
اینم تفاوتشون. نقطه قرمز کاربری که درخواست رو فرستاده هستش. دایره طوسی نتیجه کوئری شما و نسبتا سنگین
مربع طوسی نتیجه کوئری بنده و سبک، هرچند که اون نقطه مشکی بالا سمت چپ فاصلش کمی بیشتر از 10 کیلومتر هستش.
امیدوارم متوجه شده باشی

EDIT:
-------------------------------
البته برای اینکه
distance رو داشته باشی، باید خروجی رو بفرستی به برنامه. اونجا هم lat و long کاربر و lat و long کاربرایی که اطرافش هستنو داری. حالا میتونی همونجا کلاینت ساید حساب کنی که هرکدوم چند متر فاصله دارن.

[jafaripur]

-------------------------------------------

[jafaripur]

این کد که نوشتی تو سرورهای شلوغ ریسورس زیادی میطلبه. می تونی از جاش از مربع و ایندکس گذاری روی فیلد های latitude, longitude استفاده کنی:
SELECT * FROM locations
WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);

0.0253 radians = 100 miles
می تونی بیشتر هم optimize کنی:
ALTER TABLE locations
ADD lat_floor INT NOT NULL DEFAULT 0,
ADD lon_floor INT NOT NULL DEFAULT 0,
ADD KEY(lat_floor, lon_floor);

UPDATE locations SET lat_floor = FLOOR(lat), lon_floor = FLOOR(lon);
بعد با یک کوری چهار گوش رو پیدا می کنی:
SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
بعد پیدا کردن چهار گوشه که می خوای توش جستجو کنی و استفاده از داده های موجود کوئری اصلی رو می زنی:
SELECT * FROM locations
WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);
lati, long هایی که تو شرط IN استفاده شده با همون کوئری قبل که Floor و Ceil کردیم در آورده شده.
در آخر هم می تونی روش اصلی که performance نداره و روش جدید رو با هم یکی کنی:

SELECT * FROM locations
WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
AND 3979 * ACOS(
COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
+ SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

خیلی واضح هستش فکر نکنم نیاز به توضیح خاصی باشه.

-حواست باشه engine دیتابیست MyISAM نباشه.
-می تونی از Sphinx استفاده کنی که جستجوهای جغرافیایی رو با سرعت بالا انجام میده (ایندکس گذاری است نه دیتابیس)
-از Elasticsearch استفاده کن از همشون بهتره و چون توضیع شده هستش با شلوغ شدن سرور می تونی سرورهای بیشتری رو اضافه کنی تا بار روی یه سرور نباشه.

[r.miri19]

باتشکر از آقایون arenaw و jafaripur


این روش هایی که گفتید خیلی عالی هست اما من یک مشکل دارم. چطور می تونم کوئری های بدست آمده رو از نزدیک ترین به بزرگ ترین بچینم.آخه من با limit هر دفعه 10 تا رو به وسیله php به JSON تبدیل می کنم و بعد به وسیله اندروید دانلود می کنم و تجزیه تحلیل می کنم.
ممکنه با این روش هایی که گفتید مثلا در فاصله 100 کبلومتری 2000 نفر پبدا بشه بعد گرفتن و چیدن در اندروید خیلی به موبایل ها فشار می آورد.به خاطر این من هر دفعه 10 تا می گیرم .
آیا شما راه حلی خوبی برای چیدن نیز دارید؟

[jafaripur]

این رو تو Select بنویس نه در شرط:
(3979 * ACOS(
COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
+ SIN(RADIANS(lat)) * SIN(RADIANS(38.03))) AS `distance`
توی شرط هم بنویس:
HAVING BY `distance` <= 100
توی مرتب سازی هم از distance استفاده کن:
ORDER BY `distance`

[r.miri19]

این رو تو Select بنویس نه در شرط:
(3979 * ACOS(
COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
+ SIN(RADIANS(lat)) * SIN(RADIANS(38.03))) AS `distance`
توی شرط هم بنویس:
HAVING BY `distance` <= 100
توی مرتب سازی هم از distance استفاده کن:
ORDER BY `distance`

این کد که شما گذاشتید، تقریبا همان فشار رو به سرور نمی آره؟مثل کد پست اول خودم هست

[jafaripur]

این کد که شما گذاشتید، تقریبا همان فشار رو به سرور نمی آره؟مثل کد پست اول خودم هست

نه، من کمی تغییرش دادم. نوشته قبلی رو خوب بخودن کدها رو هم بررسی کن. اولین شرط اینه که با ایندکس گذاری خوب جواب می ده:
lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
شرط وقت گیر در آخر هستش وقتی کلی دیتا فیلتر شده.

قبلاً هم گفتم، برای همچین سیستم هایی خوب نیست از RDBMS ها استفاده کنی. اگر بخوای حتماً از MySQL استفاده کنی، همراهش می تونی از Sphinx هم استفاده کنی یا سیستم رو انتقال بدی به Elasticsearch و یا ...

[r.miri19]

نه، من کمی تغییرش دادم. نوشته قبلی رو خوب بخودن کدها رو هم بررسی کن. اولین شرط اینه که با ایندکس گذاری خوب جواب می ده:
lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
شرط وقت گیر در آخر هستش وقتی کلی دیتا فیلتر شده.

قبلاً هم گفتم، برای همچین سیستم هایی خوب نیست از RDBMS ها استفاده کنی. اگر بخوای حتماً از MySQL استفاده کنی، همراهش می تونی از Sphinx هم استفاده کنی یا سیستم رو انتقال بدی به Elasticsearch و یا ...

من کامل گیج شدم.
آقای jafaripur میشه کدها رو کلان یه جا بزارید توی یک پست؟من php زیاد بلد نیستم .

[r.miri19]

این کد که نوشتی تو سرورهای شلوغ ریسورس زیادی میطلبه. می تونی از جاش از مربع و ایندکس گذاری روی فیلد های latitude, longitude استفاده کنی:
SELECT * FROM locations
WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253);

0.0253 radians = 100 miles
می تونی بیشتر هم optimize کنی:
ALTER TABLE locations
ADD lat_floor INT NOT NULL DEFAULT 0,
ADD lon_floor INT NOT NULL DEFAULT 0,
ADD KEY(lat_floor, lon_floor);

UPDATE locations SET lat_floor = FLOOR(lat), lon_floor = FLOOR(lon);
بعد با یک کوری چهار گوش رو پیدا می کنی:
SELECT FLOOR( 38.03 - DEGREES(0.0253)) AS lat_lb,
CEILING( 38.03 + DEGREES(0.0253)) AS lat_ub,
FLOOR(-78.48 - DEGREES(0.0253)) AS lon_lb,
CEILING(-78.48 + DEGREES(0.0253)) AS lon_ub;
بعد پیدا کردن چهار گوشه که می خوای توش جستجو کنی و استفاده از داده های موجود کوئری اصلی رو می زنی:
SELECT * FROM locations
WHERE lat BETWEEN 38.03 - DEGREES(0.0253) AND 38.03 + DEGREES(0.0253)
AND lon BETWEEN −78.48 - DEGREES(0.0253) AND −78.48 + DEGREES(0.0253)
AND lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77);
lati, long هایی که تو شرط IN استفاده شده با همون کوئری قبل که Floor و Ceil کردیم در آورده شده.
در آخر هم می تونی روش اصلی که performance نداره و روش جدید رو با هم یکی کنی:

SELECT * FROM locations
WHERE lat_floor IN(36,37,38,39,40) AND lon_floor IN(-80,-79,-78,-77)
AND 3979 * ACOS(
COS(RADIANS(lat)) * COS(RADIANS(38.03)) * COS(RADIANS(lon) - RADIANS(-78.48))
+ SIN(RADIANS(lat)) * SIN(RADIANS(38.03))
) <= 100;

خیلی واضح هستش فکر نکنم نیاز به توضیح خاصی باشه.

-حواست باشه engine دیتابیست MyISAM نباشه.
-می تونی از Sphinx استفاده کنی که جستجوهای جغرافیایی رو با سرعت بالا انجام میده (ایندکس گذاری است نه دیتابیس)
-از Elasticsearch استفاده کن از همشون بهتره و چون توضیع شده هستش با شلوغ شدن سرور می تونی سرورهای بیشتری رو اضافه کنی تا بار روی یه سرور نباشه.

این " حواست باشه engine دیتابیست MyISAM نباشه. " پس چی باشه؟

[jafaripur]

این " حواست باشه engine دیتابیست MyISAM نباشه. " پس چی باشه؟

MyISAM itself just doesn’t work well for large, high-traffic applications, for all the usual reasons: data corruption, table-level locking, and so on

InnoDB رو انتخاب کن. گفتم که می تونی همراه با MySQL از Sphinx استفاده کنی.