redesign database around selecting rows by time range
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?
database-design datetime
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
add a comment |
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?
database-design datetime
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
add a comment |
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?
database-design datetime
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
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?
database-design datetime
database-design datetime
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
edited Jan 9 at 20:02
Jordan.J.D
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
asked Jan 8 at 16:45
Jordan.J.DJordan.J.D
1063
1063
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226602%2fredesign-database-around-selecting-rows-by-time-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Look into PostgresQL with Timescale Extension:
https://docs.timescale.com/v1.1/using-timescaledb
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 mins ago
Syed JafriSyed Jafri
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
answered 3 mins ago
Syed JafriSyed Jafri
11
answered 3 mins ago
Syed JafriSyed Jafri
11
11
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Syed Jafri is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |
add a comment |
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f226602%2fredesign-database-around-selecting-rows-by-time-range%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
