Let's say you have "news articles" (rows in a table) and want a web page showing the latest ten articles about a particular topic.
Variants on "topic":
Variants on "news article"
Variants on "latest"
Variants on "10" - there is nothing sacred about "10" in this discussion.
Currently you have a table (or a column) that relates the topic to the article. The SELECT statement to find the latest 10 articles has grown in complexity, and performance is poor. You have focused on what index to add, but nothing seems to work.
First, let me give you the solution, then I will elaborate on why it works well.
Find the latest 10 articles for a topic:
SELECT a.* FROM Articles a JOIN Lists s ON s.article_id = a.article_id WHERE s.topic = ? ORDER BY s.sequence DESC LIMIT 10;
You must not have any WHERE condition touching columns in Articles.
When you mark an article for deletion; you must remove it from Lists:
DELETE FROM Lists WHERE article_id = ?;
I emphasize "must" because flags and other filtering is often the root of performance issues.
By now, you may have discovered why it works.
The big goal is to minimize the disk hits. Let's itemize how few disk hits are needed. When finding the latest articles with 'normal' code, you will probably find that it is doing significant scans of the Articles table, failing to quickly home in on the 10 rows you want. With this design, there is only one extra disk hit:
OK, you pay for this by removing things that you should avoid.
Rick James graciously allowed us to use this article in the Knowledge Base.
Rick James' site has other useful tips, how-tos, optimizations, and debugging tips.
Original source: http://mysql.rjweb.org/doc.php/lists
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.