from Amazon
Friday, August 28, 2009
MySQL views are terrible
I’ve been working with MySQL a bit lately, and that’s only because that’s the database offered by my webhost. So far, I’ve seen two huge limitations, and both involves views (my preferred process).
The first is doing a left join rather than an inner join. Even though the number of returned records were virtually identical, turning the outer into an inner changed the run time from 51 seconds to 0.3 seconds. It was a pretty basic view.
The second one is joining a table to a view. It seems that the view doesn’t use any of the underlying tables’ indexes. So, what I’m left with is merging a 100,000 row indexed table to a 3000 row view, and it’s doing the join without the use of nay index. Even MsAccess is faster.
Suggestions?