HUGE TABLE, need help querying

Anon

I have a mysql database with a table in it that currently has over 4 million entries. This table will always contain from 0 to 6 million entries and it is deleted each month. The problem is, I am having a terrible time querying that particular table in a timely manner within PHP. It currently has 7 fields with 4 of those 7 being primary keys and one of those 4 being an "auto-increment" field. What can I do to help speed up my queries??? Thanx in advance.

Anon

I just read about Indexes for a brief period. I am pretty sure that is what I need to use, BUT it is saying something about needing to be updated as well...is this done dynamically, or do I actually need to write some code to do this. Also, is it wise to make every field I have in a where clause an index???? thanx again.

Anon

4 primary keys? You can have only one primary key per table.. Your auto-increment column should be the primary key.

Mysql documentation has help on indices. Generally, you will want to create indices on columns of high cardinality. Don't bother indexing lots of repeated columns.

Anon

Actually, the 4 columns combined = primary key. and the auto-increment is one of them. yes or no? or should it only be the auto-increment column.

Anon

Try to make your indices as small as possible. If the auto-increment is UNIQUE, use it alone as primary key.