poirot Posted June 9, 2006 Share Posted June 9, 2006 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]<?phpmysql_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.799793Method 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\" /] Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/ Share on other sites More sharing options...
poirot Posted June 9, 2006 Author Share Posted June 9, 2006 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]<?phpmysql_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 = 10000Method 1: 0.188684Method 2: 2.462727$num = 500Method 1: 0.009768Method 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 = 10000Method 1: 0.288067Method 2: 2.914783$num = 500Method 1: 0.014375Method 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\" /] Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43524 Share on other sites More sharing options...
hvle Posted June 9, 2006 Share Posted June 9, 2006 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 Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43525 Share on other sites More sharing options...
poirot Posted June 9, 2006 Author Share Posted June 9, 2006 [!--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\" /] Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43528 Share on other sites More sharing options...
hvle Posted June 9, 2006 Share Posted June 9, 2006 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 Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43530 Share on other sites More sharing options...
poirot Posted June 9, 2006 Author Share Posted June 9, 2006 [!--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\" /] Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43533 Share on other sites More sharing options...
hvle Posted June 9, 2006 Share Posted June 9, 2006 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 Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43535 Share on other sites More sharing options...
poirot Posted June 9, 2006 Author Share Posted June 9, 2006 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\" /] Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43775 Share on other sites More sharing options...
Fyorl Posted June 9, 2006 Share Posted June 9, 2006 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. Quote Link to comment https://forums.phpfreaks.com/topic/11550-mysql-only-vs-phpmysql/#findComment-43846 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.