List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:September 23 2000 4:22am
Subject:Re: Slow Queries (Again)
View as plain text  
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
Thread
Slow Queries (Again)Waseem23 Sep
  • Re: Slow Queries (Again)Jeremy D. Zawodny23 Sep
    • RE: Slow Queries (Again)Waseem23 Sep
  • Re: Slow Queries (Again)Dan Nelson23 Sep