Is there any way to keep rows with same id sequential even if they are not stored sequentially? 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.
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 )