30,000 rows needs weekly ANALYZE or SELECT is slow?

What's going on?

[whoisgregg]

I have a MySQL MyISAM table with just under 30,000 rows that takes up 9 MB data/7 MB index. This table is only 3 months old. About a month ago our intranet home page was taking almost 10 seconds to load. That page generates statistics based on the data in the affected table. 3 selects, one with COUNT().

Turned out that running ANALYZE TABLE [dev.mysql.com] on this table caused the page to load quickly again. Since then, I've had the same problem and fixed it with the same command 3 times. It takes about a week for the problem to present itself.

I have many other tables that have been running much longer, but this is the largest and fastest growing MySQL db I've used. Is this normal to need to run this command? Does this sound like a symptom of a different problem? Any input would be appreciated as this is my first problem with a MySQL db.

[FalseDawn]

[dev.mysql.com...]
might be of use

Specifically 7.4.7

You can probably set up a cron job to automatically update table stats (daily?), but otherwise (as you have found), just remembering to manually update the tables regularly is as good a solution as any.

Having said all that, 10 secs for 3 or 4 queries on a 9MB table sounds excessive. Have you added indexes as necessary for these queries and have you used the EXPLAIN statement to see what's going on?

[whoisgregg]

I'll have to go through that link -- looks like great information. :)

I could do the cron job, but I like that it's in "real time" as they say. I imagine even running it every 15 minutes would be good enough.

However, my real concern is that something is "wrong" and my preference would be to cure the cause, not work around the symptoms.

All my indexes are in place. When it works, it works fast. EXPLAIN statement? ::blush:: I had to look that up, so no, I haven't run that. I'll do that and get back with that info.