Page 1 of 1

long executation

Posted: Mon Jul 16, 2007 2:55 am
by m2babaey
Hi
what is ther problem of this code:

Code: Select all

<?php
include'global.php';
$result=mysql_query("SELECT * FROM articles ORDER BY id LIMIT 20");
$row = mysql_fetch_array($result);
while($row){

echo $title=$row['title'],$name=$row['name'];
}
?>
It brings a long result after a long time ( so long that IE cannot browse it and I have to use firefox)
in addition, the result is not what I need. I need the title of 20 last post articles

Posted: Mon Jul 16, 2007 3:07 am
by volka
please try

Code: Select all

require 'global.php';

$query = 'SELECT Count(*) FROM articles';
$result = mysql_query($query) or die(mysql_error());
echo 'count: ', mysql_result($result, 0), "<br />\n";
$query = "EXPLAIN SELECT * FROM articles ORDER BY id LIMIT 20";
$result = mysql_query($query) or die(mysql_error());
while( false!==($row=mysql_fetch_array($result, MYSQL_ASSOC)) ) {
  foreach($row as $k=>$v) {
    echo $k,'=', $v, '; ';
  }
  echo "<br />\n";
}
and post the output.

Posted: Mon Jul 16, 2007 5:00 am
by m2babaey
this is the output:
count: 68
id=1; select_type=SIMPLE; table=articles; type=index; possible_keys=; key=PRIMARY; key_len=4; ref=; rows=68; Extra=;

Posted: Mon Jul 16, 2007 5:31 am
by volka
Because there is only one mysql_fetch_array before the while loop the script is fetching only the first record and processes it again and again and again ...

Code: Select all

<?php
include'global.php';
$result=mysql_query("SELECT * FROM articles ORDER BY id LIMIT 20");

while( false!==($row=mysql_fetch_array($result)) ) {
  echo 'title=', $row['title'], 'name=', $row['name'], "<br />\n";;
}
?>
You should also set an index for id, see http://www.w3schools.com/sql/sql_create.asp
But for 68 records it's not that important.

Posted: Mon Jul 16, 2007 8:49 am
by m2babaey
id is a primary key. is that enough?

Posted: Mon Jul 16, 2007 8:54 am
by volka
oops, yes that's enough.
I only saw the possible_keys=;
but not key=PRIMARY;