MySQL Speed

[marcus1060]

I'm using this PHP Script to access a MySQL database it doesn't load to much information, but for some reason everytime my page loads/refreshes, it takes a long time(and I'm on 1.5mb dsl and its hosted in T3) to load it.

<?php
$db = mysql_connect("localhost", "LOGiN", "PASSWORD");
mysql_select_db("links",$db);
$result = mysql_query("SELECT * FROM links ORDER BY RAND() LIMIT 10",$db);
if ($myrow = mysql_fetch_array($result)) {
  do {
  printf("-<a href='%s' target='_blank'>%s</a><br> \n",$myrow["link"], $myrow["title"]);
  } while ($myrow = mysql_fetch_array($result));
} else {
	echo "Sorry, no links were found!";	
}
?>

[tsinka]

Hi,

is the code part of a script or just the script ?

Besides that, you're missing one record because of the if statement. Change the code to something like

<?php
$db = mysql_connect("localhost", "LOGiN", "PASSWORD");
mysql_select_db("links",$db);
$result = mysql_query("SELECT * FROM links ORDER BY RAND() LIMIT 10",$db);
if (mysql_num_rows($result)) {
  while ($myrow = mysql_fetch_array($result)) {
    printf("-<a href='%s' target='_blank'>%s</a><br> \n",$myrow["link"], $myrow["title"]);
  } 
} else {
    echo "Sorry, no links were found!";    

}
?> 

The mysql_fetch_array in the if statement fetches the first record, so the do...while loop won't display the first record.

Thomas

[marcus1060]

It is a script all to its self, that is included through php onto another page.

[drawmack]

Acctually Tsinka was wrong you're not missing a record cause you do a do while loop however this may be marginally faster

<?php
$db = mysql_connect("localhost", "LOGiN", "PASSWORD");
mysql_select_db("links",$db);
if(($result = mysql_query("SELECT * FROM links ORDER BY RAND() LIMIT 10",$db)) !== FALSE)
  while ($myrow = mysql_fetch_array($result)) {
    printf("-<a href='%s' target='_blank'>%s</a><br> \n",$myrow["link"], $myrow["title"]);
  } ;
} else {
	echo "Sorry, no links were found!";	
}
?>

[herve]

If the table "links" has other columns then doing a

"SELECT link,title FROM links ORDER BY RAND() LIMIT 10 "

can be way more efficient than

"SELECT * FROM links ORDER BY RAND() LIMIT 10 "

[tsinka]

Hi drawmack,

I think that
if ($myrow = mysql_fetch_array($result)) {
in his first post fetches the first record. So the do...while loop will start with the second record ... correct me if I am wrong 🙂

Edit: forget it ... I got it. I was wrong 🙂

Thomas

[marcus1060]

Thanks after drawmack, and herves suggestions it loads alot faster.
Even though there seams to be a slit error in your code drawmack. But I fixed it.