I understand the restrictions, due to the way index maintenance works. The only one I'm bothered about is the inability to use a LEFT JOIN when the right side is pre-aggregated, as it should be possible to do delta updates in that case.
Actually just realized why it doesn't work for you, because Votes needs aggregation and Posts also does.
Microsoft (and others) did all the research work to make outer join indexed views possible, and even indexed views on indexed views. It hasn't made it to the final product so far.
Of course, it's one thing to make the thing work in isolation.
Efficiently matching indexed views to arbitrary query trees is harder to do efficiently beyond simple inner joins (and that matching is already p darn clever).
Have you tried doing SELECT * FROM x.yyy_backup after the message that the table was created? Generally speaking database management systems don't lie; it sure looks like it created the table. — Hannah Vernon ♦1 min ago
having said that, it is MySQL so who knows. Maybe it is lying.
heh, Reopen queue has 1 item. Someone went and edited a question you'd just closed
following your advice
Without knowing anything about how those backups are taken it's pretty well impossible to provide an accurate answer. In short, you'll need to edit your question to provide more detail. — Hannah Vernon ♦23 mins ago
@PaulWhite A regular LEFT JOIN is difficult, as it requires a check in the delta deletes as to whether there are any other rows for the left table, otherwise it needs to null out instead of deleting, and delta inserts likewise need to delete the null row. A grouped join is much easier, as there is only ever one row or none from the right table per row in the left table.
