Online computer courses, code, programming tutorial and sidebar information for monitoring Canadian S&P/TSX index. Build friendship and networking. Welcome to visit my blogs often!!! I also have two other sites: YouTube Channel and Google site.
Adsense
Popular Posts
- PHPWind-- A PHP forum script applcaition in China
- How to blend adsense inside your post?
- Formatting my post
- Notepad++ - Add C++ compiler
- Install PHPMailer 5.2.4 and use smtp gmail
- Set up a child account and set screen time limit in Windows 8
- Wayback Machine - see archived versions of web pages across time
- phpexcel toggle expand and hide column in EXCEL and summary
- Install PHP ibm_db2 extension in Linux (redHat)
- PHP: add a download as pdf file button in report page
Friday, November 29, 2013
Increase MySQL SELECT speed in complicated statement
I have following MySQL statement using 4 SELECT loops:
$sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))) ORDER BY study_code";
$result = mysql_query($sql, $db);
It takes about 2 minutes to finish execution.
Change the MySQL statement into two steps: one is three SELECT, another one SELECT
//step 1
$sql = "SELECT subject_id FROM review_slot_subjects WHERE review_slot_id
IN (SELECT review_slot_id FROM review_slots WHERE project_id=$pid AND review_slot_id
IN (SELECT review_slot_id FROM review_slot_reviewers WHERE member_id=$rid))";
$result = mysql_query($sql, $db);
$subject_id_array=array();
while($row = mysql_fetch_assoc($result)) $subject_id_array[]=$row['subject_id'];
//step 2
$matches = implode(',',$subject_id_array);
$sql = "SELECT subject_id,study_code,active,reminders FROM subjects WHERE subject_id IN(
$matches) ORDER BY study_code";
$result = mysql_query($sql, $db);
The MySQl execution time is reduced to few seconds.
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment