Need Help Speeding Up SQL Queries

[CrimsonDemon]

Hello, I'm currently rewriting some code to speed it up, make it more portable, and more secure. I want to display a list of all members in a table, simple enough. However, the problem happens when I've got 300+ "active" users, my queries seem to be very slow when I'm not running my code locally. Here's the code:

function dispMembers() {
	global $session;
	$result = mysql_query("select * from ranks order by num desc");
	$row = mysql_fetch_array($result); 
	$largest = $row['num']; 

echo ("<center><p>Members</p><table border=1 width=50% id=table1 bordercolor=#000000 cellspacing=0>
<tr>
	<td>Rank</td>
	<td>Username</td>
	<td>Email</td>
</tr>");

$i = 0;
for($i=$largest; $i>=1; $i--) {
	$result = mysql_query("SELECT * FROM members WHERE rank = $i and disable != 1");
	while($row = @mysql_fetch_array($result)) {
		extract($row);
		echo("<tr><td><img src=" . rankImg($rank) . "></td>
		<td><a href=profile.php?PHPSESSID=$session&user=$username>$username</a></td>
		<td><a href=mailto:$email>$email</a></td></tr>");
	}
}
}

It basically looks for the newest row ($largest), and echos out all the info from 1 to $largest. Is there anyway I can speed this up? Is it possible to restrict which columns it will look up, because I only really need rank, username and email?

Thanks in advance.

[mrhappiness]

SELECT rank, username, email
FROM members
WHERE disable != 1
ORDER BY num DESC

what about that?

[swr]

Doing an SQL query inside a loop is usually a bad thing, and usually avoidable. mrhappinesses'ises (wtf is the apostrophe rule for words ending in ss?) query would avoid that. You could use * instead of specific fields if you want, and I think he meant "order by rank" rather than "order by num".

Also, if you have a lot of disabled users, you can help mysql skip them by using an index:

alter table members add index (disable)

[CrimsonDemon]

Thanks a lot! I just ran 1000 iterations of ApacheBench on the member display page, and it went from 30 requests a second, to 45! I'm very happy with those results 😉

About indexing, I don't know much about it, but from what I understand it just predetermines where things are in the table to help make future requests faster. Does this mean I could just index everything? I do a lot of WHEREs based on username, so would it be useful for me to also index that?

[ahundiak]

As a general rule index any field that will appear in a WHERE or ORDER BY or GROUP BY clause.

You don't want to go wild and index everything as that slows up inserts and makes your database bigger. But certainly username should be indexed if you look up information by username.