[MDEV-3818] Query against view over IS tables worse than equivalent query without view Created: 2012-10-25  Updated: 2012-12-18  Resolved: 2012-12-17

Status: Closed
Project: MariaDB Server
Component/s: None
Affects Version/s: 5.5.28
Fix Version/s: 5.5.29

Type: Bug Priority: Major
Reporter: Georg Richter Assignee: Timour Katchaounov (Inactive)
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux x64



 Description   

A view which access an information schema table seems to use another plan, than the query in its extend explain output (it scan's all databases)

CREATE VIEW v1 AS SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS;

explain extended select column_name FROM v1 WHERE TABLE_SCHEMA="osm" AND TABLE_NAME="test";

extra: Using where; Open_frm_only; Scanned all databases

show warnings;
..
select `information_schema`.`COLUMNS`.`COLUMN_NAME` AS `COLUMN_NAME` from `INFORMATION_SCHEMA`.`COLUMNS` where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = 'osm') and (`information_schema`.`COLUMNS`.`TABLE_NAME` = 'test'))

explain select `information_schema`.`COLUMNS`.`COLUMN_NAME` AS `COLUMN_NAME` from `INFORMATION_SCHEMA`.`COLUMNS` where ((`information_schema`.`COLUMNS`.`TABLE_SCHEMA` = 'osm') and (`information_schema`.`COLUMNS`.`TABLE_NAME` = 'test'));

extra: Using where; Open_frm_only; Scanned 0 databases



 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-10-25 ]

The problem is present in MySQL 5.6.7 as well.

Comment by Timour Katchaounov (Inactive) [ 2012-12-17 ]
  • Review discussion with Sanja, some more tests, approved by Sanja
  • Merged into latest 5.5, pushed
Generated at Thu Feb 08 06:51:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.