Should I create a separate table for non null user_id values in my MySQL database schema for performance?

Question

I have a MySQL database schema for storing uploads, where each configuration can be associated with a user account (user_id) or uploaded as a guest without an associated user account (null user_id). The schema looks like this:

CREATE TABLE uploads (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT UNSIGNED,
    category_id TINYINT UNSIGNED NOT NULL,
    created_at BIGINT UNSIGNED NOT NULL,
    updated_at BIGINT UNSIGNED NOT NULL,
    s3_key VARCHAR(255),

    INDEX(user_id, updated_at),
    INDEX(user_id, category_id, updated_at),
);

Assuming 10 million uploads, 5 million guest uploads and 5 million user uploads

should I create separate table specifically for uploads associated with user accounts? Guest uploads will be queried only by the primary key (id) Goal is to maximize performance.

Answer 1

Tables don't have performance. They just sit there, storing data.

Queries have performance. The optimization strategy you choose should be based on the specific queries you need to execute optimally.

You haven't shown any specific queries, but you mentioned you will always query by id (the primary key).

There won't be any significant difference in performance between querying by id against one table with 10 million rows versus two tables with 5 million rows each.

Maybe it will be important if your database grows to the point where you have billions of uploads, but not in the shorter term.

Once your database grows that much, you will probably have to rearchitect a lot more than just the uploads table.

In performance consulting, we used to have a general guideline that for every order of magnitude (10x) of growth (either data size or traffic), you should re-evaluate your architecture, because you likely have performance bottlenecks that weren't present at the smaller scale.

Answer 2

Perhaps this is the appropriate approach. (I am not clear on your goals.)

• Create a Stored procedure with SQL SECURITY DEFINER (and define as 'root').
• In that, do whatever checking you need to allow the user/guest to get to an upload.
• After verification, perform the desired SELECT to get the upload. (Or deny access.)

Meanwhile,

• Give no one (except root) access to the upload tables. (This avoids anyone trying to sneak around the proc with random ids.)
• Give everyone EXECUTE "privilege". (Any other procs/functions should also be self-protecting.)