1

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?

  • Your query is missing an ON clause. Can you edit the question and fix it? – ypercubeᵀᴹ Feb 3 at 13:47
0

Look into PostgresQL with Timescale Extension:

https://docs.timescale.com/v1.1/using-timescaledb

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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