MySql: table index speed vs consecutive row speed. Is there a big difference?

user_4630773 0
Viewed 45

I have a table UserRecord which has columns id, userId, recordType, recordCount, with INDEX on userId and auto-incrementing id. The table is to record different user behaviors and count the number of times of the behavior.

The create table looks like following: (Edit: I am not allowed to change the table structure due to project limitation)

CREATE TABLE `UserRecord` (
    `id` bigint(20) unsigned NOT NULL AUTO INCREMENT,
    `userId` bigint(20) unsigned NOT NULL,
    `recordType` int(3) unsigned NOT NULL,
    `recordCount` int(10) unsigned NOT NULL,
    PRIMARY KEY (`id`),
    KEY `userId` (`userId`)
);

I know in advance that there are 10 recordTypes (behavior types) in total, but some user behaviors are very unlikely so some users might not ever perform once such behavior. I am wondering whether I should generate all the 10 rows whenever a new user is created in order for the rows to have consecutive ids, or insert a record whenever it is first touched.

Method 1: I generate all the 10 rows when a new user is created, so the table size is always going to be 10 times the number of users, but every single user will have their records with consecutive ids.

Method 2: I generate a record only when a user first perform the recordType behavior. In this way the table on average will have size around 3 times the number of users, but records for the same user (of different types) might have ids very different, like 10000 and 1000000.

Now consider when I select the following query on a large scale:

select * from UserRecord where userId=x;

Remainder: userId has an INDEX on it. My question is, will the first method be significantly faster than the second, or not?

I would like to understand this from a theoretical aspect rather than experimental result.

indexing select mysql database
1 Answers

Two tables:

  • table with one row per user. This probably has an AUTO_INCREMENT PRIMARY KEY
  • table with one row per behavior per user. (Up to 10 times as many rows) PRIMARY KEY(user_id, behavior)

This gives the rows (up to 10) for user number 123:

SELECT ...
    FROM Users
    JOIN Behaviors  USING(user_id)
    WHERE user_id = 123

A LEFT JOIN ... IS NULL can provide a row of NULLs when you hit a missing behavior.

A PRIMARY KEY is an index and is "unique".

Unless your list of users includes everyone in a large country, I would not worry about performance.

For further discussion, please provide SHOW CREATE TABLE and tentative queries.

Locality of reference

The data in the table is in PRIMARY KEY order.

If the [up to] 10 rows for one user_id are inserted as [up to] 10 single-row inserts, then the rows will probably have consecutive ids (hence, adjacent in the BTree). At least they will have "close" ids.

If a user's rows are inserted as a multi-row ("batch") INSERT, then the rows will be adjacent. Also, such an insert will be on the order of 3 times as fast.

This forces ordering, eliminates a column, etc:

CREATE TABLE `UserRecord` (
    `userId` INT unsigned NOT NULL,
    `recordType` TINYINT unsigned NOT NULL,
    `recordCount` INT unsigned NOT NULL,
    PRIMARY KEY (`userId`, recordType)
);

And it cuts the disk space about in half.

By not going through a secondary key, SELECT ... WHERE userId = 1000 will be faster.

Again, batch INSERT is preferred for speed.

Even if you later add a row for a Type because its number is no longer zero, it will be "adjacent" to the other columns for the user. Hence, the SELECT ... WHERE userId = 1000 continues to be fast.

I recommend

INSERT INTO t
    (userId, recordType, recordCount)
    VALUES
    (?, ?, 1)
    ON DUPLICATE KEY UPDATE
        recordCount = VALUES(recordCount) + 1;

This takes care of the INSERT when it goes from 0 to 1 and takes care of incrementing by 1.

compare Methods 1 and 2

This may get into an analysis of disk activity and caching, so can you provide:

  • RAM size
  • innodb_buffer_pool_size
  • the current number of rows in the table
  • the current table size (in GB)
  • the anticipated number of rows when the table reaches its max
  • the various queries (INSERT, UPDATE, SELECT)
  • the relative frequency of those queries.

The analysis will go something like this:

  • Method 1 (repopulate with 10 rows per) -- bulkier, thereby more likely to be bigger than cache, thereby slower due to more I/O. OTOH, fetching the 10 will not take more I/O since they are clustered together.
  • Method 2 (the 10 are scattered, but not empty) -- smaller table, hence better caching. But SELECTing the 10 may need up to 10 disk hits.
  • Method 3 (my table and index improvements) -- better than both 1 and 2.

If it turns out that the table will never be bigger than innodb_buffer_pool_size (and there are no other big tables vying for cache space), then Method 1 is not worth doing.

Otherwise, the relative frequency of the queries will determine which of Method 1 or 2 will be faster on average.

Method 3 always wins.

Analysis

Assuming

  • 10M rows --> less than 1 GB for the table+indexes for any Method.
  • A cloud instance of at least 4GB --> The table will probably stay completely cached. --> very little I/O.

Conclusion

Methods 1 and 2 won't show much overall performance difference. The operations you mentioned will each take very few milliseconds for either Method.

user_17668310
Related