At first blush, your problem would appear to concern the lack of index-use.
That's where I would begin my investigation. It might be painstaking, but
I would do something like this:
For each view
Look at the Join(s) and see what columns are being joined
Look at the tables and see what columns are being indexed
If any columns are not indexed that should be, create said index(es)
Next view
Of course, this process could be optimized by looking at the views in terms
of their frequency of use.
Finally, you didn't mention what sort of front end you're using. It's
possible that you might benefit by using stored procedures rather than
views. That switch would of course cost you some time invested in changing
the front end to pass explicit parameters.
Hope this helps,
Arthur
On Thu, Dec 29, 2011 at 12:50 PM, Bruce Ferrell <bferr...@baywinds.org>wrote:
> Hi all,
>
> I've got some semi-general questions on the topics in the title. What I'm
> looking for is more in the line of theory than query specifics. I am but a
> poor peasant boy.
>
> What I have is an application that makes heavy use of views. If I
> understand views correctly (and I may not), views are representations of
> queries themselves. The guy who wrote
> the app chose to do updates and joins against the views instead of against
> the underlying tables themselves.
>
> I've tuned to meet the gross memory requirements and mysqltuner.pl is
> saying that 45% of the joins are without indexes. With the slow query logs
> on and queries_without_indexes,
> I'm frequently seeing updates that often take more that 2 seconds to
> complete... Often MUCH longer (how does 157 seconds grab you?).
>
> So, with that background, what would you do next and is it possible this
> use of views, in this way is a significant contributor to the problem?
>
> Bruce Ferrell
>
>
--
Cell: 647.710.1314
Thirty spokes converge on a hub
but it's the emptiness
that makes a wheel work
-- from the Daodejing
