Mysql – In a simple table with no complex indexes, does a large number of existing rows slow down an INSERT

bulkinsertMySQL

I have this small, simple table:

CREATE TABLE `ads_tracker` (
  `id` int unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `type` smallint unsigned NOT NULL,
  `reference` smallint unsigned NOT NULL,
  `date` timestamp NOT NULL,
  `action` enum('view','click') NOT NULL DEFAULT 'view'
) COMMENT='' ENGINE='InnoDB' COLLATE 'utf8_unicode_ci';

It has only an index as the Primary Key, but nothing else. Will there be any noticeable difference between inserting rows now (empty table), with say 100,000 rows, and say 1,000,000 rows? I figure UPDATEs will be slower, but I'd say that an INSERT could be pretty much just as fast, as there are no indexes except for the primary key. Or am I wrong there?

Best Answer

When you are mass INSERTs into an InnoDB table with just a Primary Key you are building up the gen_clust_index (a.k.a. Clustered Index).

Secondary Indexes are built in conjunction with the Clustered Index. Since you have no Secondary Indexes, this reload would be just fine.

I would high recommend doing two things when reloading:

Organize your INSERTs as extended INSERTs
Set bulk_insert_buffer_size to 512M or 1G

When you use the bulk insert buffer, it helps load the data into a treelike structure that makes it easier to load into a non-empty table.

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

Mysql – Are two indexes needed

Mysql – Finding rows for a specified date range

Related Question