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
