How much will using a varchar(15) PK affect my tables performance?

Question

I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. Sadly, these timestamps are not always guaranteed to be unique. There is also a 2-6 (thus the VARCHAR) character hex ID which, when appended to the timestamp, is unique. I tested this out with ~400k records and just doing a SELECT * on the table takes over 15 seconds.

Before I go completely redesigning my table in some drastic way, I want to be sure that using this PK (as opposed to an auto-incrementing INT) is where my performance hit is. I have never really worked on a table using something other than a regular INT PK (I am a developer, not a DBA).

I am using InnoDB engine and a few FK relations to some small tables. MySQL admin is showing the data length of the table at ~150MB, and the index length at 21MB with 380k rows.

As I said, I'm a developer, not a DBA, but in my current situation I don't really have one I can bring in. I did some Googling but found a pretty wide array of answers that often delved into topics that just raised more questions for me. I'm hoping someone here can give a concise answer or at least point me to some more resources.

EDIT: Changed the column to CHAR(14) and removed one large TEXT column that was somewhat superfluous. This seems to have improved the time a good deal and took the table size down to about 80MB, but I'm still looking for suggestions.

Answer 1

I don't know that the PK is your issue. To me, doing a request for all 400k rows, 15 seconds doesn't sound too bad. PKs and indexes really come into the equation when you're trying to filter the set (using WHERE conditionals).

Answer 2

"I want to be sure that using this PK (as opposed to an auto-incrementing int) is where my performance hit is."

If you can recreate your table with an auto-incrementing primary key and test your parsing against this new table, you will find out for sure.

"Changed the column to char(14) and removed on large TEXT column that was somewhat superfluous. This seems to have improved the time a good deal and took the table size down to about 80MB, but still looking for suggestions."

Did you do both these changes in one step? Of course I could speculate that dropping a large TEXT column would decrease your I/O more than changing a VARCHAR(15) to a CHAR(14) but you can prove that for yourself - there is no better way to get a handle on performance issues than designing and running reproducible tests, and we learn loads in the process

Answer 3

As you and DTest mention, I'm not entirely sure that the PK is your bottleneck.

That being said, if you were to construct a new PK (for whatever reason) from the timestamp and that hex id I would suggest you make it an unsigned bigint (8 bytes) rather than char(14) (though even char(14) is better than varchar). It will fit even if you are leave it signed, but why not make it unsigned. More descriptive, looks more like ID, etc.

In order to maintain uniqueness you want to 'append' them rather than simply add them. So multiply the timestamp by 16^8. a shift of 8 hex digits to the left, and then add.

new PK = timestamp * 16^8 + oldId

Since you've got some room to spare, you can do your 'append' in decimal if you prefer. This way you can more readily 'see' each number separately afterwards. 10 digits for timestamp (16^8 = 4294967296 < 10,000,000,000) and 8 for the other id (16^6 = 16777216 < 100,000,000)

In that case, new PK = timestamp * 10^10 + oldId

Answer 4

SHOW CREATE TABLE ...; SHOW TABLE STATUS; SHOW VARIABLES LIKE '%buffer%'

If you are using utf8, then VARCHAR is not a good idea for a hex string. VARBINARY is better.

When you have a big column (TEXT, BLOB, etc), it is often better to compress that data before inserting it. This will cut down on I/O.

select * on the table takes over 15 seconds Sounds like I/O-bound.