PHP MySQL query timeouts

[whitecrook]

hi all

I have this code, generating VIEWS in my database.

Essentially I have a 200,000+ row table, which has a few hundred more rows ('apps') added to it each day.
These newly added apps are compared against all previously entered apps, and 2 results are found: Apps previously entered into the database, and 'new' apps, i.e. apps the database has never seen before. (per date)

Now that's pretty straightforward. here's the function

function getApps($date_value, $querystring) {
	$tablename = 'ts' . strtotime ( $date_value );
	$querystringtmp = 'CREATE OR REPLACE VIEW ' . $tablename . 
						' AS SELECT main_table.id, main_table.app_no, main_table.date, main_table.council' .
						' FROM main_table WHERE main_table.date = \''. $date_value . '\'';
	print $querystringtmp;
	mysql_query($querystringtmp) or trigger_error(mysql_error()); 
	
	$querystringtmp = 'CREATE OR REPLACE VIEW upto_' . $tablename . 
					' AS SELECT main_table.id, main_table.app_no, main_table.date, main_table.council' .
					' FROM main_table WHERE main_table.date < \''. $date_value . '\'';
	print $querystringtmp;
	mysql_query($querystringtmp) or trigger_error(mysql_error()); 

	$querystringtmp = 'CREATE OR REPLACE VIEW ' . $tablename . '_NEW AS SELECT ' . $tablename . '.id, ' . $tablename . '.app_no, ' . $tablename . '.date, ' . $tablename . '.council
FROM ' . $tablename . ' LEFT JOIN upto_' . $tablename . ' ON ' . $tablename . '.app_no = upto_' . $tablename . '.app_no
WHERE upto_' . $tablename . '.app_no Is Null';
	print $querystringtmp;
	mysql_query($querystringtmp) or trigger_error(mysql_error()); 

	$querystringtmp = 'CREATE OR REPLACE VIEW ' . $tablename . '_OLD AS SELECT ' . $tablename . '.id, ' . $tablename . '.app_no, ' . $tablename . '.date, ' . $tablename . '.council
FROM ' . $tablename . ' LEFT JOIN upto_' . $tablename . ' ON ' . $tablename. '.app_no = upto_' . $tablename . '.app_no
WHERE upto_' . $tablename . '.app_no Is NOT Null';
	print $querystringtmp;
	mysql_query($querystringtmp) or trigger_error(mysql_error()); 

/* 	$querystringOLD='SELECT * FROM ' . $tablename . '_OLD ORDER BY id DESC LIMIT ' . $start_from .', ' . $records_per_page ;
	
	$querystringNEW='SELECT * FROM ' . $tablename . '_NEW ORDER BY id DESC LIMIT ' . $start_from .', ' . $records_per_page ;
 */
	$querystringOLD='SELECT * FROM ' . $tablename . '_OLD ORDER BY id DESC';
	
	$querystringNEW='SELECT * FROM ' . $tablename . '_NEW ORDER BY id DESC';

	$querystring_tmp = array( $tablename => array( 'New' => $querystringNEW,
											'Old' => $querystringOLD
											)
					);
					
	if (!isset ($querystring)) {
		$querystring = $querystring_tmp;
	} else {
		$querystring = array_merge($querystring, $querystring_tmp);
	};
					
echo '<P>';		
	print_r($querystring);


	return $querystring;
					
}

