mySQL table, primary key

Question

I have a table which is used for two purposes. One purpose is for calculation that is done every 5 seconds by running a PHP cron job. For this purpose I need a primary key that is a combination of about 5 fields. With this primary key the cron job runs really effectively and really fast. Then there is the second purpose of the table and that is to retrieve data to display on the web-page after user signs in. For that purpose, totally different primary key would be needed. The one that I use for cron makes it slow. I am tempted to create two tables with identical fields and data but with different primary key. I know it would add a lot of overhead but the website will be really quick and responsive. Is that something that would be recommended?

Answer 1

You can create an index on whatever combination of fields you need.

In general, I prefer an auto-incremented integer primary key on tables. Very useful.

You can have such an index and then build two more indexes on the other columns

create index table_col1_col2_col3_col4_col5 on table(col1, col2, col3, col4, col5);

for the first index. If you like, you can make this a unique index and the database will then enforce uniqueness among rows for these five columns. Then you can create another index for surfing the table in another way:

create index table_col6_col7 on table(col6, col7);

This can be used for retrieval.

There is some overhead to maintaining the indexes on insert/update/delete operations. You would want to test in your environment to see if this is a problem (typically it is not).