Bug #27467 | MAX don't optimized. | ||
---|---|---|---|
Submitted: | 27 Mar 2007 10:37 | Modified: | 30 May 2007 7:49 |
Reporter: | Mikhail Strizhov | Email Updates: | |
Status: | No Feedback | Impact on me: | |
Category: | MySQL Server: Optimizer | Severity: | S3 (Non-critical) |
Version: | 5.1.16-beta-log | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[27 Mar 2007 10:37]
Mikhail Strizhov
[27 Mar 2007 13:11]
Mikhail Strizhov
I have table with 8000000rows and with this query query runs long time :( I need to calculate totals by all articles (about ~3000) and all shops (about ~30) for certain date. I can't store all data, because table will huge (3000x30x5(five operation with articles) = 450000 per day). I wan't store totals for article if article was sold in this day, if article don't sold this certain day i wan't get totals from previous day etc. I find this solution, but query takes long time, however by idea it can takes small time if MySQL don't will be scan all rows by WHERE, and find MAX(Date) from direct from index.
[27 Mar 2007 13:12]
Mikhail Strizhov
Any ideas how this may be done?
[27 Mar 2007 13:19]
Mikhail Strizhov
I already two weeks think how it can be done. I tryied to make new field DateTo, but query SELECT * FROM tmp WHERE Date<='2007-03-01' AND '2007-03-01'<=DateTo AND Article=... AND LoginID=... AND Shop=...; taskes long time too with index (Article, Operation, Shop, Date, DateTo), because can't cath this moment and use index optimal, how i need.
[28 Mar 2007 7:32]
Sveta Smirnova
Thank you for the report. please provide output of SHOW CREATE TABLE for tables PrList2 and Shops
[28 Apr 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[30 Apr 2007 7:49]
Valeriy Kravchuk
Feedback is still needed.
[30 May 2007 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".