0

I have a MySQL Database of 250,000 news articles. With time, it's size will continue to increase by at least 250,000 articles per year.

I'm doing full text searches on this database to find articles that have keyword matches (from user input) in the article titles and snippets.

For example, the query below searches the database for articles containing the keyword "biden". This database query takes 500ms. Some of my more complex queries which return X articles per publisher take longer, like 1800ms. I have a fulltext index made on the "title" and "snippet" column.

I'm trying to minimize the query time and get ahead of it now since this database will only get bigger with time.

My server is using an HDD, not an SSD. Would switching to an SSD speed up the database queries? I thought since I am using a fulltext index, this index is already stored in the server's RAM and an SSD won't make a difference.

Is the fulltext database query I'm performing the optimal way to search and query the database?

All input and advice greatly appreciated.

SELECT * 
FROM news
WHERE (MATCH(title, snippet) AGAINST("biden" IN BOOLEAN MODE)) 
ORDER BY datePublished DESC LIMIT 50
1
  • How big (GB) are the tables? (I would guess about 1GB per year.) How much RAM do you have? Are you also saving the entire article, or just a snoppet?
    – Rick James
    Dec 12, 2022 at 4:22

1 Answer 1

0

SSD are indeed faster than HDD, and if performance is important, it seems like an obvious upgrade. Likewise, SSD on an NVMe is even faster. And a RAID system using SSD devices can be faster still.

But storage of any type is still orders of magnitude slower than RAM.

In InnoDB, typically part of the index is in RAM at any given time. It has to compete for space in the buffer pool with other types of pages. Pages for the table rows, pages from different tables and the indexes of those tables, and other types of pages, like data structures InnoDB uses to manage the lifecycle of data.

If you run a search on a given index, maybe some of the pages for that index are in RAM, and maybe they aren't. Pages are loaded into and evicted from the buffer pool based on query traffic. Over time, an equilibrium point is reached so the buffer pool contains the most frequently-requested pages.

But if the set of most frequently-requested pages is greater than the size of RAM allocated to the buffer pool, then pages are repeatedly replaced, and then a moment later re-loaded from storage. In this event, faster storage will be helpful, but not as much as increasing RAM so that all the pages you need can just stay in the buffer pool and not have to be re-loaded often.

CPU speed is also important. A given MySQL query still executes in a single-threaded manner (though this is just starting to change in MySQL 8.0, and only for limited cases so far). Even if the whole index is in the buffer pool in RAM, the MySQL Server still has to scan that index using a single CPU core. RAM is fast, but a memory access still takes more than zero time. So upgrading the CPU can be beneficial too.

Another strategy is to use parallelism in different MySQL session to scan subsets of the data using multiple CPU cores. This means your application code becomes more complex, because you have to split the query work in a way that can be made parallel, then execute different queries in different threads, each accessing their own respective MySQL session. Then combine the results somehow once they return. MySQL does not do this for you — you would have to write application code to do it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.