Jump to content


Photo

MySQL only vs. PHP+MySQL


  • Please log in to reply
8 replies to this topic

#1 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 03:29 AM

Hi guys...

I am trying to figure out what is better: use MySQL only or MySQL+PHP to do some things. To not jump into arbitrary conclusions, I'll do some testing.

I'm using a package called [a href=\"http://www.uniformserver.com\" target=\"_blank\"]Uniform Server[/a], under Windows obviously.

Test 1: Pull 10 random records from the database.

For this test I used Alice's (AIML chat bot) patterns table. There are 273,720 records. The code is the following:

<?php

mysql_connect('localhost', 'user', 'pass');
mysql_select_db('alice');

$time1 = microtime(true);

// Method 1 Start
$query1 = mysql_query("SELECT * FROM `patterns` ORDER BY RAND() LIMIT 10");

while ($row = mysql_fetch_assoc($query1)) {
   $array1[] = $row;
}

// Method 1 End

$time2 = microtime(true);

// Method 2 Start
$query2 = mysql_query("SELECT * FROM `patterns`");

while ($row2 = mysql_fetch_assoc($query2)) {
   $array2_temp[] = $row2;
}

srand((float) microtime() * 10000000);
$rand = array_rand($array2_temp, 10);

foreach ($rand as $value) {
   $array2[] = $array2_temp[$value];
}
// Method 2 End

$time3 = microtime(true);

echo 'Method 1: ' . number_format(($time2 - $time1), 6) . '<br />';
echo 'Method 2: ' . number_format(($time3 - $time2), 6) . '<br />';

echo '<pre>';
print_r($array1);
print_r($array2);
echo '</pre>';

?>

Guess what? The results...

[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]Method 1: 14.799793
Method 2: 6.639532[/quote]

If there is something I could improved in the code to make things faster, tell me.

Apparently PHP+MySQL wins this one.
I will test some more things and post feedback here... [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]
~ D Kuang

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:35 AM

