Handler_read_rnd_next at 3,852.74 M

[bfinke]

My website is running super slow and I cannot determine the reason. I think it has to do with sql. I noticed..

Handler_read_rnd_next is at 3,852.74 M

Can anyone tell if this is abnormal? In phpmyadmin it shows up as red.

Thanks.

[Fou-Lu]

Thats way too high. Way way too high.
Are you indexing you're tables properly? Its been awhile since I covered theory, and there are a few here that can probably tell you right off the bat, but numbers this high point towards mysql constantly needing to scan records instead of lookups via indexing if I'm not mistaken. That can of course show up a lot during fulltext searching too I'd suspect.

[bfinke]

Could you explain what indexing means or provide me with some links?

When I create tables I have a column for "id" that is the primary key and is auto_increment -isn't that indexing...?

[Fou-Lu]

Yes, primary keys are indexed. But they are only valuable if you're serching primarily for records based on a primary key field. I would presume you're searching on other criteria correct? Also, %criteria is a killer. Try to avoid using wild-carded starts. Endings should be much better since its indexing on strings will start at the first letter.
Links, lets take a look see...
Took a quick look at some of this one: http://aptoma.com/select.star/2008/06/26/mysql-indexes-to-mysql-what-green-kryptonite-is-to-smallville-universe-bizarro/ and it seemed to be pretty good. Its a little out of date, but valid enough. Take a gander at that one.

[bfinke]

Thanks...

So is it just a matter of setting primary keys in each table then in sql statements doing something like this...

select id from table where name = 'name';

instad of

select * from table where name = 'name'; ??

For the most part, I am already doing that.

[Fou-Lu]

While if you're only using the id, it is better to just query for that information, I don't believe that is affected by the keys (I'm also assuming that this is the problem here, but I could be way off the mark so I'm keeping my fingers crossed that the dusty little corner of my mind with the DBMS information is somewhat valid ;)). Assuming that id is you're primary key, you'll want to add an INDEX or UNIQUE on the name (I'd suspect unique, name sounds like something you'd use only once?). Essentially, if you plan on querying a table using a particular piece of information: id, name/username, emails, etc on a regular bases, index those values. On the other hand, you will waste space and a lot of it if you just index everything and don't make use of it. Its an awesome balancing act there, and my new position will require me to take care of this sort of stuff.

[bfinke]

Thanks again. So I need to learn how to use INDEX() in my sql statements?

[Fou-Lu]

From PHPMyAdmin its a matter of clicking the lightning boltish looking icon on a corresponding field. From command line its a simple ALTER TABLE table ADD INDEX (fieldname)

[bfinke]

Sorry to be redundant, I appreciate the help, but right now it says there's an index of PRIMARY. Should I remove this?

[bfinke]

Okay, I set all the tables to have an index for "id". The majority of my queries are more specific than "SELECT * ..." they are "SELECT id FROM..."


Before my Handler_read_rnd_next on mysql was at 3,000 MB, now its at 603MB.

That's improvement for sure... but it still sounds high. Is this still high?


I can't say for sure right when I made the changes I noticed the drop, it could be also that its later and my traffic is down now.

Are there any other factors that might create such a high Handler_read_rnd_next? I only get about 1,000 visitors a day and its simple CMS site, hardly any images whatsoever.

[Fou-Lu]

Thats a lot better (assuming the mysql server hasn't been reset).
Just keep optimizing you're tables and queries. I'm pretty certain that the number climbs as long as the service is running and queries are being executed that it finds need to cache. For example, I'm sitting at 5600 bytes from the looks of it after ~13 hours. The problem is when the number is staggering and the uptime is low.