Mysql – Slow MySQL query, not sure why

MySQLqueryview

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?

Best Answer

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.

Related Question