Hello mysql list, Could anybody explain why index stops to work in the query SELECT SUM(...) WHERE ... with encreasing the size of range, but continue to work in the analog query SELECT COUNT(*) WHERE ... ?
See later: In the table ip_summary_d: PRIMARY KEY = (date,....): mysql> explain select SUM(In_byts) from ip_summary_d where date > '2001-10-26'; +--------------+-------+---------------+---------+---------+------+---------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | | +--------------+-------+---------------+---------+---------+------+---------+------------+ | ip_summary_d | range | PRIMARY | PRIMARY | 3 | NULL | 2366655 | where |used | +--------------+-------+---------------+---------+---------+------+---------+------------+ 1 row in set (0.00 sec) mysql> explain select SUM(In_byts) from ip_summary_d where date > '2001-10-25'; +--------------+------+---------------+------+---------+------+----------+------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +--------------+------+---------------+------+---------+------+----------+------------+ | ip_summary_d | ALL | PRIMARY | NULL | NULL | NULL | 13974447 | where used | +--------------+------+---------------+------+---------+------+----------+------------+ 1 row in set (0.00 sec) BUT (!!!): mysql> explain select count(*) from ip_summary_d where date > '2001-10-25'; +--------------+-------+---------------+---------+---------+------+---------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | | +--------------+-------+---------------+---------+---------+------+---------+-------------------------+ | ip_summary_d | range | PRIMARY | PRIMARY | 3 | NULL | 2572752 | where |used; Using index | +--------------+-------+---------------+---------+---------+------+---------+-------------------------+ 1 row in set (0.00 sec) WITH count(*) exept SUM(In_bytes) it WORKS AGAIN !!! EVEN MORE FOR ALL TABLE: mysql> explain select count(*) from ip_summary_d where date > '2001-1-1'; +--------------+-------+---------------+---------+---------+------+----------+-------------------------+ | table | type | possible_keys | key | key_len | ref | rows | Extra | | +--------------+-------+---------------+---------+---------+------+----------+-------------------------+ | ip_summary_d | range | PRIMARY | PRIMARY | 3 | NULL | 13974447 | where |used; Using index | +--------------+-------+---------------+---------+---------+------+----------+-------------------------+ 1 row in set (0.00 sec) Adjusting "key_buffer_size" variable has no result. Other infomation: ----------------- mysql-3.23.44-pc-linux-gnu-i686 2*Pentium3-750Mhg 512Mb memory Large Mysql Model: set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=256 set-variable = sort_buffer=1M set-variable = record_buffer=1M set-variable = myisam_sort_buffer_size=64M set-variable = thread_cache=8 mysql> show keys from ip_summary_d; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | |Cardinality | Sub_part | Packed | Comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ | ip_summary_d | 0 | PRIMARY | 1 | date | A | | 128 | NULL | NULL | | | ip_summary_d | 0 | PRIMARY | 2 | local | A | |3493611 | NULL | NULL | | | ip_summary_d | 0 | PRIMARY | 3 | tosCO | A | |4658149 | NULL | NULL | | | ip_summary_d | 0 | PRIMARY | 4 | tosTYPE | A | |13974447 | NULL | NULL | | | ip_summary_d | 0 | PRIMARY | 5 | tosSLINK | A | |13974447 | NULL | NULL | | | ip_summary_d | 0 | PRIMARY | 6 | tosDLINK | A | |13974447 | NULL | NULL | | | ip_summary_d | 1 | local | 1 | local | A | | 48691 | NULL | NULL | | | ip_summary_d | 1 | local | 2 | date | A | |3493611 | NULL | NULL | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+---------+ -- Best regards, wk 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
