I need to display on the page a huge table with a bunch of columns, the data of which is pulled from different tables. I think all this will be cached in one table and updated on the crown once a day. The question is whether PK (primary key)? I know for sure that there will be no references to it from any tables (why?). Would this not affect performance?

And one more question: what about indices? I have never used them before and I do not understand them well (although I’ve already read the wiki). There are many columns, search / sorting will be possible for each of them and almost all of them are textual (except for dates that are stored in the number of the TIMESTAMP type so that you can sort them too). What should I hang on the index for each column? Or one but compound for all?

  • If in the selection condition there are no columns with which the index starts, it cannot be used. so if the conditions are separate, then the indices will have to be separate. but at the same time it will take up a lot of space and the operation of inserting records will be longer than if there were no indexes. If you do not intend to delete or modify specific entries (what should be done on a unique field, such as a primary key), then the key is really not needed - Mike
  • Or maybe you don’t need a separate table, but it’s enough to create an appropriate view (VIEW)? - Yaant
  • @Mike, I will not change / delete anything, most likely I will, with a general update, simply truncate the table and re-fill it, and the selection will be all at once, only in blocks of N-records so as not to kill the server with a request for several thousand records (through offset / limit, for the same kind of PK is not needed?). And what about the indexes, I did not understand all the same? I do not care for the insert - it will be invisible to the user when updated once a day. The main thing that the sample worked quickly (including search and sorting). - PECHAIR
  • one
    @DarkByte I’m aware of this syntax, but besides the syntax itself, I’m aware of how the database handles such a request inside, so she first reads 20 records from a disk, then the first 10 of them just throws out (does not use), but the remaining gives to the client. Those. the difference in the load from such a request and from a request without limit only in that the records do not go across the network from the server to the client. But the load on the disk remains (and the disk is usually the main bottleneck in any database) - Mike
  • 2
    And unfortunately, most developers who do pagination do not think about such features of the database. Yes, and without limit it is impossible to make a universal solution. Any pagination solution that should work quickly should be made for a specific case, based on the specific situation (for example, ruhighload.com/post/Paging+using+MySQL ). Once you decide to make a cache, this is a very good reason to introduce such a solution with it, because this is a rare situation where you can afford to number the records in the right order and not worry about skipping numbers during deletions. - Mike

0