MySQL – Joining table with different charset in a view

Ownership chains work down not up. If you give the user rights to the view, they can query the view without needing rights to the objects under the view, in this case the table. It doesn't work the other direction. The user always needs rights to the object they are attempting to access. All the objects under that object will come automatically.

There are a few things going on here:

• your CREATE VIEW statement contains a SUM() function. Even though your ALGORITHM is specified as MERGE, it is really going to be TEMPTABLE as discussed in the documentation here:

  If the MERGE algorithm cannot be used, a temporary table must be used instead. MERGE cannot be used if the view contains any of the following constructs: Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)

• So we're using temporary tables. I suspect you're not seeing 'blocking' due to this benefit of TEMPTABLE algorithm:

  A reason to choose TEMPTABLE explicitly is that locks can be released on underlying tables after the temporary table has been created and before it is used to finish processing the statement. This might result in quicker lock release than the MERGE algorithm so that other clients that use the view are not blocked as long.

• However, the problem with the TEMPTABLE algorithm is that they do not have/use indexes [src]:

  View processing is not optimized:

  It is not possible to create an index on a view.

  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).

So now we need to know the underlying structure of your table as to why the direct query blocks:

• If it's not InnoDB, consider switching to use row-level locks instead of table-locks of MyISAM
• If there's no index on (number, bet_amount) consider creating one.

If the above points have already been done, and you still for some reason have blocking on a query directly against the salestable table, then you can institute a poor-man's materialized view by creating triggers that modify a temporary table with each number and a running total.