Is there any way to keep rows with same id sequential even if they are not stored sequentially?

Question

Edit: I think i used the wrong word here. By "sequential" i don't mean that everything aid 1 enters has to be in the order of the time at which they enter it.

What i mean is that they should be clubbed together in the table that's it.

Suppose there are tables A and B.

A is linked to table B that stores data which may be repeated for a user. Each user may have 0 or 1 or 2... or n(limit) number of data is table B.

But the data is not entered sequentially because it totally depends on if/when the user chooses to enter it, how many data they enter.

So if user with aid 1 enters "lol" today, and then user with aid 2 enters ":)" tomorrow,and then 1 enters xd day after tomorrow then the data will be stored in the same manner i.e.

| bid        | data3       |
|:-----------|------------:|
| 1          |        lol  |
| 2          |        :)   |
| 1          |        xd   |

Is there a better way to do this? Maybe the way i am storing data is wrong.

Or maybe the table can to optimized in some way like making the rows with same id to be sequential i.e. one after the other such that there is no row with a different id between 2 rows with the same id.

Answer 1

The example below is meant for MS-SQL, although the concepts will apply for both MySQL (and really any RDBMS).

The effort you are going to go through to keep TableB with all of the same users data physically close to each other won't be worth it or matter to the engine very much. Instead, you should have an index on TableB with the foreign key to TableA as the first column. Then any query against TableB (ex: give me all data for User X) will be fast.

In the example below, I have a users table and a Posts table. The Posts table has a Foreign Key reference to the User table. In addition, there is an index on the Posts table for UserID and PostID. Queries against the Posts table will be extremely fast when looking for specific users. I also won't need to continually re-order the Posts table in order to get this performance.

CREATE TABLE dbo.Users
    (
    UserID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , UserName VARCHAR(100) NOT NULL
    )

CREATE TABLE dbo.Posts 
    (
    PostID INT NOT NULL PRIMARY KEY IDENTITY(1,1)
    , UserID INT NOT NULL REFERENCES dbo.Users (UserID)
    , DateAdded DATETIME NOT NULL DEFAULT SYSDATETIME()
    , PostData VARCHAR(MAX) NOT NULL
    )

CREATE NONCLUSTERED INDEX IDX_Posts_UserID_PostID ON dbo.Posts (UserID, PostID)

Answer 2

In rare cases the storage order of the data does matter for performance. By "rare" I mean less than maybe 2% of tables benefit enough to bother doing anything.

Here's a use case where clustering consecutive rows 'together' (or at least 'close') could lead to significant decrease in I/O (sometimes 10-fold).

• The table is too big to be cached in RAM in InnoDB's buffer_pool -- hence the potential for being slowed down by I/O.
• The main queries do a range scan on whatever controls the data order. For InnoDB, that is a range scan on the PRIMARY KEY.
• Or the main queries are otherwise "close" because of a 2-column PRIMARY KEY. Think of a messaging system and PRIMARY KEY(user_id, message_id) and the user is jumping around in his messages.

In one application where message_id is AUTO_INCREMENT, changing from

PRIMARY KEY(message_id),
INDEX(user_id, message_id),
INDEX(user_id, ...)

to

PRIMARY KEY(user_id, message_id)  -- clustered primarily on user_id
INDEX(message_id),    -- sufficient for AUTO_INCREMENT
INDEX(user_id, ...)   -- benefits, too

nearly doubled the capacity of the server.

WordPress's wp_postmeta is an example of where this can be beneficial. But I would say that less that 2% of such instances are big enough to matter. Still, there are various reasons to change from

  PRIMARY KEY (meta_id),
  INDEX(post_id),
  INDEX(meta_key)

to

  PRIMARY KEY(post_id, meta_key),
  INDEX(meta_key)

More discussion: http://mysql.rjweb.org/doc.php/index_cookbook_mysql#speeding_up_wp_postmeta . (This link also discusses what to do if you really need to keep meta_id.)

That brings up another topic. Should the PRIMARY KEY always be a surrogate AUTO_INCREMENT? In my experience, two-thirds of tables have a perfectly good "natural key" (sometimes composite) that could be used instead. Wp_postmeta is one of many examples where the surrogate gets in the way of performance.

Semi-related: A huge table with a UUID index is hopeless for performance. Clustering won't help because you never do a 'range' scan and two UUIDs are rarely 'near' each other. (A partial workaround: http://mysql.rjweb.org/doc.php/uuid )

Answer 3

The answer depends on what operations you can live with

TABLE ORDER DOES NOT MATTER

You should index table B

ALTER TABLE B ADD INDEX bid_data3_ndx (bid,data3);

Then, your queries will do index lookups and index scans instead of worrying about table order.

If table B only has bid, data3 and some auto_increment value, this is all you will need.

TABLE ORDER DOES MATTER

You still need to index the table as mentioned above.

If you want to eek out a little more performance and table B is small enough, you can redo the physical order of the table, then reindex.

ALTER TABLE B DROP INDEX bid_data3_ndx;
ALTER TABLE B ORDER BY bid,data3;
ALTER TABLE B ADD INDEX bid_data3_ndx (bid,data3);

Try this once. If the performance gain is worth it, you will have repeat this step periodically.

At some point in the future, you will stop seeing a performance gain doing this step. Once that happens, you will never need to redo the table's physical again. Just work solely with the index.

BEST ADVICE : Just index the table

Answer 4

Why do you assume that "i assumed that if they are sequential then they are optimized and retrieving them would take less time"

In a relational database, tables are relations, and relations have no order. You can't control physical order on the disk, nor should you care. There is nothing 'optimized' about rows that 'seem to be' stored sequentially, and that is 10X true when the underlying storage is an SSD, or a RAID array (or both), which introduces deliberate physical fragmentation by storing fragments of the data on different drives.

The whole idea of 'optimized sequential' storage dates back to the 1950's, when data was stored on magnetic tapes that spin slowly. Like many other wrong ideas, it still lingers on in the minds of many, although it is irrelevant.

If you are experiencing some performance issue, spell it out, and someone can probably help. If you are trying to optimize your system in advance, forget about physical data order, and focus your efforts on getting the data model and queries and indexes right. That's all you should care about, and that will bring you orders of magnitude more benefit than worrying about physical storage. Let the RDBMS engine handle that for you. It is optimized to do so.