MySQL Bugs: #8414: SQL Engine refuses to use index on first table of outer query when subselect

Bug #8414	SQL Engine refuses to use index on first table of outer query when subselect
Submitted:	10 Feb 2005 4:35	Modified:	8 Apr 2005 23:54
Reporter:	Dan Tomlinson	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.9	OS:	Linux (Fedora Core 3)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:

Attempting to use a subquery in a relatively simple query to reduce the query time of a previous version of this query from about a minute.

It appears that when the subquery exists, the from table index does not get used, even with a "force index (indexname)" or a "use index (indexname) after the first table.

I know it's not an optimal query, but it should be sub-second response time.

Same behavior from mysql client and PHP 4.3.10 interface.

I've re-written the query to not use a subquery, so it's not critical for me right now, but this SHOULD work, and does work on my Oracle database properly.

I didn't see any similar bugs in the database.

Database installed from RPMs as shown below:
-rw-r--r--  1 root root  3257080 Feb  3 20:02 MySQL-bench-4.1.9-0.i386.rpm
-rw-r--r--  1 root root  5512802 Feb  3 20:02 MySQL-client-4.1.9-0.i386.rpm
-rw-r--r--  1 root root  2756123 Feb  3 20:02 MySQL-devel-4.1.9-0.i386.rpm
-rw-r--r--  1 root root 15133597 Feb  3 20:02 MySQL-server-4.1.9-0.i386.rpm
-rw-r--r--  1 root root  1123446 Feb  3 20:02 MySQL-shared-4.1.9-0.i386.rpm
-rw-r--r--  1 root root  1603158 Feb  3 20:02 MySQL-shared-compat-4.1.9-0.i386.rpm

Any suggestions appreciated!

How to repeat:

Query used:
select subcustomer.cus_id as cus_id, cus_type, cus_storename, 
  concat(cus_fname,' ',cus_lname) as cusname, cus_phone_H as cus_phone, 
  cus_prefterm,  left(note_date,10) as Printed, sum(extension) as amountdue 
from subcustnote 
 left outer join subcustomer on subcustnote.cus_id = subcustomer.cus_id 
 left outer join artrans on subcustomer.cus_id = artrans.cus_id  
where subcustnote.cus_id IN (
select distinct(cus_id)  as thislist from subcustnote as z where note_content like 'COLLECTION LETTER PREPARED%' and note_date like "2003-12%" and subcustnote.cus_id > 1
/* 2478,4255,5891,8995,9392,9524,4439,9200,3226 */
) and 
 note_content like 'COLLECTION LETTER PREPARED%' and currency = "USD" 
 group by subcustomer.cus_id 
having Printed like "2003-12%" 
order by Printed desc, cus_id

---
Explain output of this query:
+----+--------------------+-------------+----------------+-------------------------------+-----------+---------+-------------------------------+--------+----------------------------------------------+
| id | select_type        | table       | type           | possible_keys                 | key       | key_len | ref             | rows   | Extra                                        |
+----+--------------------+-------------+----------------+-------------------------------+-----------+---------+-------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY            | subcustnote | ALL            | NULL                          | NULL      |    NULL | NULL             | 230627 | Using where; Using temporary; Using filesort |
|  1 | PRIMARY            | subcustomer | eq_ref         | PRIMARY                       | PRIMARY   |       4 | stonehouse.subcustnote.cus_id |      1 |                                              |
|  1 | PRIMARY            | artrans     | ref            | artrancus                     | artrancus |       4 | stonehouse.subcustomer.cus_id |     18 | Using where                                  |
|  2 | DEPENDENT SUBQUERY | z           | index_subquery | cusbydate,cusbydate2,cus4note | cusbydate |       4 | func             |     12 | Using index; Using where                     |
+----+--------------------+-------------+----------------+-------------------------------+-----------+---------+-------------------------------+--------+----------------------------------------------+

Show Processlist while this is running shows "Preparing ..."
As you can see on the subquery, 3 indexes are available in the subquery, and two of them could be used for the primary query.

Experimentation done:

The results of the subquery is shown commented in the code above, and return time is 0.35 seconds for the subquery.

Result time IF I give a list instead of a subquery is .02 seconds. and uses the indexes properly.

Replacing the subquery with 
( select distinct(9200) as garbage from dummytable )
when dummytable has 1 row ALSO refuses to use the index on the subcustnote table above.
Return time for the whole query using this as the subquery is 1 minute, 22 seconds.

