List:General Discussion« Previous MessageNext Message »
From:Adam Powell Date:July 11 1999 11:10am
Subject:Re: [PHP3] optimising a query
View as plain text  
What we are trying to do is take the load off the database and put it in the
script.  We have MANY variables associated with the banner, like weighting,
size, subjects it favours, subjects it rejects, time targeting and so on...
we also have a cookie-based profile of the individual banner viewer that we
use to judge what would be the best banner for them to see at any one time.

The problem with all this stuff is that we cant put it in the SELECT
statement, as this would make it about 20 lines long and span about 3
tables, so what we are trying to do is do a SELECT * FROM banners, and then
do a loop that goes through each banner and assigns it points based on the
individual criteria, and then do a bit of fuzzy random logic to pick the
best one.  We are moving the processing overhead from our database server to
our individual banner servers.

So yes, we do need to do the entire SELECT statement, which is why I am
wondering how we can reduce the load...  we only have to select four
individual rows from the DB, is it quicker to do select a, b, c, d FROM...
instead of SELECT *?  I think it is, but I am not sure by what degree....
Also, if there is a criterion that always means a banner is inactive, would
it slow us down if we used SELECT a, b, c, d FROM banners WHERE
weighting=inactive?

Thanks,
Adam Powell,
CTO - Click Agents Corporation.
Web : http://www.clickagents.com
Email : adam@stripped

----- Original Message -----
From: Paul DuBois <paul@stripped>
To: Adam Powell <adam@stripped>; <mysql@stripped>;
<php3@stripped>
Sent: Sunday, July 11, 1999 7:47 PM
Subject: Re: [PHP3] optimising a query


> At 11:34 AM +0100 7/11/1999, Adam Powell wrote:
> >Hi there... I was just wondering about the database processing overhead
in
> >each of the following situations.  Basically I have a database of banners
> >(for an ad rotation script) that I want to SELECT from.  The load on the
> >database server got to high when I selected from ALL of them, so I split
> >them up into 4 sections and in 4 individual scripts did :
> >
> >SELECT * FROM banners WHERE section=1
> >SELECT * FROM banners WHERE section=2
> >SELECT * FROM banners WHERE section=3
> >SELECT * FROM banners WHERE section=4
> >
> >This helped get the load back down again.  However now I have come to a
> >point where I have to actually revert back to my old method as there are
a
> >few more things I wish to do, targeting functions that must include the
> >whole range of banners.
> >
> >Could anybody give me some pointers, and some tips in table design that
will
> >speed up SELECT * FROM banners.... are there any column types to avoid?
We
> >need to do about 200 lookups a second, and there are 160 rows in the
> >table...
>
> If you want specific optimizations, you'll need to show the WHERE clause
> of your query.  From the queries you show above, it looks like you're
> selecting a lot more rows that you need to, for example.  You're not
> really selecting a fourth of the rows (section = 1, 2, 3, or 4) for each
> query just to display banner ads, are you?
>
> --
> Paul DuBois, paul@stripped
> Northern League Chronicles: http://www.snake.net/nl/
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread7018@stripped
>
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail mysql-unsubscribe@stripped instead.
>
>

Thread
optimising a queryAdam Powell11 Jul
  • Re: [PHP3] optimising a queryPaul DuBois11 Jul
    • Re: [PHP3] optimising a queryAdam Powell11 Jul
      • Re: [PHP3] optimising a queryAriel11 Jul
    • Re: [PHP3] optimising a querySasha Pachev12 Jul
    • Re: [PHP3] optimising a queryAdam Powell12 Jul
    • Re: [PHP3] optimising a querySasha Pachev12 Jul
  • Re: optimising a querySasha Pachev12 Jul