I have a few big tables but only a small portion is normally used for selects. However, it takes a long time to make the initial selects until the data is cached into memory (query cache or some other memory cache).
I noticed that MySQL creates 1 file per table, I feel this is quite bad for performance?
Is there any way to either move the most used rows into the start of the files or split the files so that the most used rows are in the same file?
I wouldn’t want to split the tables itself as I feel that’s a bad approach, the RDBMS should be able to handle that transparently?
I think my indexes are ok etc but it’s still very slow (initial queries).