hi there,

some of the queries I'm doing involve joining 8-10+ tables with 10-20,000 records in them. I'm good about indexing but I've had some queries which annoyingly take 10,20 even 100 seconds.

I'm using phpMyAdmin.

Generally what I do is build the query up table by table to see where things begin to slow down. HOWEVER, there is a problem - caching

A query that took 9.58 seconds a moment ago might take .043 seconds a moment later - I assume because of caching where mysql determines nothing has changed.

How do I stop caching (and I hope that's the right phrase)? So that I know each time the "true" time a query is going to take to assemble. THANK YOU, I really appreciate it.

Sam

    OK I confess, no I didn't and it was pretty easy. But my contract with phpbuilder specifies that I get special treatment 😉

      5 days later

      Given how slow your queries are running you might not want to run them very many times, but I found this old script I used for testing. It uses the now-deprecated mysql extension rather than mysqli, but it could easily be modified:

      define("DB_HOST", "localhost");
define("DB_NAME", "my_database");
define("DB_USER", "db_user");
define("DB_PASS", "*****");

mysql_connect(DB_HOST, DB_USER, DB_PASS)
	or die("db connection failed");

mysql_select_db(DB_NAME)
	or die("db select failed.");

function clear_query_cache() {
	mysql_query("RESET QUERY CACHE;")
		or die("failed to reset query cache");
}


define("TOTAL_RUNS", 1000);
// define the query you want to test here
$sql = "SELECT col1, col2, col3 FROM my_table";

$total_elapsed_time = 0;
for($i=0; $i<TOTAL_RUNS; $i++) {
	// clear the query cache
	clear_query_cache();
	// run the query and time it
	$start = microtime(TRUE);
	mysql_query($sql)
		or die("query failed: " . mysql_error());
	$end = microtime(TRUE);
	$total_elapsed_time += ($end-$start);
}
echo "\nQuery run: " . $sql . "\n\n";
echo "Times: " . TOTAL_RUNS . "\n";
echo "Elapsed time:" . $total_elapsed_time . "\n";
echo "Time per query:" . ($total_elapsed_time / TOTAL_RUNS) . "\n";
        Write a Reply...