Jump to content

Archived

This topic is now archived and is closed to further replies.

poirot

MySQL only vs. PHP+MySQL

Recommended Posts

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.

[b]Test 1[/b]: 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:

[code]<?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>';

?>[/code]

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\" /]

Share this post


Link to post
Share on other sites
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]

[b]Test 2[/b]: 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\" /]
[code]<?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>';

?>[/code]

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]

[b]Conclusion[/b]: 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\" /]

Share this post


Link to post
Share on other sites
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!

Share this post


Link to post
Share on other sites
[!--quoteo(post=381735:date=Jun 8 2006, 09:39 PM:name=hvle)--][div class=\'quotetop\']QUOTE(hvle @ Jun 8 2006, 09:39 PM) [snapback]381735[/snapback][/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\" /]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
[!--quoteo(post=381740:date=Jun 8 2006, 09:55 PM:name=hvle)--][div class=\'quotetop\']QUOTE(hvle @ Jun 8 2006, 09:55 PM) [snapback]381740[/snapback][/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\" /]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites
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\" /]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.