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:
Category:MySQL Server Severity:S1 (Critical)
Version:4.1.9 OS:Linux (Fedora Core 3)
Assigned to: Assigned Account CPU Architecture:Any

[10 Feb 2005 4:35] Dan Tomlinson

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.
[10 Feb 2005 4:52] Dan Tomlinson
This was tested against both InnoDB and MyIsam table types.
[18 Feb 2005 21:31] Jorge del Conde
Can you send me the create statement of the tables that you used to reproduce this behaviour ?

[22 Feb 2005 15:09] Dan Tomlinson
Sorry for the delay, I was out of town for a few days.

Schema creation was:

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)

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) );

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)
[8 Mar 2005 0:58] Dan Tomlinson
Do I change the status or is this automatic?
[8 Mar 2005 1:07] Dan Tomlinson
This appears to also be related to 7830?
[8 Apr 2005 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".
[8 Apr 2005 23:54] Dan Tomlinson
Despite providing feedback I see no change to status.