MySQL Bugs: #22779: "order by" clause performance..

Bug #22779	"order by" clause performance..
Submitted:	28 Sep 2006 12:18	Modified:	8 Apr 2011 22:36
Reporter:	Dorin Manoli	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S5 (Performance)
Version:	Ver 14.12 Distrib 5.0.24a	OS:	Windows (Windows 2000 server)
Assigned to:		CPU Architecture:	Any
Tags:	limit, order by

Description:
select from 3 tables with order by clause slow down data request.

How to repeat:
InnoDB storage engine: 
I have 3 tables with aproximateley 300 k records on each.

DROP TABLE IF EXISTS "dataireland_dbo"."companies";
CREATE TABLE  "dataireland_dbo"."companies" (
  "companyId" bigint(19) NOT NULL auto_increment,
  "serial" varchar(8) default NULL,
  "regNo" varchar(8) default NULL,
  "regDate" varchar(10) default NULL,
  "legalFormId" int(10) default NULL,
  "vatno" varchar(12) default NULL,
  "originCountry" varchar(6) default NULL,
  "officeHr" varchar(40) default NULL,
  "employees" int(10) default NULL,
  "businessOverseas" int(10) default NULL,
  "printOptions" varchar(6) default NULL,
  "member" varchar(5) default NULL,
  "mail" varchar(1) default NULL,
  "indica" varchar(5) default NULL,
  "journl" varchar(4) default NULL,
  "comptr" varchar(10) default NULL,
  "isLocked" tinyint(4) default NULL,
  "replyCodeId" int(10) default NULL,
  PRIMARY KEY  ("companyId"),
  UNIQUE KEY "unqSerial" ("serial"),
  KEY "FK_Companies_CompanyReplyCodes" ("replyCodeId"),
  KEY "FK_Companies_LegalForms" ("legalFormId"),
  KEY "idxCompanyId" ("companyId"),
  CONSTRAINT "FK_Companies_CompanyReplyCodes" FOREIGN KEY ("replyCodeId") REFERENCES "companyreplycodes" ("replyId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_Companies_LegalForms" FOREIGN KEY ("legalFormId") REFERENCES "legalforms" ("legalFormId") ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS "dataireland_dbo"."companynames";
CREATE TABLE  "dataireland_dbo"."companynames" (
  "nameId" bigint(19) NOT NULL auto_increment,
  "companyId" bigint(19) default NULL,
  "companyName" varchar(55) NOT NULL,
  "sortKey" varchar(50) default NULL,
  "dateChanged" datetime default NULL,
  "addressId" bigint(19) NOT NULL,
  "nameTypeId" int(10) NOT NULL,
  PRIMARY KEY  ("nameId"),
  KEY "idxCNAddressId" ("addressId"),
  KEY "idxCNNameType" ("nameTypeId"),
  KEY "idxCNCompanyId" ("companyId"),
  CONSTRAINT "FK_CompanyNames_Address" FOREIGN KEY ("addressId") REFERENCES "address" ("addressId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_CompanyNames_Companies" FOREIGN KEY ("companyId") REFERENCES "companies" ("companyId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_CompanyNames_CompanyNameCodes" FOREIGN KEY ("nameTypeId") REFERENCES "companynamecodes" ("nameTypeId") ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS "dataireland_dbo"."address";
CREATE TABLE  "dataireland_dbo"."address" (
  "addressId" bigint(19) NOT NULL auto_increment,
  "companyId" bigint(19) NOT NULL,
  "acode" varchar(2) default NULL,
  "estateRef" varchar(10) default NULL,
  "areaKey" varchar(2) default NULL,
  "postSortCode" varchar(8) default NULL,
  "addressLine1" varchar(25) default NULL,
  "addressLine2" varchar(25) default NULL,
  "addressLine3" varchar(25) default NULL,
  "addressLine4" varchar(25) default NULL,
  "addressLine5" varchar(25) default NULL,
  "Town" varchar(25) default NULL,
  "county" varchar(25) default NULL,
  "countryCode" varchar(6) default NULL,
  "telex" varchar(8) default NULL,
  "telexab" varchar(10) default NULL,
  "atlxref" varchar(8) default NULL,
  "atlgrm" varchar(15) default NULL,
  "gridEst" int(10) default NULL,
  "gridNorth" int(10) default NULL,
  "aprtopt" varchar(4) default NULL,
  "intPhone" varchar(25) default NULL,
  "phoneCodesId" int(10) default NULL,
  "intFax" varchar(25) default NULL,
  "faxCodesId" int(10) default NULL,
  "buildingId" varchar(8) default NULL,
  "employeesAtLocation" int(10) default NULL,
  "statusCodeId" int(10) default NULL,
  "serial" varchar(8) default NULL,
  "orderNr" varchar(8) default NULL,
  PRIMARY KEY  ("addressId"),
  KEY "idxAddrCompanyID" ("companyId"),
  KEY "idxAddrEstateRef" ("estateRef"),
  KEY "idxAddrPrivacyCodes" ("phoneCodesId","faxCodesId"),
  KEY "idxAddrStatus" ("statusCodeId"),
  KEY "idxOrderNr" ("orderNr"),
  KEY "FK_Address_PrivacyCodes1" ("faxCodesId"),
  CONSTRAINT "FK_Address_Companies" FOREIGN KEY ("companyId") REFERENCES "companies" ("companyId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_Address_CompanyStatusCodes" FOREIGN KEY ("statusCodeId") REFERENCES "companystatuscodes" ("statusCodeId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_Address_Estates" FOREIGN KEY ("estateRef") REFERENCES "estates" ("estateRef") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_Address_PrivacyCodes" FOREIGN KEY ("phoneCodesId") REFERENCES "privacycodes" ("privacyCodeId") ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT "FK_Address_PrivacyCodes1" FOREIGN KEY ("faxCodesId") REFERENCES "privacycodes" ("privacyCodeId") ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select query:
  select c.companyId,c.serial,cn.companyName,adr.intPhone,
  replace(CONCAT_WS(', ',adr.addressLine1,adr.addressLine2,adr.addressLine3,adr.addressLine4),', ','') as address
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and replace(adr.orderNr,'00','')='1'  and c.companyId>0  order by  c.companyId LIMIT 0,100 ;
takes about 52 secconds.  

I have indexes for c.companyId, adr.companyId, cn.addressId, adr,orderNr,cn.nameTypeId .

but executing this query:
 select c.companyId,c.serial,cn.companyName,adr.intPhone,
  replace(CONCAT_WS(', ',adr.addressLine1,adr.addressLine2,adr.addressLine3,adr.addressLine4),', ','') as address
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and replace(adr.orderNr,'00','')='1' and c.companyId>0  order by  c.companyId LIMIT 1,100 ;

a got results in 0.014 secconds..
I add only : and c.companyId>0..

duing explain for forst query I got:
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","cn","ref","idxCNAddressId,idxCNNameType","idxCNNameType","4","const",166694,"Using where; Using temporary; Using filesort"
1,"SIMPLE","adr","eq_ref","PRIMARY,idxAddrCompanyID","PRIMARY","8","dataireland_dbo.cn.addressId",1,"Using where"
1,"SIMPLE","c","eq_ref","PRIMARY,idxCompanyId","PRIMARY","8","dataireland_dbo.adr.companyId",1,""

explain for seccond query:
"id","select_type","table","type","possible_keys","key","key_len","ref","rows","Extra"
1,"SIMPLE","c","range","PRIMARY,idxCompanyId","PRIMARY","8","",57096,"Using where"
1,"SIMPLE","adr","ref","PRIMARY,idxAddrCompanyID","idxAddrCompanyID","8","dataireland_dbo.c.companyId",1,"Using where"
1,"SIMPLE","cn","ref","idxCNAddressId,idxCNNameType","idxCNAddressId","8","dataireland_dbo.adr.addressId",1,"Using where"

have a look how type are changed .. 

If I remove order by clause and c.companyId>0  from where clause everything is ok. but I have the wrong ordering of data .

Thank you for a problem report. Please, send the results of:

  select count(*)
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and replace(adr.orderNr,'00','')='1'
   and c.companyId>0;

and

 select count(*)
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and replace(adr.orderNr,'00','')='1';

1: 280853 fetched in 9.5194s
2: 280853 fetched in 4.3354s

Please, try to repeat with a newer version, 5.0.26, and inform about the results.

explain select c.companyId,c.serial,cn.companyName,adr.intPhone,
  replace(CONCAT_WS(',
',adr.addressLine1,adr.addressLine2,adr.addressLine3,adr.addressLine4),', ','')
as address
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and adr.orderNr='1'
 order by  c.companyId LIMIT 0,100 ;

I got :
1, 'SIMPLE', 'cn', 'ref', 'idxCNAddressId,idxCNNameType', 'idxCNNameType', '4', 'const', 170417, 'Using where; Using temporary; Using filesort'
1, 'SIMPLE', 'adr', 'eq_ref', 'PRIMARY,idxAddrCompanyID,idxOrderNr,idxAddressId', 'PRIMARY', '8', 'dataireland_dbo.cn.addressId', 1, 'Using where'
1, 'SIMPLE', 'c', 'eq_ref', 'PRIMARY,idxCompanyId', 'PRIMARY', '8', 'dataireland_dbo.adr.companyId', 1, ''

explain select c.companyId,c.serial,cn.companyName,adr.intPhone,
  replace(CONCAT_WS(',
',adr.addressLine1,adr.addressLine2,adr.addressLine3,adr.addressLine4),', ','')
as address
   from companies c
   left join address adr on c.companyId=adr.companyId	
   left join companynames cn on adr.addressId=cn.addressId	
   where cn.nameTypeId=1 and adr.orderNr='1' and c.companyId>0
 order by  c.companyId LIMIT 0,100 ; 

1, 'SIMPLE', 'c', 'range', 'PRIMARY,idxCompanyId', 'PRIMARY', '8', '', 57096, 'Using where'
1, 'SIMPLE', 'adr', 'ref', 'PRIMARY,idxAddrCompanyID,idxOrderNr,idxAddressId', 'idxAddrCompanyID', '8', 'dataireland_dbo.c.companyId', 1, 'Using where'
1, 'SIMPLE', 'cn', 'ref', 'idxCNAddressId,idxCNNameType', 'idxCNAddressId', '8', 'dataireland_dbo.adr.addressId', 1, 'Using where'

why seccond querry scan less reccords than first query?

Note a different join order (it explains different estimated number of rows accessed)! Have you got the same plans/problem with 5.0.26/5.0.27 as with 5.0.24a?

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