Jump to content

MySQL only vs. PHP+MySQL


poirot

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.