Distinct AND Group By have LONG delay, return nothing.

[njdubois]

Monday, everything worked fine.  Tuesday godaddy started updates.  Now, today this.

 

I'm trying to get a distinct list of years from an auto database for user input.  As mentioned, Monday this code worked no problems.  And fyi worked fine for years.

<?php
	$Car_Info_Con = mysql_connect($host,"marcomdata",$password);

	if (!$Car_Info_Con) {die('Could not connect: ' . mysql_error());}
	mysql_select_db("marcomdata", $Car_Info_Con);
	
	// Works, is fast, but not what I want.
	$Car_Info_All_SQL="SELECT * FROM car_make ORDER BY year DESC ";
	

	// These cause the page to SLOW DOWN and eventually finishes with
	// an empty select element.

	//$Car_Info_All_SQL="SELECT * FROM car_make GROUP BY year ORDER BY year DESC ";
	//$Car_Info_All_SQL="SELECT year FROM car_make GROUP BY year ORDER BY year DESC ";
	//$Car_Info_All_SQL="SELECT DISTINCT year FROM car_make ORDER BY year DESC ";


	echo $Car_Info_All_SQL . '<br />';

	$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

	$year_html = '<select id="year" name="year" onchange="do_Auto(this.name,this.value)"onfocus=" clear_state(this.name);" onblur="saveData(this.name,this.value);">';

	if($cur_car_year=='') {
		$year_html.='<option></option>';
	}

	if ($Car_Info_All_results) {

		while ($row = mysql_fetch_array($Car_Info_All_results)) {
			if ($row['year']==$cur_car_year) {
				$year_html.='<option selected>'.$row['year'].'</option>';
			}
			else {
				$year_html.='<option>'.$row['year'].'</option>';
			}
		}

	}

	$year_html.='</select>';

	mysql_close($Car_Info_Con);

	echo $year_html;
?>

What am I missing?  I can take the above SQL statements and run them inside the database and it is ripping fast.

For example, 

SELECT year FROM car_make GROUP BY year ORDER BY year DESC

returns :

Showing rows 0 - 29 (79 total, Query took 0.1548 sec) 

 

whats going on here?

 

don't tell me it's godaddy.  Was on the phone with them for a while already today and it "isn't anything on their end."

 

Options?

 

Thanks so much for your help!

 

Nick

 

 

[njdubois]

I'm sorry, I forgot to mention that with the SQL statement being anything with Distinct or Group By, the page spins and spins for a good 30 seconds before it comes back false.

 

If I take the distinct/group by out it loads zippy quick.

 

Nick

[CroNiX]

Do you have the fields indexed that you are using distinct or group by on?

[njdubois]

The table fields are as follows

id, year, make, model and trim.

 

Is the ID the index?  How would I use the ID to help with this?

 

If the ID is not the index, what do I need to look up?

 

Thanks

 

Nick

[ginerjm]

I say this over and over again - Turn On PHP Error Checking.

 

Also - what do you mean by 'it returns false'?  What line is that coming from? 

 

Try testing your query result to see if it failed or not.

[njdubois]

I am testing the Query result, if ($Car_Info_All_results) {} This is where it is testing if the query is "false" or equal to the results.

 

I'm pretty sure PHP error checking is on, if I take the if($Car_Info_All_results) statement out, I get this error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in public_html/autotest.php on line 31

 

As mentioned, it returns false.  Meaning that there was a problem with the query right?  So, when I take the query and paste it into the php Mysql Panel, it works, and is really fast.  Statements using Group By, or Distinct.  Either way, less than a second.

 

The database itself is a collection of american vehicles going back to the 80's.  ID, year, make, model, trim.  I have a form where a user can enter a vehicle mentioned in a phone call.  The above chuck of code pulls the list of available years, removing doubles and populates a select element to present the choices to the user.  This form has been in use for years without a problem.  Same exact, unchanged code.

 

Thanks for the help, I'm so confused.

 

Nick

[ginerjm]

Where does it return false?  I don't see how you handle the 'false' condition of that if (which I missed before).

 

