دلیل پایین بودن سرعت سیستم در هنگام استفاده از این کوئری چیه؟

[i-php-i]

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


SELECT a.*,(SELECT COUNT(*) FROM comment WHERE art_id = a.id ) as countFROM article AS a WHERE type='paper'ORDER BY a.id DESCLIMIT 10
البته باید از دوستان عزیز MMSHFE (http://barnamenevis.org/member.php?55504-MMSHFE) و Mahdi.Spirit (http://barnamenevis.org/member.php?150376-Mahdi.Spirit) که قبلا در این زمینه کمکم کردن تشکر کنم.

[امیـرحسین]

اینو امتحان کنید.
SELECT article.*, COUNT(`comment`.id) AS comments FROM article
LEFT JOIN `comment` ON comment.art_id=article.id
WHERE article.type='paper'
GROUP BY article.id
ORDER article.id DESC
LIMIT 10
من فرض کردم که جدول comment ستونی با نام id داره.

از Sub-Query پرهیز بفرمایید که سرعت رو پایین میاره.

[i-php-i]

اینو امتحان کنید.
SELECT article.*, COUNT(`comment`.id) AS comments FROM article
LEFT JOIN `comment` ON comment.art_id=article.id
WHERE article.type='paper'
GROUP BY article.id
ORDER article.id DESC
LIMIT 10
من فرض کردم که جدول comment ستونی با نام id داره.

از Sub-Query پرهیز بفرمایید که سرعت رو پایین میاره.
این روش رو قبلا تست کردم، بخاطر استفاده از GROUP BY سرعت اجرا خیلی کمتر از سرعت کنونی می شه.

راهی نیست که GROUP BY رو حذف کنید؟

[امیـرحسین]

GROUP BY باید باشه تا COUNT درست عمل کنه. این مدل حالت استانداردیه و نباید توی سرعت تاثیر داشته باشه. کوئری رو توی PhpMyAdmin یا Console اجرا کنید تا سرعت اجراش رو ببینید. شاید کندی به جای دیگه‌ی مربوط باشه.
در هر حال INDEXهای جداول رو هم چک کنید که صحیح و کارا INDEX انجام شده باشه.

[i-php-i]

GROUP BY باید باشه تا COUNT درست عمل کنه. این مدل حالت استانداردیه و نباید توی سرعت تاثیر داشته باشه. کوئری رو توی PhpMyAdmin یا Console اجرا کنید تا سرعت اجراش رو ببینید. شاید کندی به جای دیگه‌ی مربوط باشه.
در هر حال INDEXهای جداول رو هم چک کنید که صحیح و کارا INDEX انجام شده باشه.
کوئری درست بود ولی سرعت اجرا خیلی پایین بود.

[Mahdi.Spirit]

اگر ممكنه كاملتر توضيح بدين؟
سرعت رو چقدر پايين مياره ؟ اگه ميشه تكه تكه كد ها رو امتحان كنيد و زمان هر قسمت رو بزاريد
حجم مطالبتون (تعداد ركوردها)چقدره ؟ با join و group by ‌هم زمانش رو بزاريد
اگر هم شد خود محتويات ديتابيس رو اينجا آپلود كنيد كه تست كنيم



از Sub-Query پرهیز بفرمایید که سرعت رو پایین میاره.

ميشه دليلش رو بگيد؟ براي من كه برعكسه! من سرعتم با group by كمتر ميشه ، يعني با اين group by و join‌ سرعتم 10 برابر كمتر از كد پست اول شد ،‌ كلا هم 5تا ركورد براي جدول article‌ و 11تا ركورد براي comment دارم
ضمنا مستقيم توي PhpMyAdmin تست ميكنم

[Mahdi.Spirit]

من ديتابيس خودم رو ميزارم تست كنيد:

[AMIBCT]

با اين تعداد اطلاعات كه به طور كلي نبايد مشكلي باشه

در اين مورد مشكل شما جاي ديگه‌اي هست، مثلا سرعت كم ارتباطي يا سرعت كم سرور

ولي به طور كلي شما روي فيلدهايي كه مقايسه انجام مي‌ديد بايد INDEX قرار بديد

مثلا اينجا فيلدهاي art_id و type بايد INDEX داشت باشند كه ندارند

[Mahdi.Spirit]

با اين تعداد اطلاعات كه به طور كلي نبايد مشكلي باشه
در اين مورد مشكل شما جاي ديگه‌اي هست، مثلا سرعت كم ارتباطي يا سرعت كم سرور
ولي به طور كلي شما روي فيلدهايي كه مقايسه انجام مي‌ديد بايد INDEX قرار بديد
مثلا اينجا فيلدهاي art_id و type بايد INDEX داشت باشند كه ندارند
ممنون ، مشكل سر همون index بود
البته چون رو local تست ميكردم ربطي به سرعت ارتباطي و سرعت سرور نداشت

[i-php-i]

با اين تعداد اطلاعات كه به طور كلي نبايد مشكلي باشه

در اين مورد مشكل شما جاي ديگه‌اي هست، مثلا سرعت كم ارتباطي يا سرعت كم سرور

ولي به طور كلي شما روي فيلدهايي كه مقايسه انجام مي‌ديد بايد INDEX قرار بديد

مثلا اينجا فيلدهاي art_id و type بايد INDEX داشت باشند كه ندارند
من توی دیتابیس، ستون مربوط به ای دی هر مقاله و هر نظر رو بصورت کلید اولیه ایجاد کردم.

آیا باید برای اینها هم index ایجاد کرد؟

سوال دیگه اینکه محتویات ستون type ممکنه تکراری باشن. آیا برای این ستون هم باید ایندکس تعریف کرد؟

[AMIBCT]

ستون كليد به صورت خودكار Index مي‌سازه و نيازي به ساخت دستي Index نيست

ساخت Index براي فيلدي مثل type كه از نوع متني هست خيلي لازمه
هر چند بهتر بود از نوع Enum يا Int تعريف بشه و Index هم تعريف بشه تا باز كارايي افزايش پيدا كنه

[i-php-i]

ستون كليد به صورت خودكار Index مي‌سازه و نيازي به ساخت دستي Index نيست

ساخت Index براي فيلدي مثل type كه از نوع متني هست خيلي لازمه
هر چند بهتر بود از نوع Enum يا Int تعريف بشه و Index هم تعريف بشه تا باز كارايي افزايش پيدا كنه
برای ایجاد ایندکس، تا اونجا که می دونم محتویات یه ستون نباید تکراری باشن.

این مطلب درسته؟

اگر جواب مثبت هست، چطور می گید که برای ستون type ایندکس تعریف کنم؟

[Mahdi.Spirit]

برای ایجاد ایندکس، تا اونجا که می دونم محتویات یه ستون نباید تکراری باشن.

این مطلب درسته؟

اگر جواب مثبت هست، چطور می گید که برای ستون type ایندکس تعریف کنم؟

موقع تعریف میتونید نوعش رو تعیین كنید كه یونیك باشه یا تكراری هم قبول كنه
اگر كلید نباشه ،بصورت پیش فرض تكراری هم قبول میكنه

[i-php-i]

موقع تعریف میتونید نوعش رو تعیین كنید كه یونیك باشه یا تكراری هم قبول كنه
اگر كلید نباشه ،بصورت پیش فرض تكراری هم قبول میكنه
تقریبا 10000 ردیف توی جدول مقالاتم هست.

شما توی پستتون گفتید که ردیفهای کمی توی جدولتون هست. آیا استفاده از index در ستونها و group by توی همچین جدولی، روی کمتر شدن سرعت تاثیری نداره؟

[AMIBCT]

من قاطی کردم

منظور من از تعداد کم ردیف‌ها، اون نمونه‌ای بود که دوست گرامی Mahdi.Spirit فرستاده بودن

بهینه‌سازی در تعداد کم ردیف نیازی نیست و به چشم هم نمیاد
ولی وقتی تعداد ردیف‌ها زیاد بشه، مشکلات مشخص می‌شه

Index کردن اطلاعات در پایگاه داده باعث می‌شه جدول‌های اضافی برای اطلاعات ایجاد بشه
پس از ایجاد Index، به جای پیمایش تمامی ردیف‌ها برای جستجو،
درخت‌های دودویی و درخت‌های بی سرعت جستجو رو افزایش می‌دن
این ساختارهای داده تعداد مقایسه رو از n مقایسه برای n ردیف به Log n کاهش می‌دن
در نتیجه سرعت مقایسه و جستجو به صورت تصاعدی افزایش پیدا می‌کنه

آگاهی از ساختار داخلی پایگاه داده و نوشتن بهینه‌ی SQL هم اصل مهم بعدی هست
برای گرفتن یک مجموعه از اطلاعات شاید ده‌ها نوع SQL بشه نوشت
مهم اینه که بدونیم کدوم پرس‌وجو کمترین فشار رو به پایگاه داده میاره و حافظه‌ی کمتری مصرف می‌کنه

[i-php-i]

من قاطی کردم

منظور من از تعداد کم ردیف‌ها، اون نمونه‌ای بود که دوست گرامی Mahdi.Spirit فرستاده بودن

بهینه‌سازی در تعداد کم ردیف نیازی نیست و به چشم هم نمیاد
ولی وقتی تعداد ردیف‌ها زیاد بشه، مشکلات مشخص می‌شه

Index کردن اطلاعات در پایگاه داده باعث می‌شه جدول‌های اضافی برای اطلاعات ایجاد بشه
پس از ایجاد Index، به جای پیمایش تمامی ردیف‌ها برای جستجو،
درخت‌های دودویی و درخت‌های بی سرعت جستجو رو افزایش می‌دن
این ساختارهای داده تعداد مقایسه رو از n مقایسه برای n ردیف به Log n کاهش می‌دن
در نتیجه سرعت مقایسه و جستجو به صورت تصاعدی افزایش پیدا می‌کنه

آگاهی از ساختار داخلی پایگاه داده و نوشتن بهینه‌ی SQL هم اصل مهم بعدی هست
برای گرفتن یک مجموعه از اطلاعات شاید ده‌ها نوع SQL بشه نوشت
مهم اینه که بدونیم کدوم پرس‌وجو کمترین فشار رو به پایگاه داده میاره و حافظه‌ی کمتری مصرف می‌کنه
کوئری که ارسال می کنم به شکل زیر هست. توی این پرس و جو ستونهای ای دی در دو جدول کلید اولیه هستند. تنها موردی که باقی می مونه که باید ایندکس بشه، textid هست. آیا این نوع از کوئری استاندارد هست؟

$sql= "SELECT *,(SELECT COUNT(*) FROM `comments` WHERE (`textid`='$_GET[id]')) AS `count` FROM `ad_sent` WHERE (`id`='$_GET[id]')";

[Mahdi.Spirit]

تقریبا 10000 ردیف توی جدول مقالاتم هست.
شما توی پستتون گفتید که ردیفهای کمی توی جدولتون هست. آیا استفاده از index در ستونها و group by توی همچین جدولی، روی کمتر شدن سرعت تاثیری نداره؟
من گفتم وقتي به art_id‌ ايندكس اضافه كردم با اينكه تعداد رديف ها كم بود ولي دستوري كه توش از group by استفاده كرده بودم سريعتر از زماني كه ايندكس اضافه نشده بود عمل كرد ،‌ خب وقتي تعداد رديفها بيشتر بشه اين زمان محسوس تره ديگه
الان شما اگر همون دستور ( با group by و join) رو با ايندكس و بدون ايندكس تست ميكني (براي همون 10000 رديف ) زمانش بهتر نميشه ؟

[i-php-i]

من گفتم وقتي به art_id‌ ايندكس اضافه كردم با اينكه تعداد رديف ها كم بود ولي دستوري كه توش از group by استفاده كرده بودم سريعتر از زماني كه ايندكس اضافه نشده بود عمل كرد ،‌ خب وقتي تعداد رديفها بيشتر بشه اين زمان محسوس تره ديگه
الان شما اگر همون دستور ( با group by و join) رو با ايندكس و بدون ايندكس تست ميكني (براي همون 10000 رديف ) زمانش بهتر نميشه ؟
این مطلبی که می گید برای زمانی که از sql query استفاده می کنید هم صادق هست؟

[i-php-i]

من قاطی کردم

منظور من از تعداد کم ردیف‌ها، اون نمونه‌ای بود که دوست گرامی Mahdi.Spirit فرستاده بودن

بهینه‌سازی در تعداد کم ردیف نیازی نیست و به چشم هم نمیاد
ولی وقتی تعداد ردیف‌ها زیاد بشه، مشکلات مشخص می‌شه

Index کردن اطلاعات در پایگاه داده باعث می‌شه جدول‌های اضافی برای اطلاعات ایجاد بشه
پس از ایجاد Index، به جای پیمایش تمامی ردیف‌ها برای جستجو،
درخت‌های دودویی و درخت‌های بی سرعت جستجو رو افزایش می‌دن
این ساختارهای داده تعداد مقایسه رو از n مقایسه برای n ردیف به Log n کاهش می‌دن
در نتیجه سرعت مقایسه و جستجو به صورت تصاعدی افزایش پیدا می‌کنه

آگاهی از ساختار داخلی پایگاه داده و نوشتن بهینه‌ی SQL هم اصل مهم بعدی هست
برای گرفتن یک مجموعه از اطلاعات شاید ده‌ها نوع SQL بشه نوشت
مهم اینه که بدونیم کدوم پرس‌وجو کمترین فشار رو به پایگاه داده میاره و حافظه‌ی کمتری مصرف می‌کنه
الان من یه جدول ایجاد کردم و تعدادی از ستونهاش رو ایندکس کردم. وقتی با استفاده از phpmyadmin جدول رو نگاه می کنم، در مورد اینکه ستونها ایندکس شدن هیچ اطلاعاتی نمایش نمی ده. حتی نمی شه بعد از ایجاد جدول، بعضی از ستونها رو ایندکس کرد یا اینکه ایندکس رو از بعضی ستونها حذف کرد.

جدولهای اضافه ای هم که گفتید هنگام ایندکس کردن یه ستون ایجاد می شن رو هم نتونستم پیدا کنم.


این چه دلیلی داره؟

[AMIBCT]

Indexهاي ايجاد شده رو وقتي كه جدول رو باز كنيد
در بخش پايين و سمت چپ صفحه قابل مشاهده هست

جدول ايجاد شده براي Index ها هم داخلي هست و در فهرست به شما نشون داده نمي‌شه

در مورد پرس‌وجويي كه نوشته بوديد

در اين موارد كه تنها يك رديف مورد نياز شما هست بهتره كه در انتهاي پرس‌وجو از عبارت LIMIT 1 استفاده كنيد
در اين حالت وقتي كه اولين ركورد برگردونده بشه، تحت هر شرايطي، پرس‌وجو متوقف مي‌شه و در كمترين زمان ممكن نتايج برگشت داده مي‌شن
علاوه بر ايجاد Index براي فيلد textid سعي كنيد از Query در Query استفاده نكنيد
براي اين مورد شما به سادگي مي‌تونيد يه JOIN بزنيد

يه مورد امنيتي هم كه شما رعايت نكرديد، ارسال مستقيم متغير GET به پايگاه داده هست
قبل از ارسال بررسي كنيد كه اين متغير عددي باشه تا از SQL Injection جلوگيري كنيد

[i-php-i]

Indexهاي ايجاد شده رو وقتي كه جدول رو باز كنيد
در بخش پايين و سمت چپ صفحه قابل مشاهده هست

جدول ايجاد شده براي Index ها هم داخلي هست و در فهرست به شما نشون داده نمي‌شه

در مورد پرس‌وجويي كه نوشته بوديد

در اين موارد كه تنها يك رديف مورد نياز شما هست بهتره كه در انتهاي پرس‌وجو از عبارت LIMIT 1 استفاده كنيد
در اين حالت وقتي كه اولين ركورد برگردونده بشه، تحت هر شرايطي، پرس‌وجو متوقف مي‌شه و در كمترين زمان ممكن نتايج برگشت داده مي‌شن
علاوه بر ايجاد Index براي فيلد textid سعي كنيد از Query در Query استفاده نكنيد
براي اين مورد شما به سادگي مي‌تونيد يه JOIN بزنيد

يه مورد امنيتي هم كه شما رعايت نكرديد، ارسال مستقيم متغير GET به پايگاه داده هست
قبل از ارسال بررسي كنيد كه اين متغير عددي باشه تا از SQL Injection جلوگيري كنيد
قبل از قرار دادن متغیر گت توی کوئری، اون رو تست کردم و مشکلی نداره.

موقع اضافه کردن ایندکس به یه ستون، باید نام ایندکس (Index name) و سایز ایندکس (Size) رو مشخص کنیم. لطفا بگید که این مقادیر برای چه کاری هستن و اگر سایز ایندکس رو زیاد در نظر بگیریم، سرعت سیستم کاهش پیدا نمی کنه؟

[AMIBCT]

نام Index كه اختياري هست و بهتره طوري اون رو مشخص كنيد كه نشون‌دهنده‌ي نام جدول و نام فيلد باشه

اندازه‌ي Index براي ستون‌هاي متني يا دودويي كاربرد داره
اگه اين اندازه رو مشخص كنيد، فقط مقدار مشخص شده از اون ستون Index مي‌شه
يعني اگه شما يه فيلد varchar داشته باشيد كه طولش ۲۵۵ حرف هست
مي‌تونيد ۲۰ بايت ابتدايي رو در جدول Index ذخيره كنيد

اين طوري چند تا مزيت به وجود مياد
اول اينكه حجم جدول Index خيلي كمتر اشغال مي‌شه
دوم موقع اضافه كردن رديف جديد، زمان كمتري صرف ايجاد Index مي‌شه
و سوم موقع جستجو و مرتب‌سازي، مورد دلخواه زودتر پيدا مي‌شه

تمام اين برتري‌ها موقعي به دست مياد كه در ۲۰ بايت ابتدايي فيلد مورد نظر، مقدار تكراري وجود نداشته باشه
اگه مقدارها تكراري باشن، كارايي Index از بين خواهد رفت

[i-php-i]

نام Index كه اختياري هست و بهتره طوري اون رو مشخص كنيد كه نشون‌دهنده‌ي نام جدول و نام فيلد باشه

اندازه‌ي Index براي ستون‌هاي متني يا دودويي كاربرد داره
اگه اين اندازه رو مشخص كنيد، فقط مقدار مشخص شده از اون ستون Index مي‌شه
يعني اگه شما يه فيلد varchar داشته باشيد كه طولش ۲۵۵ حرف هست
مي‌تونيد ۲۰ بايت ابتدايي رو در جدول Index ذخيره كنيد

اين طوري چند تا مزيت به وجود مياد
اول اينكه حجم جدول Index خيلي كمتر اشغال مي‌شه
دوم موقع اضافه كردن رديف جديد، زمان كمتري صرف ايجاد Index مي‌شه
و سوم موقع جستجو و مرتب‌سازي، مورد دلخواه زودتر پيدا مي‌شه

تمام اين برتري‌ها موقعي به دست مياد كه در ۲۰ بايت ابتدايي فيلد مورد نظر، مقدار تكراري وجود نداشته باشه
اگه مقدارها تكراري باشن، كارايي Index از بين خواهد رفت
آیا ایجاد ایندکس برای ستونهایی که متنهای بلند مثل متن یه مقاله رو ذخیره می کنن مناسب هست؟

[AMIBCT]

برای جستجو در متن مقاله باید از Full Text Search استفاده کنید
هر چند می‌تونید با همین روش بالا چند بایت ابتدایی اون مقاله رو Index کنید که چندان کاربردی به نظر نمی‌رسه و در جستجوی لغت به لغت هم کمکی به شما نخواهد کرد

در مورد Full Text Search
باید زمان بذارید و مستندات MySQL رو مطالعه کنید
نمی‌دونم آیا آموزش فارسی در این زمینه هست یا خیر