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?