Re: How to get faster queries in the database?

On Sep 9, 2012, at 16:36, Andre Lopes <lopes80andre(at)gmail(dot)com> wrote:

> Hi,
>
> I've developed a system that was not to work online, but now it is
> online and it is degrading due to bad design choices.
>
> Here is the thing. I've the database build in "vertical mode". I will
> justo explain what I mean with that.
>
>
> My question is, how can I get the queries faster. It is possible to do
> it with INDEXES or it is better to search for other approach? I simple
> SELECT using a LIMIT do paginate is taking 5 minutes. Any ideas on
> where to start?
>

Indexes are worth looking at always but to address the narrow example you show:

Instead of a live view you need to "materialize" it into a permanent table and query the table. Basically you cache the slow query - and deal with all the consequences of working with a cache (mainly stale data).

There are so many aspects to this that any online advice, given without seeing the whole system and environment, is going to be vague and possibly incorrect. If you know it is bad you should decide how it should look and then figure out how to get there by rewriting application and migrating data.

David J.