Physically cluster a MYSQL table for better performance

Question

I have a 30M row table, which is indexed by ID now. However, a lot of the requests are around a much smaller subset, which is identified by app_user=1

Does it make sense to physically cluster the table according to this? If so, how can I do so?

Answer 1

Does it make sense to physically cluster the table according to this?

I think it does make sense, depending on how you select your data. For example, if one of most important use cases for selecting the data is SELECT fields FROM tablename WHERE app_user = something then that will be more efficient if the selected records are all together, i.e. if app_user is the clustered index.

If so, how can I do so?

I think you Google "clustered index" for MySql.