List:General Discussion« Previous MessageNext Message »
From:Adam Powell Date:May 24 1999 6:32pm
Subject:problem with indexing
View as plain text  
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

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