List:General Discussion« Previous MessageNext Message »
From:Adam Powell Date:July 11 1999 4:35pm
Subject:Re: [PHP3] optimising a query
View as plain text  
Hi - in answer to your questions, firstly the database and the banner script
ARE running on different machines.  So we want to move the load off the
database onto the banner machines, as it is possible to use a round-robin
method and add additional banner servers.  Since we want all our data to be
centralised, we need to only have one database server.

Here is the description of the banners table.  Im trying to make a SELECT *
FROM banners as fast as possible...  I just need to know all the ways in
which it could be possibly slowed down!

Adam

mysql> describe banners;
+--------------+-----------------+------+-----+---------+-------+
| Field        | Type            | Null | Key | Default | Extra |
+--------------+-----------------+------+-----+---------+-------+
| username     | char(10) binary |      | MUL |         |       |
| banner_name  | char(10) binary |      | PRI |         |       |
| url          | char(180)       |      |     |         |       |
| views_total  | int(11)         |      |     | 0       |       |
| clicks_total | mediumint(9)    |      |     | 0       |       |
| views_today  | int(11)         |      |     | 0       |       |
| clicks_today | mediumint(9)    |      | MUL | 0       |       |
| date_started | char(11)        |      |     |         |       |
| active       | tinyint(4)      |      |     | 0       |       |
| max_day      | mediumint(9)    |      | MUL | 0       |       |
| image_url    | char(180)       |      |     |         |       |
| alt_text     | char(100)       |      |     |         |       |
| flags        | smallint(6)     |      |     | 0       |       |
| max_rate     | smallint(6)     |      |     | 0       |       |
| payment_rate | smallint(6)     |      |     | 0       |       |
+--------------+-----------------+------+-----+---------+-------+
15 rows in set (0.00 sec)

----- Original Message -----
From: Sasha Pachev <sasha@stripped>
To: Adam Powell <adam@stripped>
Cc: <mysql@stripped>
Sent: Sunday, July 11, 1999 10:57 PM
Subject: Re: [PHP3] optimising a query


> Adam Powell wrote:
> >
> > 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
> >
>
> The only time shitfing the load from the database to a script could
> possibly make sense that I can think of is when your database is not
> running on the same machine as the script. Even then, this most of the
> time will not be the best solution.
>
> Ideally, you want to incorporate all of your operations natively into
> the database. You may consider modifying the sources of mysql to
> incorporate all the fancy processing. If you do not feel competent
> enough, or just do not have the time, you may consider getting TCX to do
> it for you. Don't know if the extended support will cover it. In any
> case, I imagine you can work out some kind of a deal with TCX to help
> you.
>
> --
> Sasha Pachev
> http://www.sashanet.com
>
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail mysql-thread7028@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