I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine by date range like:
SELECT info.sessionID, info.otherstuff, t.time FROM info
INNER JOIN
(SELECT session.sessionID, session.time FROM session
WHERE session.time > :afterSomeDate AND session.time < :beforeSomeOtherDate) as t
ORDER BY t.time DESC
This causes an exponentially large bottleneck in query time as the date range increases. As I am completely redesigning the database, any suggestions are valid including different DB types. There is currently a 3 month RANGE partition on the time column, as well as an INDEX.
During my research I have done a few tests directly in the MySQL database. For instance a query to select every session in my table in a date range of 2 months, has a duration time of .453s and fetch time of 81s (returned 1.7 million rows). According to the accepted answer of this question, my bottleneck could really be the network connection between database and the application.
What else could I do to get a significant performance increase?
Best Answer
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb