Hello Jeremy, Friday, January 25, 2002, 11:00:43 AM, you wrote:
>> Why query type >> >> select count(*) from table_name where key_field between 'min' and >> 'max' so slow? >> >> example: >> >> mysql> select count(*) from Textes where ID between 7937 and 45061; >> +----------+ >> | count(*) | >> +----------+ >> | 36360 | >> +----------+ >> 1 row in set (0.42 sec) JZ> How large is your key_buffer? key_buffer_size 8388600 JZ> How many rows are in the table? not so many. 36362 rows. but big rows. table size 255Mb. >> mysql> explain select count(*) from Textes where ID between 7937 and 45061; >> >+--------+-------+---------------+---------+---------+------+-------+-------------------------+ >> | table | type | possible_keys | key | key_len | ref | rows | Extra > | >> >+--------+-------+---------------+---------+---------+------+-------+-------------------------+ >> | Textes | range | PRIMARY | PRIMARY | 4 | NULL | 36361 | where used; >Using index | >> >+--------+-------+---------------+---------+---------+------+-------+-------------------------+ >> 1 row in set (0.00 sec) >> >> why explain much faster? JZ> Because it doesn't actually run the query. >> why rows from expalain not exactly match count(*)? JZ> Because it's just an esitmate which helps MySQL decide the fastest way JZ> to execute the query. It's clean. But why index structure don't allow count exactly how many rows between keys? >> Will It's be fixed in 4.0? JZ> Hard to say. We don't know what the problem is yet. AFAIK, in 4.0 index structure will be changed - for allow 'order by ... desc' optimization. Will new structre allow fast count exactly how many rows between keys? it very common query: select count(*) from Textes where path like '/os/%'; select count(*) from Textes where SectID=6; and many other. JZ> Can you run "ANALYZE TABLE Textes" and see if that helps? of course: mysql> ANALYZE TABLE Textes; +---------------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------+---------+----------+----------+ | NewWeb.Textes | analyze | status | OK | +---------------+---------+----------+----------+ 1 row in set (0.93 sec) JZ> Jeremy -- Best regards, Artem mailto:[EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
