in reply to Re: Re: Migrating to static structure?
in thread Migrating to static structure?

As a polite question from someone fallen in this pit once: Are you sure the indexes/indices(sp?) set for the tables are used in your queries?
It seems unbelievable how well a database can operate if the indices set match the queries made. (From >10sec to <0.02 in my case for )

another option is to look, if you can optimize the database-server itself, if not alreday done, to better accomodate to your special circumstances.

Maybe a look into the slashcode or everything helps you find a cache solution.

regards,
tomte
  • Comment on Re: Re: Re: Migrating to static structure?

Replies are listed 'Best First'.
Re: Re: Re: Re: Migrating to static structure?
by pit (Initiate) on Aug 01, 2002 at 12:09 UTC
    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
[reply]
      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]

In Section Seekers of Perl Wisdom