Could not run query: Too many keys specified; max 64 keys allowed

Hello Im Not Sure This Is Where To Post This

but I need some help

I am running a php dating site

http://abmatch.com

It was working fine until the database became larger like 7000 members

the people who wrote the script installed a php indexing file for me

db_index3.php .... it went through and indexed the database

After this was done the home page loaded super fast again....

but now it is slow again and when I run the db_index3.php file it comes up with this error:

Could not run query: Too many keys specified; max 64 keys allowed

so it can not index everything it needs to.... can someone please explain a little better how these keys are used????

could I undo the indexing of the database and re-run the indexing script??

Please anyones help on this would be greatly appreciated....

Paul

abmatch.com

What you find you will probably need to do is analyse why the database is running slowly, and also take a look at the db_index3.php file to see what keys they have added to the databases.
The "SHOW STATUS;" command will get you off the ground. What you need to be looking out for is the "slow_queries" number and after that the cache and key variables under "SHOW VARIABLES;". You also need to be aware of other load on the server. If this database on a seperate machine or does it share facilities with other services?

You potentially want to run "OPTIMIZE TABLE" and "ANALYZE TABLE" on your table in the db to make sure that the keys are in good order. This should probably be run regularly.

Aside from saying these things I would recommend talking to someone who knows databases... ;-)

To explain indexes: they are essentially binary trees or hash tables (but can be other sorts) which allow quick referencing of information inside a table in a DB. If you have an index on a column that is regularly used as a search term you will find performance improves. If you don't have an index on a search term (usually defined by a WHERE clause) there is a greater possibility that the database will be doing a full table scan to retrieve its needed results. In the example of your members table being 7000 records there is a good possibility it will take a LONG time to scan the whole table to find certain records if they're not indexed. Worst case scenario being that it scans all 7000 records.