MySQL Bugs: #71845: Query planner for view is not optimised

Bug #71845	Query planner for view is not optimised
Submitted:	26 Feb 2014 14:59	Modified:	16 Nov 2014 19:41
Reporter:	Charlie Clark	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S5 (Performance)
Version:	5.6.19	OS:	MacOS (10.9.2)
Assigned to:		CPU Architecture:	Any

Description:
It seems that somewhere between 5.1 and 5.6 this query planner here as changed if this kind of query is a view. In 5.1 the plan for the query from the view is optimised using the index, in 5.6 a full scan of the relevant table is applied even though the index is recognised.

How to repeat:
CREATE VIEW "last_12_months" AS 
select distinct labelDate AS survey from pages
order by labelDate desc limit 25;

select survey from last_12_months;

Query plan
1, PRIMARY, <derived2>, ALL, , , , , 25, Using filesort
2, DERIVED, pages, index, labelDate, labelDate, 4, , 8055130, Using index

When compared with the direct query it seems to be doing to a full scan of a different index.

select distinct labelDate 
from pages
order by labelDate
limit 25

Query plan
1, SIMPLE, pages, range, labelDate, labelDate, 4, , 57, Using index for group-by

Thank you for the report.

Please provide output of SHOW CREATE TABLE pages

CREATE TABLE "pages" (
  "pageid" int(10) unsigned NOT NULL AUTO_INCREMENT,
  "createDate" int(10) unsigned NOT NULL,
  "archive" varchar(16) CHARACTER SET latin1 NOT NULL,
  "label" varchar(32) CHARACTER SET latin1 NOT NULL,
  "wptid" varchar(64) CHARACTER SET latin1 NOT NULL,
  "wptrun" int(2) unsigned NOT NULL,
  "url" text CHARACTER SET latin1,
  "urlShort" varchar(255) NOT NULL,
  "startedDateTime" int(10) unsigned DEFAULT NULL,
  "renderStart" int(10) unsigned DEFAULT NULL,
  "onContentLoaded" int(10) unsigned DEFAULT NULL,
  "onLoad" int(10) unsigned DEFAULT NULL,
  "PageSpeed" int(4) unsigned DEFAULT NULL,
  "rank" int(10) unsigned DEFAULT NULL,
  "reqTotal" int(4) unsigned NOT NULL,
  "reqHtml" int(4) unsigned NOT NULL,
  "reqJS" int(4) unsigned NOT NULL,
  "reqCSS" int(4) unsigned NOT NULL,
  "reqImg" int(4) unsigned NOT NULL,
  "reqFlash" int(4) unsigned NOT NULL,
  "reqJson" int(4) unsigned NOT NULL,
  "reqOther" int(4) unsigned NOT NULL,
  "bytesTotal" int(10) unsigned NOT NULL,
  "bytesHtml" int(10) unsigned NOT NULL,
  "bytesJS" int(10) unsigned NOT NULL,
  "bytesCSS" int(10) unsigned NOT NULL,
  "bytesImg" int(10) unsigned NOT NULL,
  "bytesFlash" int(10) unsigned NOT NULL,
  "bytesJson" int(10) unsigned NOT NULL,
  "bytesOther" int(10) unsigned NOT NULL,
  "numDomains" int(4) unsigned NOT NULL,
  "labelDate" date DEFAULT NULL,
  "TTFB" smallint(5) unsigned DEFAULT '0',
  "reqGif" smallint(5) unsigned NOT NULL DEFAULT '0',
  "reqJpg" smallint(5) unsigned NOT NULL DEFAULT '0',
  "reqPng" smallint(5) unsigned NOT NULL DEFAULT '0',
  "reqFont" smallint(5) unsigned NOT NULL DEFAULT '0',
  "bytesGif" int(10) unsigned NOT NULL DEFAULT '0',
  "bytesJpg" int(10) unsigned NOT NULL DEFAULT '0',
  "bytesPng" int(10) unsigned NOT NULL DEFAULT '0',
  "bytesFont" int(10) unsigned NOT NULL DEFAULT '0',
  "maxageMore" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxage365" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxage30" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxage1" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxage0" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxageNull" smallint(5) unsigned NOT NULL DEFAULT '0',
  "numDomElements" mediumint(8) unsigned NOT NULL DEFAULT '0',
  "numCompressed" smallint(5) unsigned NOT NULL DEFAULT '0',
  "numHttps" smallint(5) unsigned NOT NULL DEFAULT '0',
  "numGlibs" smallint(5) unsigned NOT NULL DEFAULT '0',
  "numErrors" smallint(5) unsigned NOT NULL DEFAULT '0',
  "numRedirects" smallint(5) unsigned NOT NULL DEFAULT '0',
  "maxDomainReqs" smallint(5) unsigned NOT NULL DEFAULT '0',
  "bytesHtmlDoc" mediumint(8) unsigned NOT NULL DEFAULT '0',
  "fullyLoaded" int(10) unsigned DEFAULT NULL,
  "cdn" varchar(64) DEFAULT NULL,
  "SpeedIndex" mediumint(8) unsigned DEFAULT NULL,
  "visualComplete" int(10) unsigned DEFAULT NULL,
  "gzipTotal" int(10) unsigned NOT NULL DEFAULT '0',
  "gzipSavings" int(10) unsigned NOT NULL DEFAULT '0',
  "siteid" int(11) DEFAULT NULL,
  "crawlid" int(10) unsigned NOT NULL,
  "urlhash" int(11) NOT NULL,
  "_connections" int(11) NOT NULL,
  "_adult_site" tinyint(4) NOT NULL,
  "avg_dom_depth" int(4) NOT NULL,
  "document_height" int(10) NOT NULL,
  "document_width" int(10) NOT NULL,
  "localstorage_size" int(10) NOT NULL,
  "sessionstorage_size" int(10) NOT NULL,
  "num_iframes" int(8) NOT NULL,
  "num_scripts" int(8) NOT NULL,
  "doctype" varchar(255) NOT NULL,
  "meta_viewport" varchar(255) NOT NULL,
  PRIMARY KEY ("pageid"),
  UNIQUE KEY "label" ("label","urlShort"),
  KEY "labelDate" ("labelDate"),
  KEY "cdn" ("cdn"),
  KEY "urlShort" ("urlShort"),
  KEY "rank" ("rank"),
  KEY "urlhash" ("urlhash")
) |

Thank you for the feedback.

I cannot repeat described behavior with dummy data. Please try to update table statistics for table pages (ANALYZE TABLE, OPTIMIZE TABLE), then try to upgrade to current version 5.6.19 and inform us if problem still exists in your environment.

I analysed and optimised the table and am running 5.6.19. The output from EXPLAIN is unchanged.

Thank you for the feedback.

Please try with current version 5.6.21 and, if problem still exists, if possible, try to provide minimal dump the issue is repeatable with.

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

a level

Attachment: a level.pdf (application/pdf, text), 73.55 KiB.

3

Attachment: 3.pdf (application/pdf, text), 409.69 KiB.

analytical

Attachment: Analytical2.pdf (application/pdf, text), 31.81 KiB.