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:
None 
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
Description:
MAX don't optimized. MySQL can fetch MAX(Date) directly from index but he don't do it when WHERE used with "<".

How to repeat:
--------------------------------------------------------------------------------
mysql> EXPLAIN SELECT p.Art, Sum FROM PrList2 p,Shops s,tmp t1 WHERE p.Art=t1.Article AND t1.Date=(SELECT MAX(Date) FROM tmp WHERE Date<'2007-07-18' AND Article=p.Art AND Operation=1 AND LoginID=s.ID) AND t1.LoginID=s.ID AND t1.Operation=1 LIMIT 2000;
+----+--------------------+-------+-------+---------------+---------+---------+---------------------------------------+------+--------------------------+
| id | select_type        | table | type  | possible_keys | key     | key_len | ref                                   | rows | Extra                    |
+----+--------------------+-------+-------+---------------+---------+---------+---------------------------------------+------+--------------------------+
|  1 | PRIMARY            | s     | index | PRIMARY       | PRIMARY | 4       | NULL                                  |   38 | Using index              |
|  1 | PRIMARY            | p     | index | art           | art     | 252     | NULL                                  | 3280 | Using index              |
|  1 | PRIMARY            | t1    | ref   | Date,LoginID  | LoginID | 79      | mattino.s.ID,mattino.p.art,const,func |    2 | Using where              |
|  2 | DEPENDENT SUBQUERY | tmp   | ref   | Date,LoginID  | LoginID | 76      | mattino.s.ID,mattino.p.art,const      |  681 | Using where; Using index |
+----+--------------------+-------+-------+---------------+---------+---------+---------------------------------------+------+--------------------------+
4 rows in set (0.01 sec)

mysql> SHOW CREATE TABLE tmp;
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                              |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| tmp   | CREATE TABLE `tmp` (
  `Date` date NOT NULL,
  `Login` varchar(32) DEFAULT NULL,
  `Article` varchar(64) DEFAULT NULL,
  `Operation` int(11) DEFAULT NULL,
  `Sum` int(11) DEFAULT NULL,
  `Count` int(11) DEFAULT NULL,
  `LoginID` int(11) NOT NULL,
  KEY `Date` (`Date`,`Operation`),
  KEY `LoginID` (`LoginID`,`Article`,`Operation`,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

--------------------------------------------------------------------

This is very-very bad, that query SELECT MAX(Date) FROM tmp WHERE Date<'2007-07-18' AND Article=p.Art AND Operation=1 AND LoginID=s.ID
don't fetch MAX(Date) directly from index. He selects rows by where "rticle=p.Art AND Operation=1 AND LoginID=s.ID
don't fetch MAX(Date)", and then find MAX(Date) of XXX rows.
[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".