[aprssig] High speed datbase inserts (CGI)

[Steve Dimse]

On 3/1/05 at 6:26 AM AE5PL Lists <HamLists at ametx.com> sent:

>Use minimal indexes.  Indexes are more performance problematic than most
>other factors for databases which are mostly added to instead of
>searched.
>
I agree unused indexes are bad, but it is important to have the indexes you will
use. As table size goes up, the cost of a query that is not covered by an index
goes up, it means reading the entire table to answer a query. 

How bad this affects you in MySQL depends on another character of the table,
whether there is free space inside the table. If you delete records future
inserts will reuse that space, but until it is used up, the table cannot do
simultaneous reads and writes. So if you need to do a full table read, all
writes are locked out until the read completes, for a gigabyte sized table that
can be a while.

With no free space, you can have many reads and one write occuring
simultaneously in a table, this works because any new data being added is added
at the end of the table and can be picked up by the reads. If new records can be
inserted in the middle of the table, then reads are held up while a write is
executed.

You can get around this by either optimizing tables when deletes are performed,
(though be aware UPDATE can also generate free space when the records are not of
fixed length), or using a separate deleted field to exclude deleted records from
a query without actually deleting them from a table. If your tables are large,
OPTIMIZE can be more trouble than it is worth, as it locks out all reads and
writes. findU's 2004 weather table took 18 hours to optimize, it is 8.6 GB, and
of course a lot of other things were going on in the machine at the same time.

So yes, each index adds overhead to every insert operation, but a query not
covered by an index is also not a good thing, so the indicies should be
carefully chosen...

Steve K4HG