Hello everyone,
The app server in this case is PHP, and the database is MySQL 5.0.22
on RedHat linux
I've got a database with about 7.5K records in it that I expect to
start growing very quickly ~10-12K records per day. The storage
engine is InnoDB. This table is growing quickly and will continue to
grow for a long time. This table stores comments (as you can see from
the structure) and is being used to display a list of comments based
on a users affiliations.
The structure is approximately this - I'm leaving out unrelated columns:
id int - primary key - auto increment
gid bigint - indexed
comment varchar
date_posted timestamp
I run a query with the following form
select comment, gid, date_posted from tbl where id in (select max(id)
from tbl where gid in ( 1234,2345,3456 .. 7890 ) group by gid);
I have an index on gid and id is the primary key
When I describe the query with about 50 gid values inserted (where
indicated above) I get the following:
+----+--------------------+-------+-------+---------------+---------
+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
+----+--------------------+-------+-------+---------------+---------
+---------+------+------+--------------------------+
| 1 | PRIMARY | tbl | ALL | NULL | NULL |
NULL | NULL | 7533 | Using where |
| 2 | DEPENDENT SUBQUERY | tbl | range | idx_gid | idx_gid |
9 | NULL | 58 | Using where; Using index |
+----+--------------------+-------+-------+---------------+---------
+---------+------+------+--------------------------+
Running the query on a production machine with sufficient memory and
horsepower (box is only 20% utilized) it still takes 3 seconds to run
- obviously not quick enough for web use.
What I really need is the most recent comment from each group based
on a variable set of gid's that change from user to user.
Any thoughts on how to tweak this to avoid the full table scan? Thank
you in advance for your assistance.
Erik Giberti
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
