3

I'm working with an intranet application for property tax collection, and I've hit a snag. The following view runs much slower than I'd expect:

create or replace view propertytaxessub0 as select
    propertysid,
    sum(amount) as totaltax
from taxes group by propertysid;

The "taxes" table is as follows:

create table taxes (
    sid                     int(10) unsigned not null auto_increment primary key,
    propertysid             int(10) unsigned not null,

    authority               char(30) not null default '',
    amount                  decimal(14,2) not null default 0,

    index (propertysid),
    index (authority)
) engine = innodb;

Here's a single-record lookup, and its EXPLAIN output:

mysql> select * from propertytaxessub0 where propertysid = 2;
+-------------+----------+
| propertysid | totaltax |
+-------------+----------+
|           2 |   121.97 |
+-------------+----------+
1 row in set (0.00 sec)

mysql> explain select * from propertytaxessub0 where propertysid = 2;
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
| id | select_type | table      | type  | possible_keys | key         | key_len | ref  | rows   | Extra       |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
|  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL        | NULL    | NULL |  53342 | Using where |
|  2 | DERIVED     | taxes      | index | NULL          | propertysid | 4       | NULL | 467217 |             |
+----+-------------+------------+-------+---------------+-------------+---------+------+--------+-------------+
2 rows in set (2.07 sec)

I have no idea what I've done wrong here. It's not a very powerful server, comparatively, but if you do this:

mysql> select sum(amount) as totaltax from taxes where propertysid = 2;
+----------+
| totaltax |
+----------+
|   121.97 |
+----------+
1 row in set (0.00 sec)

... it runs just fine.

I'm coming at this from the MS Access side of the house, where I created named queries all the time and used them this way to good effect. What am I missing here?

3
  • So you want to improve your query time on the view (0.00 sec) so as to be as fast as a direct select (0.00 sec)? Sep 21, 2012 at 15:09
  • If that's possible, yes. Or alternately, I need a way to do the same thing efficiently. The view is used as a sub-select in another query, along with another similar view which performs slightly better (but still not well). I'd be happy with 0.1 seconds, just not 2 or more. I used views for this to avoid having a single query with a GROUP BY clause that repeats the thirty or forty field names of the combined query all over again.
    – Solomoriah
    Sep 21, 2012 at 15:37
  • Looking at my original post, I see I led you to confusion. The time listed on the single-record lookup is not correct, and I'm not sure how it got in there. On the production server, it took around 0.9 seconds to process the query, while on my development machine it takes more than a second. Another similar query takes over 2 seconds to process. I'm guessing I ran it twice (so that caching made it look quite a bit better) and pasted the wrong copy here.
    – Solomoriah
    Sep 22, 2012 at 4:06

1 Answer 1

2

From the look of the select query in the view

SUGGESTION #1 : Don't use VIEWS

Views are notorious for acting up with Query Optimization

According to MySQL Documentation

Indexes can be used for views processed using the merge algorithm. However, a view that is processed with the temptable algorithm is unable to take advantage of indexes on its underlying tables (although indexes can be used during generation of the temporary tables).

Percona calls Views a Performance Troublemaker

SUGGESTION #2 : Use another index (Optional)

You definitely need an additional index to assist the query

ALTER TABLE taxes ADD INDEX propertysid_amount_ndx (propertysid,amount);
ALTER TABLE taxes DROP INDEX propertysid;

That way, all the data needed for the view are in the index only. The other two indexes are not enough. Why do I say that? Even though the propertysid index was used, the amount has to retrieved from the table. Essentially, the query passes through both the index and the table.

2
  • Is it necessary to drop the propertysid index? I understand the point of the combined index (clever, that) but not why you drop the other one.
    – Solomoriah
    Sep 22, 2012 at 3:59
  • Doing only suggestion 2 dropped the processing time from over a second on my development machine to about 0.58 seconds.
    – Solomoriah
    Sep 22, 2012 at 4:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.