You're not sure if error checking is on?  Can you check that and make sure you are getting all Notices and All errors?

[njdubois]

This line

$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

sets false, and this line

if ($Car_Info_All_results) {

tests to see if it is and does return false.

 

To be sure I was getting all errors, I looked up PHP error checking, and added these two lines of code to the start of the above program.

error_reporting(-1);

ini_set('display_errors', 'On');

 

I got an error saying $cur_car_year was undefined, so I added a line of code and that stopped that error.  Still getting the 

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in 

error, and either way the page takes longer than it should to load.

 

The code now looks like this:

<?php
	error_reporting(-1);
	ini_set('display_errors', 'On');

	$Car_Info_Con = mysql_connect($host,"marcomdata",$password);

	if (!$Car_Info_Con) {die('Could not connect: ' . mysql_error());}
	mysql_select_db("marcomdata", $Car_Info_Con);
	
	// Works, is fast, but not what I want.
	//$Car_Info_All_SQL="SELECT * FROM car_make ORDER BY year DESC ";
	

	// These cause the page to SLOW DOWN and eventually finishes with
	// an empty select element.

	//$Car_Info_All_SQL="SELECT * FROM car_make GROUP BY year ORDER BY year DESC ";
	$Car_Info_All_SQL="SELECT year FROM car_make GROUP BY year ORDER BY year DESC ";
	//$Car_Info_All_SQL="SELECT DISTINCT year FROM car_make ORDER BY year DESC ";

	echo $Car_Info_All_SQL . '<br />';


	$Car_Info_All_results=mysql_query($Car_Info_All_SQL);

	$year_html = '<select id="year" name="year" onchange="do_Auto(this.name,this.value)"onfocus=" clear_state(this.name);" onblur="saveData(this.name,this.value);">';

	$cur_car_year="";  // This line is given a value in another place.
	if($cur_car_year=='') {
		$year_html.='<option></option>';
	}

	//if ($Car_Info_All_results) {

		while ($row = mysql_fetch_array($Car_Info_All_results)) {
			if ($row['year']==$cur_car_year) {
				$year_html.='<option selected>'.$row['year'].'</option>';
			}
			else {
				$year_html.='<option>'.$row['year'].'</option>';
			}
		}

	//}

	$year_html.='</select>';

	mysql_close($Car_Info_Con);

	echo $year_html;
?>

Thanks for the guidance!  

 

Nick

[mac_gyver]

for the logic testing if ($Car_Info_All_results) { ... } is true, you need an else { } branch using mysql_error() in it to get php to tell you why it thinks the query is failing.

[njdubois]

Oh Sir, or Ma'am!  Thank you.  We are not a little closer!

 

I added 

else {

echo mysql_error();

}

 

and it outputted:

Lost connection to MySQL server during query

 

I now know 2 things.  First, when I use the same SQL query in the mysql/php control panel, it works and is really fast.

 

Second, when I am trying to call the same SQL query from PHP, the connection to the mysql server is timing out.

 

My question now is this!  Why is PHP choking on mysql when I am using the DISTINCT, or GROUP BY mysql commands? The mysql Server itself is working, and handles the query in question without problems.  In all other areas of the webpage, the PHP connection to mysql works no problem.  Some of  these other areas are data intensive.  Pulling large amounts of data at any given time.  These operations are all working smoothly.

 

I want to also mention that if I pull everything from the database with this query:

"SELECT * FROM car_make ORDER BY year DESC"

The HTML Select element is populated with how ever many hundreds of records worth of years.  This happens as fast as it should.  So, it isn't just the connection to the server, it has to have something to do with GROUP BY, or DISTINCT.

 

Thanks for the assistance on a Sunday!  I really need to get this back by Monday and I think I'm slowly getting there.  Thank you!!

 

Nick

[ginerjm]

I took the liberty of cleaning up your code.  I firmly believe that using long var names and mixed cases is the problem with many coding errors.  Hence this:

<?php
error_reporting(E_ALL | E_NOTICE);  // Kinds of errors to show
ini_set('display_errors', '1');    // turn on display at the client to display the messages
$con = mysql_connect($host,"marcomdata",$password);
if (!$con)
{
 die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db("marcomdata", $con))
 die("Could not select db: " . mysql_error();
$sql = "SELECT year FROM car_make GROUP BY year ORDER BY year DESC ";
echo $sql . '<br />';
// run the query and check if it succeeds
$qresults = mysql_query($sql);
if (!$qresults)
 die("Query failed - ".mysql_error();
// begin building select tag
// NOTE - I removed semis from your function calls
$year_html = '<select id="year" name="year" onchange="do_Auto(this.name,this.value)" onfocus="clear_state(this.name)"  onblur="saveData(this.name,this.value)">';
$cur_car_year="";  // This line is given a value in another place.
if($cur_car_year=='')
{
 $year_html.='<option></option>';
}
//  loop thru query results and build options
while ($row = mysql_fetch_array($qresults))
{
 if ($row['year'] == $cur_car_year)
 {
  $year_html .= '<option selected>'.$row['year'].'</option>';
 }
 else
 {
  $year_html .= '<option>'.$row['year'].'</option>';
 }
}
$year_html .= '</select>';
mysql_close($con);
echo $year_html;
?>

Note the altered names. 

 

I do not see anything here that would cause your sql connection to  time out.  However I do see a problem with your options since none of them have a value so how are you going to analyze what the user clicks on?

 

I also added an error check DIRECTLY after your db select and query calls.  One must do this kind of thing all the time.

Edited July 27, 2014 by ginerjm

[njdubois]

While I do prefer my variable naming habits, in the end it is all personal preference.  Also, before hand, I though that the semis after function calls were required, but that is a conversation for another time.  I use this.value to pass the value of the option to javascript using onBlur and onChange.

 

Using your code, I still get the same output.

SELECT year FROM car_make GROUP BY year ORDER BY year DESC 
Query failed - Lost connection to MySQL server during query

 

What are my next steps?  GoDaddy isn't going to be able to offer any help.  I could do it using PHP, pull the whole table, test to see if the year is in an array, if not add it.  I don't see why I should not make mysql do the heavy lifting in this case.

 

Thanks for the guidance.  Personal coding habits aside, I know my code isn't bad, and that I am doing the connection and query correctly.  There is something in between that is broken.  It has to be something to do with the host.

 

I'll keep looking into it, and of course, thanks for the guidance!

 

Nick

[CroNiX]

post the schema to your car_make table.

SHOW COLUMNS FROM marcomdata.car_make;

 

I'm pretty sure you can make that query run a LOT faster if you had an index on the year field.

See what's happening here, and why group_by takes a long time. It has to do a lot of extra work.

http://dev.mysql.com/doc/refman/5.0/en/group-by-optimization.html

 

It's my experience that a lot of people don't properly index their database so that it performs well. Just having a (primary key) index on the "id" field isn't nearly enough in the vast majority of cases.

http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html

 

It (the slowness) doesn't show up when you only have a few records, or a few hundred records, but when you start getting a large database with thousands or millions of rows, it gets really slow because mysql has to process all of the rows.

Edited July 27, 2014 by CroNiX

[CroNiX]

And "Query failed - Lost connection to MySQL server during query" means that the query took longer than the "net_read_timeout" setting in mysql, which defaults to 30 seconds.

http://dev.mysql.com/doc/refman/5.0/en/error-lost-connection.html

[fastsol]

This block will always return true cause you are defining the var just before it runs.  So it makes no difference if it's defined soemwhere earlier cause you are overwriting it right before the block anyway.

$cur_car_year="";  // This line is given a value in another place.
	if($cur_car_year=='') {
		$year_html.='<option></option>';
	}

[njdubois]

fastsol, you solved my problem.

 

I went into the php mysql control panel, changed year from text to int, and clicked the nice "index" link next to the field.  

 

The code snippet I have been using here works, is fast, no problems.

 

Un-commented my code, where the original problem was, and it is working.\

 

Everything is great.

 

Thank you everyone for your help!

 

Nick