indexing for max(ID) ?

Mandakins

I have a database with almost 38,000 records and I'm showing # of records in the DB on the index page of my site. However, counting that many records seems to be slowing down the load time. Granted - only by 2 seconds, but I'd like the index to pop up really fast.

I'm also trying to count unique records for a certain field (surnames) on the same page.

See here for example: www.deadfred.com

"The Archive contains 10276 Surnames & 37846 Records."

I realize this page has other items that are slowing down load time, but I have tried removing the news feeds and outside links, and the page still loads slow unless I remove the counting code.

Anyone have any ideas as to the best way to speed things up and still keep my record count and unique surname count?

-A

eoghain

How are you counting these records?

I'm hoping that you are doing this with queries i.e:

SELECT COUNT(*) FROM myTable;

SELECT surname FROM myTable GROUP BY surname;

For the second query you just need to know the number of rows returned to get your surname count.

Now if you aren't using queries like I've described above then the first thing you should do is change your code to do things this way.

If you are using queries like those above then you'll need to run them against your database and check the EXPLAIN of what is happening to see where your slowdown is. Then you are going to have to either adjust your queries (if you have joins and the like) or add indexes to your tables.

Mandakins

Yes, I'm querying and counting results:

$sqlresult =mysql_query("SELECT DISTINCT surname FROM $database",$connection)
or die("Couldn't run query.");
$surnames = 0;
$surnames = mysql_num_rows($sqlresult);
echo $surnames;

$sqlresult =mysql_query("SELECT * FROM $database",$connection);
or die("Couldn't run query.");
$records = 0;
$records = mysql_num_rows($sqlresult);
echo $records;

I've heard about indexing and how it is supposed to help speed things up, but I don't know how to implement it.

Since the surname query has to collect DISTINCT results, it is probably taking longer than it needs to.

Also, since I really just need the info in the ID field for the last record in the DB to get # of records, I should probably just grab that number and not have to run through the whole database right?

How can I do that efficiently?

Thanks,

eoghain

Here is your problem. You are selecting alot of data and returning it from the database when you don't need to. You should change your queries to use the count() function like mine do. This puts more work on the database, but returns less data which is where your slowdown is probably happening.

This is the section that is probalby causing the greatest amount of slowdown:

$sqlresult =mysql_query("SELECT * FROM $database",$connection); 
or die("Couldn't run query."); 
$records = 0; 
$records = mysql_num_rows($sqlresult); 
echo $records;

Is there any reason for you to return all fields from this table? Are you using them, or are you just doing this for the count?

Mandakins

Right. That's exactly it. I just want the count, not all the records. How do I do that?

$sqlresult =mysql_query("SELECT Count(ID) FROM $table",$connection);
while ($row= mysql_fetch_array($sqlresult)) {
$records = $row['Count(ID)'];
}
echo $records;

Or is there a faster way?

Thanks for helping me out on this.

eoghain

You'll want to do this:

$sqlresult =mysql_query("SELECT Count(*) AS cnt FROM $table",$connection); 
while ($row= mysql_fetch_array($sqlresult)) { 
$records = $row['cnt']; 
} 
echo $records

Using count(*) means to count all rows. If you did count(ID) you might not get a count of all rows, especially if you had duplicate ID's.

Mandakins

Oh yeah, that shaves some significant load time.

Any way to speed up my distinct surname count?

You have been soooo helpful. Is there anyplace on here I can give you gold stars or something? ;-)

eoghain

No they don't do any rankings here, but I'm glad I could help.

The only way to speed up your surname query is to add an index on surname to your table.