Speed Up Mysql Queries? Help please.

[atokatim]

I have a database with well over 350,000 records and was wondering if there was anything i can do to speed up how it reads from the database. i cannot remove any of these tables.

here is how i read from the database now.

<?
<?
include("_db.connection.php");
$song = "<song ";
$songs = "</songs>";
$folder=$_POST['folder'];
$xmlstring = "";
$limit = 40;

$member_result = mysql_query("SELECT filename, songname, bandname FROM images WHERE mid = '$folder' ORDER BY ord LIMIT $limit");
while($file = mysql_fetch_assoc($member_result)){
$url = $file["filename"];
$name = $file["songname"];
$band = $file["bandname"];
$xmlstring = $xmlstring . $song . 'name="'.$name.'" band="'.$band.'" file="'.$url.'"/>';
}
$xmlstringfinal = $xmlstring.$songs;
echo "&playurl=$xmlstringfinal&";
mysql_close($db);
?>

Thanks for the help in advance!

[NogDog]

Is the "mid" column indexed? Also, an index on the "ord" column may help the order by clause, as well.

[atokatim]

no, the ord is what a user set that song to be in a certain order their playlist.

[NogDog]

I'm talking about setting indexes on table columns to help optimize select query perforumance. See http://dev.mysql.com/doc/refman/4.1/en/indexes.html for information about it, and the create table and alter table syntax for setting indexes.

[atokatim]

the file_id is indexed. what should i index to make it faster?

[MarkR]

You should index:

1. Any column which you are searching on using =
2. Any column you're ordering by
3. Any column you're using in a join

Then you're finished.

You can have too many indexes, but it's difficult, generally speaking.

Mark

[atokatim]

i just tried that and my server load went from 3% cpu to 40% cpu. what may have went wrong?