MySql how to optimize this select by date query to use an index?How to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?How to get a list of user accounts using the command line in MySQL?MySQL - UPDATE query based on SELECT QueryHow to reset AUTO_INCREMENT in MySQL?How to get the sizes of the tables of a MySQL database?MySQL: Can this Join query be optimized?How do I import an SQL file using the command line in MySQL?How to optimize query with date calculationHow to fix slow MySQL CURDATE Group by Query
Is there such a thing as too inconvenient?
!I!n!s!e!r!t! !n!b!e!t!w!e!e!n!
Writing/buying Seforim rather than Sefer Torah
E: Sub-process /usr/bin/dpkg returned an error code (1) - but how do I find the meaningful error messages in APT's output?
Convert HTML color to OLE
Are required indicators necessary for radio buttons?
Does C++20 mandate source code being stored in files?
Is this kind of description not recommended?
Does Denmark lose almost $700 million a year "carrying" Greenland?
Why don't sharp and flat root note chords seem to be present in much guitar music?
Unsolved Problems (Not Independent of ZFC) due to Lack of Computational Power
Nuclear decay triggers
Was Switzerland really impossible to invade during WW2?
Do predators tend to have vertical slit pupils versus horizontal for prey animals?
How does the Saturn V Dynamic Test Stand work?
What is the latest version of SQL Server native client that is compatible with Sql Server 2008 r2
Is "stainless" a bulk or a surface property of stainless steel?
Sort, slice and rebuild new object with array data
Are there reliable, formulaic ways to form chords on the guitar?
Is a butterfly one or two animals?
What can I do to keep a threaded bolt from falling out of it’s slot
Does git delete empty folders?
Is there any road between the CA State Route 120 and Sherman Pass Road (Forest Route 22S0) that crosses Yosemite/Serria/Sequoia National Park/Forest?
Do living authors still get paid royalties for their old work?
MySql how to optimize this select by date query to use an index?
How to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?How to get a list of user accounts using the command line in MySQL?MySQL - UPDATE query based on SELECT QueryHow to reset AUTO_INCREMENT in MySQL?How to get the sizes of the tables of a MySQL database?MySQL: Can this Join query be optimized?How do I import an SQL file using the command line in MySQL?How to optimize query with date calculationHow to fix slow MySQL CURDATE Group by Query
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;
I have a table with about 1M records in it, with several keys; customer name and invoice date, being two of the keys.
I then have a query that I use to retrieve and group data. When I run explain on the query, it seems like it's not using my invoice date index. How do I optimize it?
The following is the query:
SELECT
business_unit as 'Service Center',
fixed_customer_name AS 'Customer',
trade as 'Trade (system)',
fixed_trade as 'Trade',
state as 'State',
Last_day(invoice_date) as 'Month Ending',
SUM(revenue) AS 'Revenue',
(SUM(revenue) - SUM(cogs)) AS 'GP',
COUNT(fixed_customer_name) as 'Ticket Count',
fixed_job_type AS 'Type'
FROM Invoice_new
WHERE
invoice_date >= '2018-01-01'
GROUP BY
business_unit,
fixed_customer_name,
trade,
fixed_trade,
state,
fixed_job_type,
Last_day(invoice_date)
Order by
invoice_date,
fixed_customer_name,
fixed_trade
mysql
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
add a comment |
I have a table with about 1M records in it, with several keys; customer name and invoice date, being two of the keys.
I then have a query that I use to retrieve and group data. When I run explain on the query, it seems like it's not using my invoice date index. How do I optimize it?
The following is the query:
SELECT
business_unit as 'Service Center',
fixed_customer_name AS 'Customer',
trade as 'Trade (system)',
fixed_trade as 'Trade',
state as 'State',
Last_day(invoice_date) as 'Month Ending',
SUM(revenue) AS 'Revenue',
(SUM(revenue) - SUM(cogs)) AS 'GP',
COUNT(fixed_customer_name) as 'Ticket Count',
fixed_job_type AS 'Type'
FROM Invoice_new
WHERE
invoice_date >= '2018-01-01'
GROUP BY
business_unit,
fixed_customer_name,
trade,
fixed_trade,
state,
fixed_job_type,
Last_day(invoice_date)
Order by
invoice_date,
fixed_customer_name,
fixed_trade
mysql
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
Why are you grouping by so many fields? TheGROUP BYandORDER BYare what's slowing the query down.
– Rocket Hazmat
Mar 27 at 14:54
1
An index can improve the performance of your query if the percentage of rows the filtering predicateinvoice_date >= '2018-01-01'is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?
– The Impaler
Mar 27 at 15:16
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38
add a comment |
I have a table with about 1M records in it, with several keys; customer name and invoice date, being two of the keys.
I then have a query that I use to retrieve and group data. When I run explain on the query, it seems like it's not using my invoice date index. How do I optimize it?
The following is the query:
SELECT
business_unit as 'Service Center',
fixed_customer_name AS 'Customer',
trade as 'Trade (system)',
fixed_trade as 'Trade',
state as 'State',
Last_day(invoice_date) as 'Month Ending',
SUM(revenue) AS 'Revenue',
(SUM(revenue) - SUM(cogs)) AS 'GP',
COUNT(fixed_customer_name) as 'Ticket Count',
fixed_job_type AS 'Type'
FROM Invoice_new
WHERE
invoice_date >= '2018-01-01'
GROUP BY
business_unit,
fixed_customer_name,
trade,
fixed_trade,
state,
fixed_job_type,
Last_day(invoice_date)
Order by
invoice_date,
fixed_customer_name,
fixed_trade
mysql
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
I have a table with about 1M records in it, with several keys; customer name and invoice date, being two of the keys.
I then have a query that I use to retrieve and group data. When I run explain on the query, it seems like it's not using my invoice date index. How do I optimize it?
The following is the query:
SELECT
business_unit as 'Service Center',
fixed_customer_name AS 'Customer',
trade as 'Trade (system)',
fixed_trade as 'Trade',
state as 'State',
Last_day(invoice_date) as 'Month Ending',
SUM(revenue) AS 'Revenue',
(SUM(revenue) - SUM(cogs)) AS 'GP',
COUNT(fixed_customer_name) as 'Ticket Count',
fixed_job_type AS 'Type'
FROM Invoice_new
WHERE
invoice_date >= '2018-01-01'
GROUP BY
business_unit,
fixed_customer_name,
trade,
fixed_trade,
state,
fixed_job_type,
Last_day(invoice_date)
Order by
invoice_date,
fixed_customer_name,
fixed_trade
mysql
mysql
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
asked Mar 27 at 14:51
haosmarkhaosmark
4556 silver badges19 bronze badges
4556 silver badges19 bronze badges
Why are you grouping by so many fields? TheGROUP BYandORDER BYare what's slowing the query down.
– Rocket Hazmat
Mar 27 at 14:54
1
An index can improve the performance of your query if the percentage of rows the filtering predicateinvoice_date >= '2018-01-01'is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?
– The Impaler
Mar 27 at 15:16
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38
add a comment |
Why are you grouping by so many fields? TheGROUP BYandORDER BYare what's slowing the query down.
– Rocket Hazmat
Mar 27 at 14:54
1
An index can improve the performance of your query if the percentage of rows the filtering predicateinvoice_date >= '2018-01-01'is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?
– The Impaler
Mar 27 at 15:16
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38
Why are you grouping by so many fields? The
GROUP BY and ORDER BY are what's slowing the query down.– Rocket Hazmat
Mar 27 at 14:54
Why are you grouping by so many fields? The
GROUP BY and ORDER BY are what's slowing the query down.– Rocket Hazmat
Mar 27 at 14:54
1
1
An index can improve the performance of your query if the percentage of rows the filtering predicate
invoice_date >= '2018-01-01' is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?– The Impaler
Mar 27 at 15:16
An index can improve the performance of your query if the percentage of rows the filtering predicate
invoice_date >= '2018-01-01' is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?– The Impaler
Mar 27 at 15:16
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38
add a comment |
0
active
oldest
votes
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%2f55380181%2fmysql-how-to-optimize-this-select-by-date-query-to-use-an-index%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
Is this question similar to what you get asked at work? Learn more about asking and sharing private information with your coworkers using Stack Overflow for Teams.
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%2f55380181%2fmysql-how-to-optimize-this-select-by-date-query-to-use-an-index%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
Why are you grouping by so many fields? The
GROUP BYandORDER BYare what's slowing the query down.– Rocket Hazmat
Mar 27 at 14:54
1
An index can improve the performance of your query if the percentage of rows the filtering predicate
invoice_date >= '2018-01-01'is selecting is low; at most 5%, ideally 0.5% or less. The questions is, what percentage of the rows is that condition [in average] selecting?– The Impaler
Mar 27 at 15:16
@TheImpaler that makes sense. Thanks for pointing it out. This is selecting more than half of the database, then compresses the output by grouping the data. So I guess I can't really make a super fast select of groups when querying such a large subset of data?
– haosmark
Mar 27 at 15:27
You can improve the speed marginally by using a covering index, if the number of columns you are selecting is far less than the total number of columns of the table.
– The Impaler
Mar 27 at 15:38