MySQL View with count() doing Full Table Scan

Question

Consider the following view

CREATE VIEW `my_view` AS
SELECT
    a.id,
    (SELECT
            COUNT( b.id )
        FROM
            another_table b
        WHERE
            b.a_id = a.id AND b.other_column = 'ABC'
    ) AS counter
FROM
    some_table a

some_table has millions of rows and another_table has an index on a_id + other_column.

Now, consider the following SQL:

SELECT vw.*
FROM some_table a
LEFT JOIN my_view vw on vw.id = a.id
WHERE a.id = 12345

Can anyone tell me why this sql query is doing a full table scan on some_table? The goal is to have a view that returns the number of rows from another_table where the other_column equals 'ABC'.

If I replace LEFT JOIN my_view in the query with LEFT JOIN ( the AS CLAUSE from the view ), it does NOT do a full table scan and uses the a_id + other_column index.

I'm stumped. Any help is appreciated.

Answer 1

Views in MySQL generally . . . [I don't want to use that word] . . . don't perform as well as one would expect or want. You think: "Oh, the SQL engine will just plug the view definition into my query and then optimize it." You are not totally wrong. MySQL does that. It is called the MERGE algorithm for views.

Unfortunately, there are not many useful views where the MERGE algorithm can be used. Basically, you can use it to rename fields and pre-compute expressions on a single table.

The alternative algorithm is the TEMPTABLE algorithm. As you have seen, this creates a temporary table and hence a full table scan.

Of course, you can read more about this in the documentation.

I do not believe that there is any simple work-around. You could create a summary table and keep it up to date using triggers. But I don't view that as "simple".