Description:
if i create a view with a group statement and i execute a query with a where condition.
select * from v3 where f1='foobarbaz';
Mysql doesn't filter before the results ,but after.
Sorry for my bad english.
How to repeat:
create table t1 (f1 char(15), index(f1));
insert into t1 values('foo'), ('bar'), ('az'), ('foobarbaz');
create table t2 (f1 char(15), index(f1));
insert into t2 values('foo'), ('bar'), ('az'), ('foobarbaz');
create view v1 as select f1 from t1;
create view v2 as select t1.f1 from t1,t2;
create view v3 as select t1.f1 from t1,t2 group by t1.f1;
explain select * from t1 where f1='foobarbaz';
explain select * from v1 where f1='foobarbaz';
mysql> explain select * from v2 where f1='foobarbaz';
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
| 1 | SIMPLE | t1 | ref | f1 | f1 | 16 | const | 1 | Using where; Using index |
| 1 | SIMPLE | t2 | index | NULL | f1 | 16 | NULL | 4 | Using index |
+----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+
mysql> explain select * from v3 where f1='foobarbaz';
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
| 2 | DERIVED | t1 | index | NULL | f1 | 16 | NULL | 4 | Using index; Using temporary; Using filesort |
| 2 | DERIVED | t2 | index | NULL | f1 | 16 | NULL | 4 | Using index |
+----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+
3
Description: if i create a view with a group statement and i execute a query with a where condition. select * from v3 where f1='foobarbaz'; Mysql doesn't filter before the results ,but after. Sorry for my bad english. How to repeat: create table t1 (f1 char(15), index(f1)); insert into t1 values('foo'), ('bar'), ('az'), ('foobarbaz'); create table t2 (f1 char(15), index(f1)); insert into t2 values('foo'), ('bar'), ('az'), ('foobarbaz'); create view v1 as select f1 from t1; create view v2 as select t1.f1 from t1,t2; create view v3 as select t1.f1 from t1,t2 group by t1.f1; explain select * from t1 where f1='foobarbaz'; explain select * from v1 where f1='foobarbaz'; mysql> explain select * from v2 where f1='foobarbaz'; +----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+ | 1 | SIMPLE | t1 | ref | f1 | f1 | 16 | const | 1 | Using where; Using index | | 1 | SIMPLE | t2 | index | NULL | f1 | 16 | NULL | 4 | Using index | +----+-------------+-------+-------+---------------+------+---------+-------+------+--------------------------+ mysql> explain select * from v3 where f1='foobarbaz'; +----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+ | 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 4 | Using where | | 2 | DERIVED | t1 | index | NULL | f1 | 16 | NULL | 4 | Using index; Using temporary; Using filesort | | 2 | DERIVED | t2 | index | NULL | f1 | 16 | NULL | 4 | Using index | +----+-------------+------------+-------+---------------+------+---------+------+------+----------------------------------------------+ 3