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)
