MySQL - How should i index this table?
I have a table where user's tv shows library is stored and i would like to improve it
The table contains the following columns:
USER_IDSHOW_IDSTATUS
PROGRESS| Last episode watchedIS_FAVORITERATING
I have the following index:
USER_ID & SHOW_ID| Primary
USER_ID| Needed for foreign key
SHOW_ID| Needed for foreign keyUSER_ID & STATUSUSER_ID & IS_FAVORITESHOW_ID & STATUSSHOW_ID & IS_FAVORITE
I have about 20M rows, data weight about 750MB, index weight about 2GB in total about 2.75GB
I think that there are too many indexes but maybe they are really need to improve performance, should i leave it like that or should i remove the last 4 indexes and why?
mysql indexing
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
add a comment |
I have a table where user's tv shows library is stored and i would like to improve it
The table contains the following columns:
USER_IDSHOW_IDSTATUS
PROGRESS| Last episode watchedIS_FAVORITERATING
I have the following index:
USER_ID & SHOW_ID| Primary
USER_ID| Needed for foreign key
SHOW_ID| Needed for foreign keyUSER_ID & STATUSUSER_ID & IS_FAVORITESHOW_ID & STATUSSHOW_ID & IS_FAVORITE
I have about 20M rows, data weight about 750MB, index weight about 2GB in total about 2.75GB
I think that there are too many indexes but maybe they are really need to improve performance, should i leave it like that or should i remove the last 4 indexes and why?
mysql indexing
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
4
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
1
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23
add a comment |
I have a table where user's tv shows library is stored and i would like to improve it
The table contains the following columns:
USER_IDSHOW_IDSTATUS
PROGRESS| Last episode watchedIS_FAVORITERATING
I have the following index:
USER_ID & SHOW_ID| Primary
USER_ID| Needed for foreign key
SHOW_ID| Needed for foreign keyUSER_ID & STATUSUSER_ID & IS_FAVORITESHOW_ID & STATUSSHOW_ID & IS_FAVORITE
I have about 20M rows, data weight about 750MB, index weight about 2GB in total about 2.75GB
I think that there are too many indexes but maybe they are really need to improve performance, should i leave it like that or should i remove the last 4 indexes and why?
mysql indexing
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
I have a table where user's tv shows library is stored and i would like to improve it
The table contains the following columns:
USER_IDSHOW_IDSTATUS
PROGRESS| Last episode watchedIS_FAVORITERATING
I have the following index:
USER_ID & SHOW_ID| Primary
USER_ID| Needed for foreign key
SHOW_ID| Needed for foreign keyUSER_ID & STATUSUSER_ID & IS_FAVORITESHOW_ID & STATUSSHOW_ID & IS_FAVORITE
I have about 20M rows, data weight about 750MB, index weight about 2GB in total about 2.75GB
I think that there are too many indexes but maybe they are really need to improve performance, should i leave it like that or should i remove the last 4 indexes and why?
mysql indexing
mysql indexing
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
edited Dec 28 '18 at 11:36
Angel Vega
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
asked Dec 28 '18 at 11:09
Angel VegaAngel Vega
12
12
4
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
1
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23
add a comment |
4
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
1
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23
4
4
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
1
1
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23
add a comment |
1 Answer
1
active
oldest
votes
The size of your indexes sure seems quite large compared to the data.
However the answer to your question depends widely on the queries that you are running against this table. You should know your queries, so you can analyze them one by one : if you find an index that none of the queries use, then drop it.
If you don't know your queries, and you are running MySQL 5.6 or higher, you can use the PERFORMANCE_SCHEMA to access indexes IO statistics (since the last time the database restarted). This can be used to detect unused indexes, like :
SELECT
object_schema,
object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
See this discussion for more information. However be careful when using this method : you might have a monthly or quaterly query that relies on an oddball index, that might not show up on a short term analyzis.
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
add a comment |
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%2f53957574%2fmysql-how-should-i-index-this-table%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
The size of your indexes sure seems quite large compared to the data.
However the answer to your question depends widely on the queries that you are running against this table. You should know your queries, so you can analyze them one by one : if you find an index that none of the queries use, then drop it.
If you don't know your queries, and you are running MySQL 5.6 or higher, you can use the PERFORMANCE_SCHEMA to access indexes IO statistics (since the last time the database restarted). This can be used to detect unused indexes, like :
SELECT
object_schema,
object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
See this discussion for more information. However be careful when using this method : you might have a monthly or quaterly query that relies on an oddball index, that might not show up on a short term analyzis.
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
add a comment |
The size of your indexes sure seems quite large compared to the data.
However the answer to your question depends widely on the queries that you are running against this table. You should know your queries, so you can analyze them one by one : if you find an index that none of the queries use, then drop it.
If you don't know your queries, and you are running MySQL 5.6 or higher, you can use the PERFORMANCE_SCHEMA to access indexes IO statistics (since the last time the database restarted). This can be used to detect unused indexes, like :
SELECT
object_schema,
object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
See this discussion for more information. However be careful when using this method : you might have a monthly or quaterly query that relies on an oddball index, that might not show up on a short term analyzis.
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
add a comment |
The size of your indexes sure seems quite large compared to the data.
However the answer to your question depends widely on the queries that you are running against this table. You should know your queries, so you can analyze them one by one : if you find an index that none of the queries use, then drop it.
If you don't know your queries, and you are running MySQL 5.6 or higher, you can use the PERFORMANCE_SCHEMA to access indexes IO statistics (since the last time the database restarted). This can be used to detect unused indexes, like :
SELECT
object_schema,
object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
See this discussion for more information. However be careful when using this method : you might have a monthly or quaterly query that relies on an oddball index, that might not show up on a short term analyzis.
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
The size of your indexes sure seems quite large compared to the data.
However the answer to your question depends widely on the queries that you are running against this table. You should know your queries, so you can analyze them one by one : if you find an index that none of the queries use, then drop it.
If you don't know your queries, and you are running MySQL 5.6 or higher, you can use the PERFORMANCE_SCHEMA to access indexes IO statistics (since the last time the database restarted). This can be used to detect unused indexes, like :
SELECT
object_schema,
object_name,
index_name
FROM
performance_schema.table_io_waits_summary_by_index_usage
WHERE
index_name IS NOT NULL
AND count_star = 0
ORDER BY object_schema, object_name;
See this discussion for more information. However be careful when using this method : you might have a monthly or quaterly query that relies on an oddball index, that might not show up on a short term analyzis.
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
edited Dec 28 '18 at 18:19
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
answered Dec 28 '18 at 11:58
GMBGMB
6,6912520
6,6912520
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
add a comment |
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
Beware of detecting "Unused" queries -- You might have a monthly Delete that needs that oddball index. Remove the index and you will be in deep doo-doo.
– Rick James
Dec 28 '18 at 17:22
1
1
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
@RickJames : right, thanks for pointing it out, I added a warning in my answer. Anyway, IMO the best option is still to know which query you run so you can start your analyzis from there instead of the other way around
– GMB
Dec 28 '18 at 18:20
add a comment |
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%2f53957574%2fmysql-how-should-i-index-this-table%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
4
It depends on your queries, what are your most run queries, if those queries are using these indexes then leave them as is, otherwise remove
– Muhammad Bilal
Dec 28 '18 at 11:18
1
Indexing depends on queris which you are executing. Do you know your most common queries on this table?
– dgebert
Dec 28 '18 at 11:18
This is worth reading: use-the-index-luke.com
– O. Jones
Dec 28 '18 at 11:23
The two FK (single column) keys are redundant and can be removed. The FKs will pick up on the other indexes as needed.
– Rick James
Dec 28 '18 at 17:23