Re: MySQL Speed

Ridge Burner wrote:
Can someone tell me which of these 2 SQL queries will be more
efficient? I'm having a debate with another guy about which would be
less resource intensive for MySQL.
Depends:
<?php
$db = mysql_connect();
$conn = mysql_select_db("testbase",$db);

$start1 = microtime(true);
for($i=1;$i<2000;$i++){
$sql = "SELECT * FROM wnk_huizen ORDER BY RAND() LIMIT 1";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
}
$end1 = microtime(true);

$start2 = microtime(true);

for($i=1;$i<2000;$i++){
$value = array();
$sql = "SELECT id FROM wnk_huizen";
$query = mysql_query($sql);
while($result = mysql_fetch_array($query)){
$value[] = $result;
}
$random = rand(0,count($value)-1);
$sql = "SELECT * FROM wnk_huizen WHERE id='" . $value[$random] . "' LIMIT
1";
$query = mysql_query($sql);
$result = mysql_fetch_array($query);
}
$end2 = microtime(true);
echo "<br />result with ".count($value)." records;<br />";
$time1= $end1-$start1;
$time2= $end2-$start2;
echo "<br />Option 1:".$start1."-".$end1." :".$time1;
echo "<br />Option 2:".$start2."-".$end2." :".$time2;
?>

Result from my extremely slow testserver:

result with 69 records;

Option 1:1146258591.25-1146258626.6845 :35.434526205063
Option 2:1146258626.6845-1146258636.4802 :9.7956740856171

result with 2208 records;

Option 1:1146259157.8281-1146259527.2195 :369.39138197899
Option 2:1146259527.2195-1146259655.4201 :128.20057296753

You'd think option 1 is slower, except if I use a different table:

result with 613 records (id=int(4), primary key);

Option 1:1146258636.4804-1146258644.8055 :8.3251550197601
Option 2:1146258644.8056-1146258683.6749 :38.869340896606

I presume it's because the first table had "text" fields, the second
integers and a 2 VARCHAR(50).
for your reference:
TABLE 1:
Field Type Null Key Default Extra
id int(5) PRI NULL auto_increment
stad varchar(50)
postcode varchar(7)
adres varchar(70)
page varchar(40)
prijs varchar(20)
short_desc text
long_desc text
status char(1)
time timestamp YES CURRENT_TIMESTAMP

TABLE 2
Field Type Null Key Default Extra
id int(5) PRI NULL auto_increment
img_name varchar(50)
img_huis_id int(5) 0
img_huis_default tinyint(1) 0
img_huis_desc varchar(50)


So, it's highly dependable on the database, I'm not going to waste time
checking the details myself, but some people on the mysql newsgroup might
now how ORDER BY RAND() is affected by type of fields in a table.

Grtz,
--
Rik Wasmus


.

Relevant Pages

  • Re: mySQL and subtracing .5
    ... If you want decimal places then change the field type ... to FLOAT, REAL, DECIMAL or NUMERIC. ... > I have tried it in mysql but it does seem to work, ...
    (comp.lang.php)
  • Re: Problem with SQL and Recordset
    ... This assumes that the name of the subform control on the parent form is the ... NextSuffix field from the "query" to be entered into the Suffix field on ... Dim mySQL As String ...
    (microsoft.public.access.modulesdaovba)
  • Re: [Q]Problems related to the MySQL linked Server.
    ... The error messge when I query data with following SQL. ... select @v_intUserNo = uno ... where userid = 'testid' ... I have two questions about the linked server for the MySQL. ...
    (microsoft.public.sqlserver.odbc)
  • Re: Performance MySQL
    ... schicke erst den Query an die Datenbank ab, ... Möglichkeit von Concurrent Inserts nur MyISAM Storages ein. ... > MySQL bis jetzt noch nichts zu bieten. ... Welcher Tabellentyp? ...
    (de.comp.datenbanken.mysql)
  • Re: where to get mySQL questions answered?
    ... After resolving some performance issues with mysql in building initial ... soon as I add an OR to my WHERE x LIKE '%blah%' type queries. ... My php code dynamically builds an sql query. ...
    (comp.lang.php)