Kobashi Computing

This afternoon I was taking a look at one of my Mysql databases doing record counts and noticed a problem. What I was seeing as the actual record count using MySQL Query Browser didn't match with the auto incremented identifier column.

Mind you, its no fun looking at a database of some 100,000+ records to find this "bug". I say "bug" because its not really a bug but a strange anomaly.

My database is generated by doing a LOAD DATA LOCAL INFILE of about 40 stock quote CSV files. Loading each one of them results in no warnings or errors.

In one of my tables, I use an auto increment definition to store each stock quote id.


create table bars
(
id bigint auto_increment,
symbol varchar(10) NOT NULL,
period char(2) NOT NULL,
barDate date NOT NULL,
barTime time NOT NULL,
open float NOT NULL,
high float NOT NULL,
low float NOT NULL,
close float NOT NULL,
volume bigint NOT NULL,
adjustedClose float NOT NULL,
PRIMARY KEY (id)
);


One would think that MySql would have a contiguous number for this identifier without any gaps given that I loaded each CSV file one by one.

First, notice that a COUNT(*) gives me 115,414 records:

Then notice that MySql Query Browser also gives me 115,414 records (look at status line)

But look at the number for the last identifier in the list - 163,449

So the summary is, don't rely upon MySql to keep a contiguous range of identifiers even if you load a data infile in one by one.