{"id":1328,"date":"2010-05-20T07:17:05","date_gmt":"2010-05-20T05:17:05","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=1328"},"modified":"2010-05-20T07:17:05","modified_gmt":"2010-05-20T05:17:05","slug":"views-better-performance-with-condition-pushdown","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/views-better-performance-with-condition-pushdown","title":{"rendered":"Views: better performance with condition pushdown"},"content":{"rendered":"

Justin’s A workaround for the performance problems of TEMPTABLE views<\/a> post on mysqlperformanceblog.com<\/a> reminded me of a solution I once saw on a customer’s site.<\/p>\n

The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with a LOT of subqueries, temporary tables, and vast amounts of data, even if only to return with a couple of rows.<\/p>\n

While we worked to solve this, a developer showed me his own trick. His trick is now impossible to implement, but there’s a hack around this.<\/p>\n

Let’s use the world database to illustrate. Look at the following view definition:<\/p>\n

\n
\r\nCREATE\r\n  ALGORITHM=TEMPTABLE\r\nVIEW country_languages AS\r\n  SELECT\r\n    Country.CODE, Country.Name AS country,\r\n    GROUP_CONCAT(CountryLanguage.Language) AS languages\r\n  FROM\r\n    world.Country\r\n    JOIN world.CountryLanguage ON (Country.CODE = CountryLanguage.CountryCode)\r\n  GROUP BY\r\n    Country.CODE;\r\n<\/pre>\n<\/blockquote>\n
The view presents with a list of spoken languages per country. The execution plan for querying this view looks like this:<\/p>\n
\n
mysql> EXPLAIN SELECT * FROM country_languages;\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n| id | select_type | table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | type\u00a0\u00a0 | possible_keys | key\u00a0\u00a0\u00a0\u00a0 | key_len | ref\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | rows | Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n|\u00a0 1 | PRIMARY\u00a0\u00a0\u00a0\u00a0 | <derived2>\u00a0\u00a0\u00a0\u00a0\u00a0 | ALL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 233 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | CountryLanguage | index\u00a0 | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 33\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 984 | Using index; Using temporary; Using filesort |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | Country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | eq_ref | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | world.CountryLanguage.CountryCode |\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
And, even if we only want to filter out a single country, we still get the same plan:<\/p>\n
\n
mysql> EXPLAIN SELECT * FROM country_languages WHERE Code='USA';\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n| id | select_type | table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | type\u00a0\u00a0 | possible_keys | key\u00a0\u00a0\u00a0\u00a0 | key_len | ref\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | rows | Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n|\u00a0 1 | PRIMARY\u00a0\u00a0\u00a0\u00a0 | <derived2>\u00a0\u00a0\u00a0\u00a0\u00a0 | ALL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 233 | Using where\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | CountryLanguage | index\u00a0 | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 33\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0 984 | Using index; Using temporary; Using filesort |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | Country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | eq_ref | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | world.CountryLanguage.CountryCode |\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
So, we need to scan the entire country_language and country tables in order to return results for just one row.<\/p>\n
A non-working solution<\/h4>\n
The solution offered by the developer was this:<\/p>\n
\n
\r\nCREATE\r\n  ALGORITHM=MERGE\r\n  VIEW country_languages_non_working AS\r\n  SELECT\r\n    Country.CODE, Country.Name AS country,\r\n    GROUP_CONCAT(CountryLanguage.Language) AS languages\r\n  FROM\r\n    world.Country\r\n    JOIN world.CountryLanguage ON\r\n      (Country.CODE = CountryLanguage.CountryCode)\r\n  WHERE\r\n    Country.CODE = @country_code\r\n  GROUP BY Country.CODE;\r\n<\/pre>\n<\/blockquote>\n
And follow by:<\/p>\n
\n
mysql> SET @country_code='USA';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SELECT * FROM country_languages_2;\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n| CODE | country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | languages\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n| USA\u00a0 | United States | Chinese,English,French,German,Italian,Japanese,Korean,Polish,Portuguese,Spanish,Tagalog,Vietnamese |\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n<\/pre>\n<\/blockquote>\n
So, pushdown a WHERE<\/strong> condition into the view’s definition. The session variable @country_code is used to filter rows. In the above simplified code the value is assumed to be set; tweak it as you see fit (using IFNULL<\/strong>, for example, or OR<\/strong> statements) to allow for full scan in case the variable is undefined.<\/p>\n
This doesn’t work. It used to work a couple years back; but today you cannot create a view which uses session variables or parameters. It is a restriction imposed by views.<\/p>\n
A workaround<\/h4>\n
Justin showed a workaround using an additional table. There is another workaround which does not involve tables, but rather stored routines. Now, this is a patch, and an ugly one. It may not work in future versions of MySQL for all I know. But, here it goes:<\/p>\n
\n
\r\nDELIMITER $$\r\nCREATE DEFINER=`root`@`localhost` FUNCTION `get_session_country`() RETURNS CHAR(3)\r\n    NO SQL\r\n    DETERMINISTIC\r\nBEGIN\r\n  RETURN @country_code;\r\nEND $$\r\nDELIMITER ;\r\n\r\nCREATE\r\n  ALGORITHM=MERGE\r\n  VIEW country_languages_2 AS\r\n  SELECT\r\n    Country.CODE, Country.Name AS country,\r\n    GROUP_CONCAT(CountryLanguage.Language) AS languages\r\n  FROM\r\n    world.Country\r\n    JOIN world.CountryLanguage ON\r\n      (Country.CODE = CountryLanguage.CountryCode)\r\n  WHERE\r\n    Country.CODE = get_session_country()\r\n  GROUP BY Country.CODE;\r\n<\/pre>\n<\/blockquote>\n
And now:<\/p>\n
\n
mysql> SET @country_code='USA';\r\nQuery OK, 0 rows affected (0.00 sec)\r\n\r\nmysql> SELECT * FROM country_languages_2;\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n| CODE | country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | languages\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n| USA\u00a0 | United States | Chinese,English,French,German,Italian,Japanese,Korean,Polish,Portuguese,Spanish,Tagalog,Vietnamese |\r\n+------+---------------+----------------------------------------------------------------------------------------------------+\r\n1 row in set, 1 warning (0.00 sec)\r\n\r\nmysql> EXPLAIN SELECT * FROM country_languages_2;\r\n+----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+\r\n| id | select_type | table\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | type\u00a0\u00a0 | possible_keys | key\u00a0\u00a0\u00a0\u00a0 | key_len | ref\u00a0 | rows | Extra\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+\r\n|\u00a0 1 | PRIMARY\u00a0\u00a0\u00a0\u00a0 | <derived2>\u00a0\u00a0\u00a0\u00a0\u00a0 | system | NULL\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL\u00a0\u00a0\u00a0 | NULL |\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | Country\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | const\u00a0 | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 1 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\r\n|\u00a0 2 | DERIVED\u00a0\u00a0\u00a0\u00a0 | CountryLanguage | ref\u00a0\u00a0\u00a0 | PRIMARY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PRIMARY | 3\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0 8 | Using where; Using index |\r\n+----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+\r\n<\/pre>\n<\/blockquote>\n
Since views are allowed to call stored routines (Justing used this to call upon CONNECTION_ID()<\/strong>), and since stored routines can use session variables, we can take advantage and force the view into filtering out irrelevant rows before these accumulate to temporary tables and big joins.<\/p>\n
Back in the customer’s office, we witnessed, what with their real data and multiple views, a reduction of query times from ~30 minutes to a few seconds.<\/p>\n
Another kind of use<\/h4>\n
Eventually we worked to make better view definitions and query splitting, resulting in clearer code and fast queries, but this solution plays nicely into another kind of problem:<\/p>\n
Can we force different customers to see different parts of a given table? e.g., only those rows that relate to the customers?<\/p>\n
There can be many solutions: different tables; multiple views (one per customer), stored procedures, what have you. The above provides a solution, and I’ve seen it in use.<\/p>\n","protected":false},"excerpt":{"rendered":"
Justin’s A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer’s site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[15,52,21,59,20],"class_list":["post-1328","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-execution-plan","tag-performance","tag-sql","tag-stored-routines","tag-syntax"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-lq","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1328","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=1328"}],"version-history":[{"count":15,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1328\/revisions"}],"predecessor-version":[{"id":2462,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/1328\/revisions\/2462"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=1328"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=1328"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=1328"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}