This was tested against both InnoDB and MyIsam table types.

Hi
Can you send me the create statement of the tables that you used to reproduce this behaviour ?

Thanks.

Sorry for the delay, I was out of town for a few days.

Schema creation was:

                                                                                                                    
DROP TABLE IF EXISTS subcustnote;
CREATE TABLE subcustnote (
  cus_id int(11) unsigned DEFAULT '0' NOT NULL,
  note_date datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
  note_user varchar(8) DEFAULT '' NOT NULL,
  note_content text,   KEY cusbydate (cus_id,note_date)
);                                                                                                                     

                                                                                                                    
DROP TABLE IF EXISTS artrans;
CREATE TABLE artrans (
  currency char(3) DEFAULT '' NOT NULL,
  orders_id int(5) DEFAULT '0',
  cus_id int(5) DEFAULT '0' NOT NULL,
  cus_terms varchar(12), 
  trandate date DEFAULT '0000-00-00' NOT NULL,
  product_amount decimal(8,2) DEFAULT '0.00' NOT NULL, 
  freight_amount decimal(8,2) DEFAULT '0.00' NOT NULL,
  extension decimal(10,2) DEFAULT '0.00' NOT NULL, 
  trantype char(1) DEFAULT '' NOT NULL,
  tax_amount decimal(8,2) DEFAULT '0.00' NOT NULL, 
  paid_flag char(1),
  paid_date date, 
  seqnum int(8) NOT NULL auto_increment, 
  applyto int(5), 
  paytype varchar(8), 
  reference varchar(8), 
  PRIMARY KEY (seqnum),
  KEY artrancus (cus_id,trandate) );
                                                                                                                     

                                                                                                                    
DROP TABLE IF EXISTS subcustomer;
CREATE TABLE subcustomer (
  cus_id int(11) unsigned NOT NULL auto_increment,
  cus_fname varchar(20) DEFAULT '' NOT NULL,
  cus_lname varchar(20) DEFAULT '' NOT NULL,
  cus_addr1 varchar(30) DEFAULT '' NOT NULL, 
  cus_addr2 varchar(30),
  cus_city varchar(24) DEFAULT '' NOT NULL, 
  cus_prov char(2) DEFAULT '' NOT NULL,
  cus_country char(2) DEFAULT 'CA' NOT NULL, 
  cus_postal varchar(14),
  cus_phone_H varchar(18), 
  cus_phone_B varchar(18),
  cus_phone_C varchar(16), 
  cus_phone_F varchar(16), 
  cus_email varchar(64) DEFAULT '' NOT NULL, 
  cus_type char(1) DEFAULT 'S' NOT NULL,
   cus_lchange timestamp(14),   cus_followup_flag char(1) DEFAULT 'N' NOT NULL,
  cus_followup_date date DEFAULT '0000-00-00' NOT NULL,
  cus_renewal_flag char(1) DEFAULT 'Y' NOT NULL,
  cus_password varchar(40) DEFAULT '' NOT NULL,
   cus_extern_id int(11) unsigned,
  cus_taxlic varchar(10) DEFAULT 'YES' NOT NULL, 
  cus_origin varchar(8),
  cus_credlim smallint(6) DEFAULT '500' NOT NULL,
  cus_allowcred char(1) DEFAULT 'Y' NOT NULL,
  cus_storename varchar(35),
  cus_prefship varchar(8),
  cus_prefterm varchar(6),
  ccoflog_id int(6),
  cus_mailerror char(1) DEFAULT 'N' NOT NULL,
  cus_created date,
  cus_hadstanding char(1) DEFAULT 'N' NOT NULL,
  cus_lastcancel varchar(30),
  cus_minshipqty smallint(6) DEFAULT '1' NOT NULL,
  cus_firm_sor char(1) DEFAULT 'F' NOT NULL,
  cusprofile set('0','99','1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19'),
  cus_has_rack char(1) DEFAULT 'N' NOT NULL,
  PRIMARY KEY (cus_id),
  UNIQUE nodupcust (cus_phone_H,cus_lname,cus_fname),
  KEY cusbydate (cus_created,cus_id)
);

Do I change the status or is this automatic?

This appears to also be related to 7830?

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".

Despite providing feedback I see no change to status.