MYSQL Performance diff

[section31]

Hi, does anyone know how much more efficient would selecting a few rows out of lets say a 1000 using the where int clause rather than using a where varchar(25) clause.

Both indexed of course.

 

Would this be a drastic increase in efficiency or not?

 

thanks,

Dave

[surefire]

I believe it would be more efficient, but by HOW MUCH... only a test could tell.

 

There are timing scripts you can plug into your pages to give you a rough estimate of how long they take to execute.

 

from php.net

 

<?php

 

function getmicrotime() {

  list($usec, $sec) = explode(" ", microtime());

  return ((float)$usec + (float)$sec);

}

 

$time_start = getmicrotime();

 

for ($i=0; $i < 1000; $i++){

  // do nothing, 1000 times

}

 

$time_end = getmicrotime();

$time = $time_end - $time_start;

 

echo "Did nothing in $time seconds\n";

 

// with PHP 5 you can do the same this way:

 

$time_start = microtime(1);

 

for ($i=0; $i < 1000; $i++){

    // do nothing, 1000 times

}

 

$time_end = microtime(1);

$time = $time_end - $time_start;

 

echo "Did nothing in $time seconds\n";

 

?>

[section31]

ya, I have something like that on another script of mine. I guess your right..I'll have to check myself.

[section31]

I tested it against a few thousand rows and all my tests showed the where varchar to be faster...I find that odd. why is that...you would think it would be easier to search for ints.

[surefire]

I don't know... but I bet you're glad you tested it for yourself. If you had just assumed what you thought you knew... as I did... then you wouldn't have chosen correctly.

 

This is a little reminder to me that I don't know everything and shouldn't take anything for granted.

 

Thanks for reporting your results.

 

By the way, was it drastically faster? Or just slightly?

[section31]

I did it from 1000 to 5000 rows... and it was only slightly faster.... I didn't calculate the percentage difference which i should have...and I already cleaned out my dummy table. But as I recall it was only like a .0002 seconds faster querying 5000 rows. But every time i ran the script the varchar was always faster.