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.
WHERE (MATCH(title, snippet) AGAINST("biden" IN BOOLEAN MODE))
ORDER BY datePublished DESC LIMIT 50