Mixing
New to SQL - speed up using indexing
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I'm new to SQL databases and have created a mySQL table with the following columns:
MJD (FLOAT) - may be duplicate
WAVELNTH (LONG) - one of ~10 values
EXPTIME (FLOAT)
DATAMEAN (FLOAT)
FLUX (FLOAT)
A typical entry is something like:
A typical entry in the database looks like this:
MJD 55329.017334,
WAVELNTH 131
EXPTIME 2.9
DATAMEAN 8.06
FLUX 2.78
In total the database has 157,502,333 entries.
My queries are almost excusively
SELECT (MJD, FLUX) FROM t
WHERE (MJD > value1 and MJD < value2 and WAVELNTH=value3)
Right now it takes about a minute to execute a single query. I'm very new to indexing so was hoping for some help in a) how best to add an index and b) how to then use that index in my query.
mysql sql
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
add a comment |
I'm new to SQL databases and have created a mySQL table with the following columns:
MJD (FLOAT) - may be duplicate
WAVELNTH (LONG) - one of ~10 values
EXPTIME (FLOAT)
DATAMEAN (FLOAT)
FLUX (FLOAT)
A typical entry is something like:
A typical entry in the database looks like this:
MJD 55329.017334,
WAVELNTH 131
EXPTIME 2.9
DATAMEAN 8.06
FLUX 2.78
In total the database has 157,502,333 entries.
My queries are almost excusively
SELECT (MJD, FLUX) FROM t
WHERE (MJD > value1 and MJD < value2 and WAVELNTH=value3)
Right now it takes about a minute to execute a single query. I'm very new to indexing so was hoping for some help in a) how best to add an index and b) how to then use that index in my query.
mysql sql
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
add a comment |
I'm new to SQL databases and have created a mySQL table with the following columns:
MJD (FLOAT) - may be duplicate
WAVELNTH (LONG) - one of ~10 values
EXPTIME (FLOAT)
DATAMEAN (FLOAT)
FLUX (FLOAT)
A typical entry is something like:
A typical entry in the database looks like this:
MJD 55329.017334,
WAVELNTH 131
EXPTIME 2.9
DATAMEAN 8.06
FLUX 2.78
In total the database has 157,502,333 entries.
My queries are almost excusively
SELECT (MJD, FLUX) FROM t
WHERE (MJD > value1 and MJD < value2 and WAVELNTH=value3)
Right now it takes about a minute to execute a single query. I'm very new to indexing so was hoping for some help in a) how best to add an index and b) how to then use that index in my query.
mysql sql
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
I'm new to SQL databases and have created a mySQL table with the following columns:
MJD (FLOAT) - may be duplicate
WAVELNTH (LONG) - one of ~10 values
EXPTIME (FLOAT)
DATAMEAN (FLOAT)
FLUX (FLOAT)
A typical entry is something like:
A typical entry in the database looks like this:
MJD 55329.017334,
WAVELNTH 131
EXPTIME 2.9
DATAMEAN 8.06
FLUX 2.78
In total the database has 157,502,333 entries.
My queries are almost excusively
SELECT (MJD, FLUX) FROM t
WHERE (MJD > value1 and MJD < value2 and WAVELNTH=value3)
Right now it takes about a minute to execute a single query. I'm very new to indexing so was hoping for some help in a) how best to add an index and b) how to then use that index in my query.
mysql sql
mysql sql
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
edited Jan 3 at 4:32
Tim Biegeleisen
237k13100160
237k13100160
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
asked Jan 3 at 4:17
Joe LlamaJoe Llama
275
275
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
I suggest the following index:
CREATE INDEX idx ON yourTable (WAVELNTH, MJD, FLUX);
The first two columns WAVELNTH and MJD, appear in the WHERE clause, and be used to potentially restrict the number of records which need to be considered in the query. It is not clear without more information what the order should be. Hence, the following index might be better:
CREATE INDEX idx ON yourTable (MJD, WAVELNTH, FLUX);
The reason why we include FLUX at the end of the index is to cover the values in that column. Since your select requests MJD and FLUX most likely any index would need to have both of these columns in order for MySQL to use the index.
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
FLUXgoes last, not sure about the order of the first two. Try both and see what works best.
– Tim Biegeleisen
Jan 3 at 4:38
|
show 2 more comments
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
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: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
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%2fstackoverflow.com%2fquestions%2f54016279%2fnew-to-sql-speed-up-using-indexing%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
I suggest the following index:
CREATE INDEX idx ON yourTable (WAVELNTH, MJD, FLUX);
The first two columns WAVELNTH and MJD, appear in the WHERE clause, and be used to potentially restrict the number of records which need to be considered in the query. It is not clear without more information what the order should be. Hence, the following index might be better:
CREATE INDEX idx ON yourTable (MJD, WAVELNTH, FLUX);
The reason why we include FLUX at the end of the index is to cover the values in that column. Since your select requests MJD and FLUX most likely any index would need to have both of these columns in order for MySQL to use the index.
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
FLUXgoes last, not sure about the order of the first two. Try both and see what works best.
– Tim Biegeleisen
Jan 3 at 4:38
|
show 2 more comments
I suggest the following index:
CREATE INDEX idx ON yourTable (WAVELNTH, MJD, FLUX);
The first two columns WAVELNTH and MJD, appear in the WHERE clause, and be used to potentially restrict the number of records which need to be considered in the query. It is not clear without more information what the order should be. Hence, the following index might be better:
CREATE INDEX idx ON yourTable (MJD, WAVELNTH, FLUX);
The reason why we include FLUX at the end of the index is to cover the values in that column. Since your select requests MJD and FLUX most likely any index would need to have both of these columns in order for MySQL to use the index.
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
FLUXgoes last, not sure about the order of the first two. Try both and see what works best.
– Tim Biegeleisen
Jan 3 at 4:38
|
show 2 more comments
I suggest the following index:
CREATE INDEX idx ON yourTable (WAVELNTH, MJD, FLUX);
The first two columns WAVELNTH and MJD, appear in the WHERE clause, and be used to potentially restrict the number of records which need to be considered in the query. It is not clear without more information what the order should be. Hence, the following index might be better:
CREATE INDEX idx ON yourTable (MJD, WAVELNTH, FLUX);
The reason why we include FLUX at the end of the index is to cover the values in that column. Since your select requests MJD and FLUX most likely any index would need to have both of these columns in order for MySQL to use the index.
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
I suggest the following index:
CREATE INDEX idx ON yourTable (WAVELNTH, MJD, FLUX);
The first two columns WAVELNTH and MJD, appear in the WHERE clause, and be used to potentially restrict the number of records which need to be considered in the query. It is not clear without more information what the order should be. Hence, the following index might be better:
CREATE INDEX idx ON yourTable (MJD, WAVELNTH, FLUX);
The reason why we include FLUX at the end of the index is to cover the values in that column. Since your select requests MJD and FLUX most likely any index would need to have both of these columns in order for MySQL to use the index.
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
edited Jan 3 at 4:24
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
answered Jan 3 at 4:20
Tim BiegeleisenTim Biegeleisen
237k13100160
237k13100160
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
FLUXgoes last, not sure about the order of the first two. Try both and see what works best.
– Tim Biegeleisen
Jan 3 at 4:38
|
show 2 more comments
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
FLUXgoes last, not sure about the order of the first two. Try both and see what works best.
– Tim Biegeleisen
Jan 3 at 4:38
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
It would be good to explain the reasoning behind using this index which has a different order of columns and why its better than the one posted
– George Joseph
Jan 3 at 4:23
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@GeorgeJoseph On a cell phone, with a particularly bad keyboard.
– Tim Biegeleisen
Jan 3 at 4:24
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
@TimBiegeleisen, thanks for your suggestion! I've edited the original post to show a typical entry. I'm also not sure why the ordering matters, and therefore how best to determine the ordering. Thanks again!
– Joe Llama
Jan 3 at 4:29
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
Please don't put my answer in your question.
– Tim Biegeleisen
Jan 3 at 4:32
1
1
FLUX goes last, not sure about the order of the first two. Try both and see what works best.– Tim Biegeleisen
Jan 3 at 4:38
FLUX goes last, not sure about the order of the first two. Try both and see what works best.– Tim Biegeleisen
Jan 3 at 4:38
|
show 2 more comments
Thanks for contributing an answer to Stack Overflow!
- 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%2fstackoverflow.com%2fquestions%2f54016279%2fnew-to-sql-speed-up-using-indexing%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
