Help using indexes

I am totally new to PHP & MySQL programming (Well 5 days into learning it) and have managed to create a database, put a few hundred records in it & get manageable data out, BUT.....

At the moment I dont have indexes being used. I estimate that at the end of importing data I will have roughly 3000 records. Each record contains the following data, but only 2 will normally ever be searched on - LName and Cemetery, and the results sorted by LName, Fname. The select line is this:

$sql = "SELECT ID, LName, FName, DOB, POB, DOD, POD, SLName, SFName, S2LName, S2FName, S3LName, S3FName,Cemetery, CemLocation, Image, ImageLoc FROM graves ORDER BY LName, FName";

At the moment I am reading through the entire database to get the results and dispolay them as I want:


$result = mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) {
If ($row["ImageLoc"]==$searchcem) {
DISPLAY BITS HERE
}

It works fine, but every page view reads all records, and considering there may be between 1 and 200 records which match $searchcem, out of the couple of thousand records, I thought it would be beneficial to use indexes.

I managed to create an index fine, but on looking on the net at a few resources, I cant actually see how to get the index to be used. I created an index with 2 fields - LName and Cemetery - I assume that is right as these are the only things I'll normally be searching by (If I decide on others, they wont be often used and I can wear the additional time taken to search all records).

The time taken to create the pages is still less than 0.01 seconds, so it's not putting a huge strain on the database server, but still, I think any reduction in time, and less load on the server has to be a good idea, plus I want to learn

Basically I'd like a pretty dumbed down explanation, or example, of how to get the SELECT FROM to use the index, or how else it should be done.

Thanks heaps. A demo of the page output can be seen here for those wondering what I am doing... It's not a plug for the site so mods can delete it if inappropriate for a 1st time poster to include a URL http://www.davidrawsthorne.com/familytree/cemetery.php?searchcem=woronora

 

You can just do $sql = "SELECT * FROM graves ORDER BY LName, FName";

As for indexes, your ID can be your primary key which is a type of "index", you may want to auto_increment it. Indexes are only needed if there is a WHERE clause which helps speed up, because it doesn't have to scan the whole table.

 

Hi, You can use EXPLAIN mysql statement to analyze your query

 

Thanks - I think I have it working OK now. Time to process is down a LOT, but since we are talking hundredths of a second it's still darn fast. I just have to translate the SQL query into something that works on the page, but the query (and explain) was rather interesting.