In the last episode (Sep 23), Waseem said:
> I am posting this problem again. I have slow queries, which are caused by
> just one table. Table has more than 1.4 million rows and increasing. Here is
> the table structure and sample select queries. Inserting into the table is
> no problem. Its only the select statements that are slow sometimes.
>
> |_Field____|_Type__________|_Null_|_Key_|_Default_|_Extra_|
> | ID | mediumint(9) | | MUL | 0 | |
> | t_stamp | timestamp(14) | YES | | NULL | |
> | host | varchar(255) | | | | |
>
> QUERY 1:
> SELECT *, count(*) as qnt, DATE_FORMAT(t_stamp, '$date_format') as
> t_stamp_f FROM checklog WHERE ID = $GLOBALS[ID] GROUP BY t_stamp_f
> ORDER BY t_stamp DESC LIMIT 8
I assume this runs quick, since its WHERE clause references an indexed
field. How many records have the same ID? 10? 5000? 100000?
> QUERY 2:
> SELECT COUNT(*) as log, ID FROM checklog WHERE t_stamp like
> '$today%' GROUP BY ID ORDER BY log DESC LIMIT 20";
I assume this is the slow-running one, since its WHERE clause
references t_stamp which isn't indexed, so it has to do a full table
scan. Try putting an index on t_stamp.
If my assumtions are wrong, give us more information. An EXPLAIN on
each select would help, plus the actual running time and sample output
of each query. Also run myisamchk -a on all your .MYI files so mysql
can gather some frequency statistics on your tables. It probably won't
help in this case since they're one-table queries, but you never know.
--
Dan Nelson
dnelson@stripped