OK... BTW Test 1 was based on:
[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=95399\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?showtopic=95399[/a]

And Test 2 (this) is based on:
[a href=\"http://www.phpfreaks.com/forums/index.php?showtopic=95375\" target=\"_blank\"]http://www.phpfreaks.com/forums/index.php?showtopic=95375[/a]

Test 2: Seach for all entries from a particular month.
Database: 2 fields, 1 id field, the date field with a UNIX timestamp.

The code I used is the following. Suggestion on how to improve performance are appreciated [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]
<?php

mysql_connect('localhost', 'user', 'pass');
mysql_select_db('dates');

$time1 = microtime(true);

// Number of records to retrieve
$num = 10000;

// Method 1 Start
$query1 = mysql_query("SELECT * FROM `records` WHERE EXTRACT(MONTH FROM FROM_UNIXTIME(date))=2 LIMIT $num");

while ($row = mysql_fetch_assoc($query1)) {
   $array1[] = $row;
}

// Method 1 End

$time2 = microtime(true);

// Method 2 Start
$query2 = mysql_query("SELECT * FROM `records`");

while ($row2 = mysql_fetch_assoc($query2)) {
   $array2_temp[] = $row2;
}

$array2 = array();

$v = 0;

foreach ($array2_temp as $value) {

   $data = getdate($value['date']);

   if ($data['mon'] == 2) {
      $array2[] = $value;
      $v++;

      if ($v == $num) {
         break;
      }
   }
}

// Method 2 End

$time3 = microtime(true);

echo 'Method 1: ' . number_format(($time2 - $time1), 6) . '<br />';
echo 'Method 2: ' . number_format(($time3 - $time2), 6) . '<br />';

echo '<pre>';
//print_r($array1);
//print_r($array2);
echo '</pre>';

?>

Results:
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$num = 10000
Method 1: 0.188684
Method 2: 2.462727

$num = 500
Method 1: 0.009768
Method 2: 1.453737[/quote]

Then I added a dummy field to every row with a short sentence (tried a paragraph but it crashed my script).
So I made a second test with them.
[!--quoteo--][div class=\'quotetop\']QUOTE[/div][div class=\'quotemain\'][!--quotec--]$num = 10000
Method 1: 0.288067
Method 2: 2.914783

$num = 500
Method 1: 0.014375
Method 2: 1.916663[/quote]

Conclusion: MySQL only here is way faster, BUT apparently the dummy field increases the load for more for MySQL only than for PHP+MySQL.

This leaves me really puzzled... Some guys here, especially Barand seems to like to use MySQL only...
But it won't be always faster...

What should I use? Or at least what do I do to make a good guess?

Thanks in advance [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]
~ D Kuang

#3 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 09 June 2006 - 04:39 AM

I think both method are very inefficient because both required to pull all record from database.

Why don't you make it so it only pull 10 records randomly.

given that each record distinguished by an id.

firsth, select 10 random number from 1 to #of record in database.
make a single query to get that 10 records.

select * from patterns where id in (3,5,5394,293,2938,27...);

that's it!
Life's too short for arguing.

#4 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 04:47 AM

[!--quoteo(post=381735:date=Jun 8 2006, 09:39 PM:name=hvle)--][div class=\'quotetop\']QUOTE(hvle @ Jun 8 2006, 09:39 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
I think both method are very inefficient because both required to pull all record from database.

Why don't you make it so it only pull 10 records randomly.

given that each record distinguished by an id.

firsth, select 10 random number from 1 to #of record in database.
make a single query to get that 10 records.

select * from patterns where id in (3,5,5394,293,2938,27...);

that's it!
[/quote]
They are needed in case we don't have a numeric id field.
And also what you suggested may not work since there we cannot guarantee the random id's actually exist. [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]
~ D Kuang

#5 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 09 June 2006 - 04:55 AM

well, in that case, yes, it's good to know that mysql+php can out perform mysql alone.

But someone who designed the database and did not put in some sort of auto-increment or id is a total idiot and would very unlikely care about efficiency.

Tom
Life's too short for arguing.

#6 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 05:05 AM

[!--quoteo(post=381740:date=Jun 8 2006, 09:55 PM:name=hvle)--][div class=\'quotetop\']QUOTE(hvle @ Jun 8 2006, 09:55 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
well, in that case, yes, it's good to know that mysql+php can out perform mysql alone.

But someone who designed the database and did not put in some sort of auto-increment or id is a total idiot and would very unlikely care about efficiency.

Tom
[/quote]
Don't forget that deleted rows also will make if fail [img src=\"style_emoticons/[#EMO_DIR#]/wink.gif\" style=\"vertical-align:middle\" emoid=\":wink:\" border=\"0\" alt=\"wink.gif\" /]
I think that this is a case by case thing, but I'd like to know how to make a good guess [img src=\"style_emoticons/[#EMO_DIR#]/smile.gif\" style=\"vertical-align:middle\" emoid=\":smile:\" border=\"0\" alt=\"smile.gif\" /]
~ D Kuang

#7 hvle

hvle
  • Members
  • PipPipPip
  • Advanced Member
  • 667 posts
  • Locationmelbourne, Australia

Posted 09 June 2006 - 05:10 AM

You seems to be interested more about engine designing and efficiency. I would love to see more of your posts on this issue. I will try to contribute within my knowledge.

Tom
Life's too short for arguing.

#8 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 09 June 2006 - 06:30 PM

Actually I ran out of ideas on what to test... [img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /]
~ D Kuang

#9 Fyorl

Fyorl
  • Members
  • PipPipPip
  • Advanced Member
  • 273 posts
  • LocationUK

Posted 09 June 2006 - 09:05 PM

I guess you could test whether using COUNT(*) is faster than counting the results from a SELECT. Somehow I think MySQL only will be way faster.
[table]



Don't worry, the printer fairies will sort it out.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users