I have a MySQL table with about 10000 entries. I want to represent these entries sorted by name. The page should only show 20 entries at a time.
My question is, is it more efficient to
Let the database sort it, this means load corresponding 20 entries by using a sorting, limit, select query.
or should one rather
Sort the list of entries once, save them in an array in a file, load file and only look at the 20 indices of interest.
Both seems terrible to me. I do not want to sort a database with 10000 entries each time a user loads the page just to show 20 entries, nor do I want to load an array with more then 10000 entries to have access to corresponding 20 entries.
What makes things better? Speed, simplicity, versatility?
What happens if you save the file and then the table is updated? You will be missing some rows in your file. You also can't guarantee that storing the rows in a file is actually going to be faster. MySQL can get quite good at caching if the table isn't being updated much.
That being said, if speed is that important to you I would look at Memcached or Redis. Both of these are storage solutions for key-pair data in which the data is stored in memory. You could do something like this to implement memcached:
You can then use only the required indexes for your pagination. Keep in mind you would have to delete the cache every time the table is updated.
Is that much of a speed improvement required though? As your table gets more and more rows it will put more strain on PHP and may eventually lead to memory issues. You can quite easily use LIMIT and OFFSET to deal with this kind of thing and assuming your tables are indexed properly it shouldn't give much of a performance hit.
Asked in February 2016Viewed 3,589 timesVoted 11Answered 1 times