OK, I know it needs a bit of tidying, but when I was testing with only 30 rows, this worked OK. Now that I have 200,000 records i am getting timeout errors. Even in phpmyadmin. When I click to view the OLD view (i.e. 'SELECT * FROM ' . $tablename . '_OLD ORDER BY id DESC'

The error in php myadmin occurs after only drawing 107 records tos the screen...

These tables should have only less than 3-400 records max.

So is there a better way to query the system ? Something more efficient? Should I be creating tables rather than views?

Need anymore info?

thanks all

 

[whitecrook]

The problem seems to be with this query

SELECT ts1241046000.id, ts1241046000.app_no, ts1241046000.date, ts1241046000.council
FROM ts1241046000 LEFT JOIN upto_ts1241046000 ON ts1241046000.app_no = upto_ts1241046000.app_no
WHERE upto_ts1241046000.app_no Is Null;

If I enter that into phpmyadmin it just timeouts. Both upto_ts1241046000 and ts1241046000 are just views as created above which both complete in less than a second, and hold around 200,000 and just less than 400 records respectively.

 

[whitecrook]

OK I fixed it. Basically had to index the table. And my query for find OLD was wrong.

When new data is added to the table do I have to perform a re-indexing of the data or will mysql automatically index it for me?

function getApps($date_value, $querystring) {
	$tablename = 'ts' . strtotime ( $date_value );
	$querystring1 = 'CREATE OR REPLACE VIEW ' . $tablename . 
					' AS SELECT main_table.id, main_table.app_no, main_table.date, main_table.council' .
					' FROM main_table WHERE main_table.date = \''. $date_value . '\'';
//	print $querystring1;
//	mysql_query($querystring1) or trigger_error(mysql_error()); 
	$querystring2 = 'CREATE OR REPLACE VIEW ' . $tablename . '_upto' .
					' AS SELECT main_table.id, main_table.app_no, main_table.date, main_table.council' .
					' FROM main_table WHERE main_table.date < \''. $date_value . '\'';
//	print $querystring2;
//	mysql_query($querystring2) or trigger_error(mysql_error()); 
	$querystring3 = 'CREATE OR REPLACE VIEW ' . $tablename . '_new' .
					' AS SELECT ' . $tablename.'.id,' . 
					$tablename.'.app_no, ' . 
					$tablename . '.date, ' . 
					$tablename . '.council' .
					' FROM ' . $tablename . ' LEFT JOIN ' . $tablename . '_upto' .
					' ON ' . $tablename . '.app_no = ' . $tablename . '_upto.app_no WHERE ' . 
					$tablename . '_upto.app_no Is Null';
//	print $querystring3;
//	mysql_query($querystring3) or trigger_error(mysql_error()); 
	$querystring4 = 'CREATE OR REPLACE VIEW ' . $tablename . '_old' . 
					' AS SELECT ' . $tablename . '.id, ' . 
					$tablename . '.app_no, ' . 
					$tablename . '.date, ' . 
					$tablename . '.council' .
					' FROM ' . $tablename . ' LEFT JOIN ' . $tablename . '_new' . 
					' ON ' . $tablename . '.app_no = ' . $tablename .'_new.app_no WHERE ' . 
					$tablename . '_new.app_no Is Null';
//	print $querystring4;
//	mysql_query($querystring4)
		mysql_query("begin"); 
		mysql_query($querystring1); 
		mysql_query($querystring2); 
		mysql_query($querystring3); 
		mysql_query($querystring4); 
		mysql_query("commit");
/* 	$querystringOLD='SELECT * FROM ' . $tablename . '_OLD ORDER BY id DESC LIMIT ' . $start_from .', ' . $records_per_page ;
	$querystringNEW='SELECT * FROM ' . $tablename . '_NEW ORDER BY id DESC LIMIT ' . $start_from .', ' . $records_per_page ;
 */
	$querystringOLD='SELECT * FROM ' . $tablename . '_old ORDER BY id DESC';
	$querystringNEW='SELECT * FROM ' . $tablename . '_new ORDER BY id DESC';
	$querystring_tmp = array( $tablename => array( 'New' => $querystringNEW,
											'Old' => $querystringOLD
											)
					);	
	if ( $querystring == 0) {
		$querystring = $querystring_tmp;
	} else {
		$querystring = array_merge($querystring, $querystring_tmp);
	};
		
//	print_r($querystring);
	return $querystring;		
}

// END FUNCTION