Select Query Optimization

digital_skwerl

I am performing a member search on a database where several one to many relationships exist. My current query often crashes MySQL on a "Copying to Temp Table" process which I then have to Kill to resume functionality. This happens when I execute a very broad search, however more specific searches work with no problems (they are very slow however).

I've read that the Copying to Temp Table crash happens most commonly using Group By (which im using here), and there is some debate whether this is a mysql bug or just bad coding on my part. Either way, I know that the query is very slow and poorly constructed and I'm looking for some help with its performance.

There is 1 users table (users) and 6 user classification tables (user_disciplines, user_grades, user_languages, user_locations, user_skills, user_subjects) which can all have mulitiple entries related to a particular user_id. I will reply to this post with table structures if needed.

Here we go...

Collecting the search form data and creating query parameters...

if(isset($_GET['search'])) {

						//State
						if(empty($_GET['state'])) {
							$sta_q  = "";
							} else {
							$state = $_GET['state'];
							$sta_q = " AND user_locations.state='$state' AND user_locations.user_id = users.user_id";
						}

						//County
						if(empty($_GET['county'])) {
							$cou_q  = "";
							} else {
							$county = $_GET['county'];
							$cou_q = " AND user_locations.county='$county' AND user_locations.user_id = users.user_id";
						}

						//Discipline
						if(empty($_GET['dis'])) {
							$dis_q  = "";
							} else {
							$did = $_GET['dis'];
							$dis_q = " AND user_disciplines.dis_id=$did AND user_disciplines.user_id = users.user_id";									
						}

						//Subject
						if(empty($_GET['subj'])) {
							$sub_q  = "";
							} else {
							$sbid = $_GET['subj'];
							$sub_q = " AND user_subjects.sub_id=$sbid AND user_subjects.user_id = users.user_id";
						}

						//Level
						if(empty($_GET['lev'])) {
							$lev_q  = "";
							} else {
							$gid = $_GET['lev'];
							$lev_q = " AND user_grades.gra_id=$gid AND user_grades.user_id = users.user_id";
						}	

						//City
						if(empty($_GET['cit'])) {
							$cit_q  = "";
							} else {
							$city = $_GET['cit'];
							$cit_q = " AND user_locations.city='$city' AND user_locations.user_id = users.user_id";
						}	

						//Skill
						if(empty($_GET['ski'])) {
							$ski_q  = "";
							} else {
							$skid = $_GET['ski'];
							$ski_q = " AND user_skills.ski_id=$skid AND user_skills.user_id = users.user_id";
						}		

						//Language
						if(empty($_GET['lan'])) {
							$lan_q  = "";
							} else {
							$lid = $_GET['lan'];
							$lan_q = " AND user_languages.lan_id=$lid AND user_languages.user_id = users.user_id";
						}

						//Price
						if(empty($_GET['pri'])) {
							$pri_q  = "";
							} else {
							$pri = $_GET['pri'];
							$prix = explode('-', $pri);
							$price1 = $prix[0];
							$price2 = $prix[1];
							$pri_q = " AND users.price BETWEEN $price1 AND $price2";
						}	

						//Gender
						if(empty($_GET['gen'])) {
							$gen_q  = "";
							} else {
							$gen = $_GET['gen'];
							$gen_q = " AND users.sex='$gen'";
						}	

						//Mode
						if(empty($_GET['mod'])) {
							$mod_q  = "";
							} else {
							$mod = $_GET['mod'];
							if($mod == 'P') {
								$mode = 'mode_person';
							}
							if($mod == 'O') {
								$mode = 'mode_video';
							}
							if($mod == 'E') {
								$mode = 'mode_email';
							}
							$mod_q = " AND users.$mode='Y'";
						}	

						//Certified
						if(!isset($_GET['cert'])) {
							$cer_q  = "";
							} else {
							$cer = $_GET['cert'];
							$cer_q = " AND users.certified='Y'";
						}	

						//Kroll
						if(!isset($_GET['kro'])) {
							$kro_q  = "";
							} else {
							$kro = $_GET['kro'];
							$kro_q = " AND users.kroll='Y'";
						}	

						//Photo
						if(!isset($_GET['pic'])) {
							$pic_q  = "";
							} else {
							$pic = $_GET['pic'];
							$pic_q = " AND users.pic!='images/pic.jpg'";
						}

Writing the query...

require_once('./x/mysql_connect.php');
								$query = "SELECT users.display_name, users.intro, users.thumb, users.user_id, titles.title, users.county, users.state, users.kroll, users.certified 
								FROM users, titles, user_disciplines, user_subjects, user_grades, user_locations, user_skills 
								WHERE users.active='Y' AND users.tit_id = titles.tit_id 
								$sta_q 
								$cou_q 
								$dis_q 
								$sub_q 
								$lev_q 
								$cit_q 
								$ski_q 
								$lan_q 
								$pri_q 
								$gen_q 
								$mod_q 
								$cer_q 
								$cro_q 
								$pic_q
								GROUP BY users.user_id ORDER BY users.last_name ASC";

Any help, very very much appreciated!

Weedpacket

The most glaring problem I see is that you join all your tables whether you have any constraints on their values or not. If you have, say, 20 user_disciplines, 8 user_grades, 50 user_locations, 40 user_subjects, and 40 user_skills, then if you don't specify any values to restrict which records from those tables match, each record that does match find will be duplicated 20×8×50×40×40=12,800,000 times. I suspect that it's only after all those (duplicate) matches are found that the GROUP BY is performed, evaluating any aggregate functions (like COUNT or SUM) that were specified in the query (although there aren't any in this case).

So the first thing to do is leave those extra tables out unless you need them. And I suspect you'd be better of using SELECT DISTINCT to remove duplicates (if there are any after you've fixed that business with the tables) instead of GROUP BY.