Table Without Primary Key

[neoform]

Is there any speed penalty if I have a table with no primary key (but other indexes that get used instead) ?

[fenway]

Hard to imagine how you're working with a table without a PK... unless you truly have another unique identifier across multiple columns...

[neoform]

Well, for example a table that contains logs.

 

I never need to uniquely identify any one particular row, however I want to access the data based on fields such as date, so I have the date field indexed, but no primary key since that key would never be used, and therefore cause wasted cycles and ram.

[fenway]

I suppose so.. as long as your query will use some sort of index, you'll be fine... incidentally, you might want to look at the ARCHIVE table type as an alternative.

[neoform]

hmmmm, ARCHIVE looked cool till I read the part that says "No indexing is allowed".. I can't imagine this would work well in on an analytics table where i need to count hits based on date.

[fenway]

hmmmm, ARCHIVE looked cool till I read the part that says "No indexing is allowed".. I can't imagine this would work well in on an analytics table where i need to count hits based on date.

Really depends how much of your data your analyze at one time... if it's about 30% of the table, mysql won't use an index regardless, archive or not

[neoform]

I usually pull up a days worth of logs from a table that contains years worth of info..

[crtreedude]

Hard to imagine how you're working with a table without a PK... unless you truly have another unique identifier across multiple columns...

 

If it is merely logs, and you don't have to work with individual rows, it actually makes sense to have no primary key. Uniqueness must be checked during insert with a primary key, but with a simple index, no.

 

If one needs to update or delete individual records, you need a primary key, or uniqueness, otherwise, not really. For example, you can delete all records for a range of dates.