MySql view is slow comparatively to their base query

Question

I have created a view in MySQL as

create view vtax
as
SELECT * FROM table1 
union
SELECT * FROM table2;

Where in table 1 have 800000 records, and table2 have 500000 records, when I run the independent queries the result are returned with 0.078 secs, but when I am running them through the view it goes in toss taking time more than 10-15 secs.

select * from vtax where col1=value; -- takes more than 10-15 secs

select * from table1 where col1=value; -- takes 0.078 secs

select * from table2 where col1=value; -- takes 0.078 secs

I have created indexes on the tables separately.

Any help/idea what should be done.

Are you sure, that everybody knows what "Lakh" is? People outside India rarely know the indian numbering system. — Hubert Schölnast, Apr 25 '15 at 07:27
Does this answer your question? [MySql view is very slow. Why?](https://stackoverflow.com/questions/62832483/mysql-view-is-very-slow-why) — underscore_d, Jul 10 '20 at 11:04

score 3 · Answer 1 · answered Apr 25 '15 at 07:26

3

UNION

performs a distinct over your results (often a sort). Can you use

UNION ALL

? (ie. are the rows distinct?)

answered Apr 25 '15 at 07:26

Mitch Wheat

295,962
43
465
541

thanks, tried union all also, but not much impact on execution time – NKS Apr 25 '15 at 07:30

score 0 · Answer 2 · edited May 23 '17 at 11:43

0

You should compare apples with apples. Unions are often much slower than simple queries. Compare the an union with the view. You will notice that the standard union query is slow as well. Probably the optimizer has problems with the decision for the optimal path. Check some other questions like: Why are UNION queries so slow in MySQL?

edited May 23 '17 at 11:43

Community

1
1

answered Apr 25 '15 at 08:02

Udo Held

12,314
11
67
93

score 0 · Answer 3 · answered Apr 25 '15 at 08:06

0

As stated in the comments a view isn't indexed in MySQL.

If you use the union in the query:

SELECT * FROM table1 WHERE col1 = 'value'
UNION
SELECT * FROM table2 WHERE col1 = 'value'

Then indexes (if there are any) can be used.

answered Apr 25 '15 at 08:06

Gervs

1,397
9
8

MySql view is slow comparatively to their base query

3 Answers3