MySQL Problem Please help

Anon

I've brought this issue up before but it hasn't been resolved yet. I have more details this time so please help if you can:

One of the table in my database takes a long time to access, sometimes the page that accesses the database does not even load. There are only 800 records in the database. The script seems to be clean as well. The table holds information for links, its basically a directory structure. When the database is restarted it works fine for some time and then becomes increasingly slow with time. The other tables in the same database is still accessible and works fine though. I've used php for the scripts to display the directory.

Now is this a problem with the script?
but then once it slows down, I can't even access the table through the command line.

A problem with the table?

I would assume its not a problem with the database since the other tables work fine.

the database does get a lot of traffic but I've increased the memory as well as the max connection for MySQL.

Note: the table has only 800 rows but it has had records up to 11000 or so and most of them have been deleted since the links are not validated. Would this be problematic?

Anon

If it doesn't work from the command line then it's a MySQL problem. What exactly is the select statement? Have you looked into indexing fields in the table?

Anon

the select statement basically selects all the fields that are required for display. About 7 fields. If it is a MySQL problem then why can I access the other tables without any problems or delay? And how will indexing the fields help? Thanks for the help.

Anon

It works perfect in MSAccess just like that, if MySQL doesnt like it its probably only a syntactical error, sorry im not particularly familiar with MySQL, you will have to figure that one out yourself.

I index the fields but allow duplicates.

Anon

Indexing the fields. Let's say you have a bookstore site and you have a table that contains the books. You site is organized by category; children’s, romance, computer, fiction, non-fiction, etc. You have all the books stored in one table but every time you access the table the select statement would include, "where category = '$some_category.'" In this instance, it might be wise to index the category field in table. What this does is create a separate table that has the placement of all the categories predefined. This way when you search the table for all books published by Sams in the computer section, it's only going to look in the main table where the category is computer. All the other fields won't even be looked at. This can speed things up.

In your case, however, it sounds like you have a problem that is too great to be solved by indexing. For a table with only 800 records, the difference will most likely be a matter of milliseconds.