PocketChange
Registered: Jul 2008
Posts: 2037 |
08-31-12 10:03 PM
This is one of those exception to the "rules" cases. The views are simple and result columns are all contained in the index... just the group by (1 min, 5 min, 10min etc) clause changes.
When doing an indexed lookup of a row, the usual procedure is to do a binary search on the index to find the index entry, then extract the rowid from the index and use that rowid to do a binary search on the original table. Thus a typical indexed lookup involves two binary searches.
If, however, all columns that were to be fetched from the table are already available in the index itself, SQL will use the values contained in the index and will never look up the original table row. This saves one binary search for each row and can make many queries run twice as fast.
For this specific case calling a 1 min view, 2 min view, 5 min view etc performance is already optimized with one table simplicity. K.I.S.S.
Quote from Kevin Schmit:
Especially on MySQL, which came by views lately, and still doesn't do them too well. Even worse when you have views referencing views. Use "EXPLAIN" to see what is going on with them, you will be shocked at the inefficiency of what is going on under the hood, at least compared to a highly optimized commercial database.
|
