List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:July 12 1999 5:38am
Subject:Re: [PHP3] optimising a query
View as plain text  
Adam Powell wrote:
> 
> 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)
> 

<cut>

a few suggestions:

- see if date_started could be converted to the native MySQL date type
- it seems to me that your table has the data of two classes - static
and dynamic.  You could speed up the update by doing the following:
  a) put the relatively static data in one table and dynamic in the
other and use a key to join the tables when you want to see the combined
data. url, image_url, user_name, banner_name, max_rate, payment_rate
would probably be in the static category, and clicks_today,
clicks_total, views_today and views_total would be dynamic
  b) set up the updates so that  you will always have the id of the
record without having to look it up in the static table if possible
  c) do not forget to use an int id for the primary key to on join
rather than a char username

- Some ideas to optimize your URL fields:

   a) never store http://
   b) store the server and the file name separately, then you can use
some smart hashing on the domain name
   c) you could store the names of all servers in one table along with
their id and store just the id in your banners table. When the name is
needed, do a join - it should be pretty fast

I suspect that your bottleneck has to do something with the fact that
every hit on a banner requires an update, and the table you are updating
is too large. You should see a lot of improvement in performance once
you reduce the size of the table you are updating.




-- 
Sasha Pachev
http://www.sashanet.com
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