Re: Re: Re: Re: Migrating to static structure?

Yeap, all the indices are set for the tables.
The problem is, that some of these selects are making joins from 3-5 tables and that's probably slows MySQL down.
DB servers are also kinda full-blown (as for current budget, running at PIII/600Mhz and 1GB of memory (this "slow" db is about 150MB big)).
We were concidering switching to PostgreSQL as it was slighty faster with these "5-lines-long-SELECTS" but on the other hand we're using the same MySQL servers with great MySQL preformance for our search engine (Postgres was not so good at fulltext search).

Peter

Comment on Re: Re: Re: Re: Migrating to static structure?

Replies are listed 'Best First'.
Re: Re: Re: Re: Re: Migrating to static structure? by Tomte (Priest) on Aug 01, 2002 at 12:23 UTC
some of these selects are making joins from 3-5 tables and that's probably slows MySQL down. Yepp, IIRC most indices on the 'later' tables in a join aren't used by MySQL (read that in the annotated html-manual). You may consider splitting the 5-lines-long select into smaller ones implementing the join logic in perl, that should provide a noticable speed-up gained by local changes to the perl-code. Coupled with a cache this should make things smooth for times to come. Implementing a cache without a db-speedup would be IMHO a half-legged solution, as some visitors would have to wait the 10+ secs on a cache-miss. regards, Tom	[reply]

Replies are listed 'Best First'.

Re: Re: Re: Re: Re: Migrating to static structure?
by Tomte (Priest) on Aug 01, 2002 at 12:23 UTC

some of these selects are making joins from 3-5 tables and that's probably slows MySQL down.

Yepp, IIRC most indices on the 'later' tables in a join aren't used by MySQL (read that in the annotated html-manual).

You may consider splitting the 5-lines-long select into smaller ones implementing the join logic in perl, that should provide a noticable speed-up gained by local changes to the perl-code.
Coupled with a cache this should make things smooth for times to come. Implementing a cache without a db-speedup would be IMHO a half-legged solution, as some visitors would have to wait the 10+ secs on a cache-miss.

[reply]