Skip to content

# Views: better performance with condition pushdown

MySQL Execution plan Performance SQL Stored routines Syntax May 20, 2010

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 a LOT of subqueries, temporary tables, and vast amounts of data, even if only to return with a couple of rows.

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.

Let’s use the world database to illustrate. Look at the following view definition:

>     CREATE
>       ALGORITHM=TEMPTABLE
>     VIEW country_languages AS
>       SELECT
>         Country.CODE, Country.Name AS country,
>         GROUP_CONCAT(CountryLanguage.Language) AS languages
>       FROM
>         world.Country
>         JOIN world.CountryLanguage ON (Country.CODE = CountryLanguage.CountryCode)
>       GROUP BY
>         Country.CODE;

The view presents with a list of spoken languages per country. The execution plan for querying this view looks like this:

>     mysql> EXPLAIN SELECT * FROM country_languages;
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+
>     | id | select_type | table           | type   | possible_keys | key     | key_len | ref                               | rows | Extra                                        |
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+
>     |  1 | PRIMARY     |       | ALL    | NULL          | NULL    | NULL    | NULL                              |  233 |                                              |
>     |  2 | DERIVED     | CountryLanguage | index  | PRIMARY       | PRIMARY | 33      | NULL                              |  984 | Using index; Using temporary; Using filesort |
>     |  2 | DERIVED     | Country         | eq_ref | PRIMARY       | PRIMARY | 3       | world.CountryLanguage.CountryCode |    1 |                                              |
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+

And, even if we only want to filter out a single country, we still get the same plan:

>     mysql> EXPLAIN SELECT * FROM country_languages WHERE Code='USA';
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+
>     | id | select_type | table           | type   | possible_keys | key     | key_len | ref                               | rows | Extra                                        |
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+
>     |  1 | PRIMARY     |       | ALL    | NULL          | NULL    | NULL    | NULL                              |  233 | Using where                                  |
>     |  2 | DERIVED     | CountryLanguage | index  | PRIMARY       | PRIMARY | 33      | NULL                              |  984 | Using index; Using temporary; Using filesort |
>     |  2 | DERIVED     | Country         | eq_ref | PRIMARY       | PRIMARY | 3       | world.CountryLanguage.CountryCode |    1 |                                              |
>     +----+-------------+-----------------+--------+---------------+---------+---------+-----------------------------------+------+----------------------------------------------+

So, we need to scan the entire country_language and country tables in order to return results for just one row.

#### A non-working solution

The solution offered by the developer was this:

>     CREATE
>       ALGORITHM=MERGE
>       VIEW country_languages_non_working AS
>       SELECT
>         Country.CODE, Country.Name AS country,
>         GROUP_CONCAT(CountryLanguage.Language) AS languages
>       FROM
>         world.Country
>         JOIN world.CountryLanguage ON
>           (Country.CODE = CountryLanguage.CountryCode)
>       WHERE
>         Country.CODE = @country_code
>       GROUP BY Country.CODE;

And follow by:

>     mysql> SET @country_code='USA';
>     Query OK, 0 rows affected (0.00 sec)

>     mysql> SELECT * FROM country_languages_2;
>     +------+---------------+----------------------------------------------------------------------------------------------------+
>     | CODE | country       | languages                                                                                          |
>     +------+---------------+----------------------------------------------------------------------------------------------------+
>     | USA  | United States | Chinese,English,French,German,Italian,Japanese,Korean,Polish,Portuguese,Spanish,Tagalog,Vietnamese |
>     +------+---------------+----------------------------------------------------------------------------------------------------+

So, pushdown a WHERE 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, for example, or OR statements) to allow for full scan in case the variable is undefined.

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.

#### A workaround

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:

>     DELIMITER $$
>     CREATE DEFINER=`root`@`localhost` FUNCTION `get_session_country`() RETURNS CHAR(3)
>         NO SQL
>         DETERMINISTIC
>     BEGIN
>       RETURN @country_code;
>     END $$
>     DELIMITER ;

>     CREATE
>       ALGORITHM=MERGE
>       VIEW country_languages_2 AS
>       SELECT
>         Country.CODE, Country.Name AS country,
>         GROUP_CONCAT(CountryLanguage.Language) AS languages
>       FROM
>         world.Country
>         JOIN world.CountryLanguage ON
>           (Country.CODE = CountryLanguage.CountryCode)
>       WHERE
>         Country.CODE = get_session_country()
>       GROUP BY Country.CODE;

And now:

>     mysql> SET @country_code='USA';
>     Query OK, 0 rows affected (0.00 sec)

>     mysql> SELECT * FROM country_languages_2;
>     +------+---------------+----------------------------------------------------------------------------------------------------+
>     | CODE | country       | languages                                                                                          |
>     +------+---------------+----------------------------------------------------------------------------------------------------+
>     | USA  | United States | Chinese,English,French,German,Italian,Japanese,Korean,Polish,Portuguese,Spanish,Tagalog,Vietnamese |
>     +------+---------------+----------------------------------------------------------------------------------------------------+
>     1 row in set, 1 warning (0.00 sec)

>     mysql> EXPLAIN SELECT * FROM country_languages_2;
>     +----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+
>     | id | select_type | table           | type   | possible_keys | key     | key_len | ref  | rows | Extra                    |
>     +----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+
>     |  1 | PRIMARY     |       | system | NULL          | NULL    | NULL    | NULL |    1 |                          |
>     |  2 | DERIVED     | Country         | const  | PRIMARY       | PRIMARY | 3       |      |    1 |                          |
>     |  2 | DERIVED     | CountryLanguage | ref    | PRIMARY       | PRIMARY | 3       |      |    8 | Using where; Using index |
>     +----+-------------+-----------------+--------+---------------+---------+---------+------+------+--------------------------+

Since views are allowed to call stored routines (Justing used this to call upon CONNECTION_ID()), 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.

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.

#### Another kind of use

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:

Can we force different customers to see different parts of a given table? e.g., only those rows that relate to the customers?

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.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
1
Leave a Reply

1 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
1 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Giuseppe Maxia

“Can we force different customers to see different parts of a given table? e.g., only those rows that relate to the customers?”
This takes me back 5 years ago, when I did something similar in multi-user arrays
http://datacharmer.blogspot.com/2005/12/mysql-5-general-purpose-routine_20.html

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.