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
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?