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.