List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 24 1999 7:00pm
Subject:Re: problem with indexing
View as plain text  
Adam Powell wrote:
> 
> Hi there, I am in desperate need of speeding up a lookup on our system...
> from one table, however many different columns are accessed (about 5).
> Could anybody give me some pointers on which columns to index, as currently
> none are, and I guess this is slowing it down a lot.
> 
> Here is the main script (in PHP) it is accessed about 300 times a second.
> The three different queries below are pretty much accessed equally.  I am
> using PHP v3.0.6 and mySQL 3.22.20 on Sun OS 5.6.  Our database server has
> 2x300 Mhz RISC processors and 1GB RAM - if that helps.
> 
>   if ( $button == "yes" ) {
>     $str="SELECT * FROM banners WHERE flags&1=1 AND active!=-2";
>   } else if ( $d->approved == 3 ) {
>     $str="SELECT banners.* FROM banners, rotation WHERE
> rotation.username='$username' AND
> rotation.banner_name=banners.banner_name AND banners.active!=-2";
>   } else {
>     $str="SELECT * FROM banners WHERE (clicks_today < max_day OR max_day=-1)
> AND active=$rand";
>   }
> 
> Any ideas on how to speed this up with indexes?  The description of the
> table is below...
> 
> mysql> describe banners;
> +--------------+-----------------+------+-----+---------+-------+
> | Field        | Type            | Null | Key | Default | Extra |
> +--------------+-----------------+------+-----+---------+-------+
> | username     | char(10) binary |      | MUL |         |       |
> | banner_name  | char(10) binary |      | PRI |         |       |
> | url          | char(120)       |      |     |         |       |
> | views_total  | int(11)         |      |     | 0       |       |
> | clicks_total | mediumint(9)    |      |     | 0       |       |
> | views_today  | int(11)         |      |     | 0       |       |
> | clicks_today | mediumint(9)    |      |     | 0       |       |
> | date_started | char(11)        |      |     |         |       |
> | active       | tinyint(4)      |      |     | 0       |       |
> | max_day      | mediumint(9)    |      |     | 0       |       |
> | image_url    | char(120)       |      |     |         |       |
> | alt_text     | char(100)       |      |     |         |       |
> | flags        | smallint(6)     |      |     | 0       |       |
> | max_rate     | smallint(6)     |      |     | 0       |       |
> | payment_rate | smallint(6)     |      |     | 0       |       |
> +--------------+-----------------+------+-----+---------+-------+
> 
> Adam
> 
>

I can think of a couple of things:

- add an index spanning flags and active (will slow down
inserts, though)
- add an index spanning max_day and clicks_today
- replace username char(10) with user_id int not null
primary key

I would also recommend testing the changes on a
developement machine first and benchmarking them
thoroughly before putting it into production.

-- 
Sasha Pachev
http://www.sashanet.com/ (home)
http://www.direct1.com/ (work)
Thread
problem with indexingAdam Powell24 May
  • Re: problem with indexingSasha Pachev24 May
  • Re: problem with indexingAdam Powell25 May
    • Re: problem with indexingMichael Widenius2 Jun
  • Re: problem with indexingSasha Pachev25 May
    • Duplicate entriesRay Brighenti25 May
      • Re: Duplicate entriesThimble Smith25 May
      • Duplicate entries the Log resultsRay Brighenti25 May
      • Re: Duplicate entriesErik E Rantapaa25 May
      • Re: Duplicate entriesChris Trown25 May
  • problem with indexingMichael Widenius2 Jun