I'm currently developing a mobile app and using Codeigniter MySQL. I'm now faced with a situation where I have a table of books (this table will be 100k+ with records). With in this table I have a column called NotSelling. Example of db:
Book A 45
Book B 0
Book C 159
Book D 78
.
.
.
Book Z 450
Where above the numbers are what appears in the NotSelling column in the db. I need to extract the top 20 books from this large table. Now my solution to doing this is to sort the table and then just use TOP to extract the top 20 records.
What I would like to know is about the performance of sorting of the table. As I'm sure constantly sorting the table to simply get the top 20 results would take a hideously long time. I have been given solutions to the problem:
index the NotSelling problem.
cache the query (but I've read about coarse invalidation which may cause problems as my case the invalidation frequency would be high)
Sort the table take the top 20 records, place them in another table and then periodically just update the table say every hour or so.
But all this being said does anyone know of a better solution to this problem or have a way/method of optimizing the performance of the functionality I'm looking to do? Note I am a newbie so should anyone be able to point me in the right direction where I can read up about database performance I would really appreciate it.
解决方案
I think you are thinking too much here. Definitely a case of premature optimization. While all the above mentioned solutions are perfectly valid. You should know that 100K+ records is chowder to Mysql. We used to routinely order on tables with 30 million+ rows, with excellent perf.
But You MUST have index on the column being sorted on and double check your table schema. Reg. Caching too don't worry, mysql does that for you for repetitive queries when the table has not changed. But index on column is a must, primary and most important requirement.
641
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
