Details
-
Type:
Bug
-
Status: Closed
-
Resolution: Not a Bug
-
Affects Version/s: None
-
Fix Version/s: None
-
Component/s: None
-
Labels:
Description
I'm not sure this is actually a bug or expected behavior....
I'm using maria 5.5.22 and you can see my table structures below.
When I execute the following query
SELECT SUM(`PAIDAMT`) as total, SUM(`DISCOUNT`) as discount from `ap_invoices` WHERE `VENDNO`='UPS' AND `INVNO` IN(SELECT `INVNO` FROM `checks_written` WHERE `CHECKNO`='42356')
I get the following explain extended
------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | <subquery2> | ALL | distinct_key | NULL | NULL | NULL | 1 | 100.00 | |
| 1 | PRIMARY | ap_invoices | ref | INVNO,VENDNO | INVNO | 13 | citydiesel_inventory.checks_written.INVNO | 1 | 100.00 | Using index condition; Using where |
| 2 | MATERIALIZED | checks_written | ref | CHECKNO | CHECKNO | 9 | const | 1 | 100.00 | Using index condition; Using where |
------------------------------------------------------------------------------------------------------------------------------------------------------
However if I remove the single quote marks from CHECKNO so the query looks like
SELECT SUM(`PAIDAMT`) as total, SUM(`DISCOUNT`) as discount from `ap_invoices` WHERE `VENDNO`='UPS' AND `INVNO` IN(SELECT `INVNO` FROM `checks_written` WHERE `CHECKNO`=42356)
I get an explain that looks like
-------------------------------------------------------------------------------------------------------------
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------
| 1 | PRIMARY | ap_invoices | ref | INVNO,VENDNO | VENDNO | 7 | const | 835 | 100.00 | Using index condition |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 8 | func | 1 | 100.00 | Using where |
| 2 | MATERIALIZED | checks_written | ALL | CHECKNO | NULL | NULL | NULL | 56507 | 100.00 | Using where |
-------------------------------------------------------------------------------------------------------------
table structures
CREATE TABLE `ap_invoices` (
`Id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`INVNO` char(12) DEFAULT NULL,
`VENDNO` char(6) DEFAULT NULL,
`PPRIORITY` char(1) DEFAULT NULL,
`PDISC` decimal(12,2) DEFAULT NULL,
`PDAYS` int(11) DEFAULT NULL,
`PNET` int(11) DEFAULT NULL,
`PURDATE` char(10) DEFAULT NULL,
`DUEDATE` char(10) DEFAULT NULL,
`DISDATE` char(10) DEFAULT NULL,
`DISCOUNT` decimal(8,2) DEFAULT NULL,
`PURAMT` decimal(8,2) DEFAULT NULL,
`PAIDAMT` decimal(8,2) DEFAULT NULL,
`DISAMT` decimal(8,2) DEFAULT NULL,
`ADJAMT` decimal(8,2) DEFAULT NULL,
`APRPAY` decimal(8,2) DEFAULT NULL,
`APRDIS` decimal(8,2) DEFAULT NULL,
`APRADJ` decimal(8,2) DEFAULT NULL,
`AMT1099` decimal(8,2) DEFAULT NULL,
`BUYER` char(2) DEFAULT NULL,
`CURRENT` char(1) DEFAULT NULL,
`REF` char(8) DEFAULT NULL,
`CHECKNO` char(8) DEFAULT NULL,
`CHECKDATE` char(10) DEFAULT NULL,
`APACC` char(9) DEFAULT NULL,
`CHKACC` char(9) DEFAULT NULL,
`TYP1099` char(5) DEFAULT NULL,
`APSTAT` char(1) DEFAULT NULL,
`APTYPE` char(1) DEFAULT NULL,
`TOSW` char(1) DEFAULT NULL,
`SIGNATURE` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`Id`),
KEY `INVNO` (`INVNO`,`CURRENT`),
KEY `VENDNO` (`VENDNO`,`CURRENT`),
KEY `PPRIORITY` (`PPRIORITY`,`CURRENT`),
KEY `CURRENT` (`CURRENT`),
KEY `SIGNATURE` (`SIGNATURE`),
KEY `APRPAY` (`APRPAY`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
–
-- Table structure for table `checks_written`
–
CREATE TABLE `checks_written` (
`Id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`INVNO` char(8) DEFAULT NULL,
`VENDNO` char(6) DEFAULT NULL,
`COMPANY` char(35) DEFAULT NULL,
`PPRIORITY` char(1) NOT NULL,
`APRPAY` decimal(8,2) DEFAULT NULL,
`AMT1099` decimal(8,2) DEFAULT NULL,
`REF` char(8) DEFAULT NULL,
`CHECKNO` char(8) DEFAULT NULL,
`CHECKDATE` char(10) DEFAULT NULL,
`VOIDDATE` char(10) DEFAULT NULL,
`CHKACC` char(9) DEFAULT NULL,
`TYP1099` char(5) DEFAULT NULL,
`APSTAT` char(1) NOT NULL,
`CKSTAT` enum('','V') NOT NULL,
`CKTYPE` char(1) DEFAULT NULL,
PRIMARY KEY (`Id`),
KEY `APSTAT` (`APSTAT`,`PPRIORITY`),
KEY `CHECKNO` (`CHECKNO`),
KEY `VENDNO` (`VENDNO`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Re: Different execution plans for subquery
Sorry I should have been more clear... The first plan is very efficient and almost instant regardless of table size, and the second seems to be doing a full table scan on checks_written regardless of the table size and index hints. In this case ~50000 records it takes .5 seconds however on the actual production machine where the table has ~300000 rows it was taking over 3 seconds.
Re: Different execution plans for subquery
Assigning to Timour to confirm that the behavior is expected (or to say it's not).
Re: Different execution plans for subquery
As far as I can see, the only difference is in the following subquery predicate:
`CHECKNO`='42356' vs `CHECKNO`=42356
In the second case a numeric constant is implicitly converted to a string.
This is not a bug. AFAIK for instance Oracle has the same behavior
with respect to implicitly converted constants.
Try the following simple example:
create table t1 (c1 char(1), c2 char(1), key(c1));
insert into t1 values (1,1);
insert into t1 values (2,1);
insert into t1 values (3,2);
If we supply a string constant of the same length, then the index is used:
explain select * from t1 where '1' = c1;
--------------------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
--------------------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ref | c1 | c1 | 2 | const | 1 | Using index condition |
--------------------------------------------------------------------------------+
However, if the constant needs to be converted implicitly, the index cannot be used
any more:
explain select * from t1 where 1 = c1;
---------------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
---------------------------------------------------------------------+
| 1 | SIMPLE | t1 | ALL | c1 | NULL | NULL | NULL | 3 | Using where |
---------------------------------------------------------------------+
Launchpad bug id: 983423
Re: Different execution plans for subquery
Hi,
What is the actual problem? Is it just the observation that the plans are different (which of course can happen), or is one of the plans badly inefficient, so the query is executed too slowly? If so, which one is it and how slow it gets (comparing to the other one)?
Thank you.