Ben Escoto writes:
> Hi, I think I found a bug in mysql. I tried the mysqlbug thing but it
> didn't work right away. I hope this message will be detailed enough.
> I am not subscribed to the mailing list.
>
> In the following sections are the output of, respectively:
>
> mysql -e "SHOW VARIABLES"
> mysqldump sqmail sequence_data
> mysql -e "EXPLAIN SELECT * FROM sqmail.sequence_data WHERE sid = 16
> ORDER BY sid desc,id desc LIMIT 10"
>
> The output of the last command contains "Using filesort" when it seems
> like it shouldn't.
>
> Variable_name Value
> ansi_mode OFF
> back_log 50
> basedir /usr/
> bdb_cache_size 8388600
> bdb_log_buffer_size 32768
> bdb_home /var/lib/mysql/
> bdb_max_lock 10000
> bdb_logdir
> bdb_shared_data OFF
> bdb_tmpdir /tmp/
> bdb_version Sleepycat Software: Berkeley DB 3.2.9a: (May 9, 2001)
> binlog_cache_size 32768
> character_set latin1
> character_sets latin1 big5 cp1251 cp1257 croat czech danish dec8 dos estonia
>euc_kr gb2312 gbk german1 greek hebrew hp8 hungarian koi8_ru koi8_ukr latin2 latin5
>swe7 usa7 win1250 win1251 win1251ukr ujis sjis tis620
> concurrent_insert ON
> connect_timeout 5
> datadir /var/lib/mysql/
> delay_key_write ON
> delayed_insert_limit 100
> delayed_insert_timeout 300
> delayed_queue_size 1000
> flush OFF
> flush_time 0
> have_bdb YES
> have_gemini NO
> have_innodb NO
> have_isam YES
> have_raid NO
> have_ssl NO
> init_file
> interactive_timeout 28800
> join_buffer_size 131072
> key_buffer_size 8388600
> language /usr/share/mysql/english/
> large_files_support ON
> locked_in_memory OFF
> log OFF
> log_update OFF
> log_bin OFF
> log_slave_updates OFF
> long_query_time 10
> low_priority_updates OFF
> lower_case_table_names 0
> max_allowed_packet 1048576
> max_binlog_cache_size 4294967295
> max_binlog_size 1073741824
> max_connections 100
> max_connect_errors 10
> max_delayed_threads 20
> max_heap_table_size 16777216
> max_join_size 4294967295
> max_sort_length 1024
> max_user_connections 0
> max_tmp_tables 32
> max_write_lock_count 4294967295
> myisam_recover_options OFF
> myisam_max_extra_sort_file_size 256
> myisam_max_sort_file_size 2047
> myisam_sort_buffer_size 8388608
> net_buffer_length 16384
> net_read_timeout 30
> net_retry_count 10
> net_write_timeout 60
> open_files_limit 0
> pid_file /var/run/mysqld/mysqld.pid
> port 3306
> protocol_version 10
> record_buffer 131072
> query_buffer_size 0
> safe_show_database OFF
> server_id 0
> skip_locking ON
> skip_networking OFF
> skip_show_database OFF
> slow_launch_time 2
> socket /var/lib/mysql/mysql.sock
> sort_buffer 2097144
> table_cache 64
> table_type MYISAM
> thread_cache_size 0
> thread_stack 65536
> transaction_isolation READ-COMMITTED
> timezone PDT
> tmp_table_size 1048576
> tmpdir /tmp/
> version 3.23.38
> wait_timeout 28800
> # MySQL dump 8.14
> #
> # Host: localhost Database: sqmail
> #--------------------------------------------------------
> # Server version 3.23.38
>
> #
> # Table structure for table 'sequence_data'
> #
>
> CREATE TABLE sequence_data (
> sid int(10) unsigned NOT NULL default '0',
> id int(10) unsigned NOT NULL default '0',
> PRIMARY KEY (sid,id)
> ) TYPE=MyISAM;
>
> #
> # Dumping data for table 'sequence_data'
> #
>
> INSERT INTO sequence_data VALUES (1,1);
> INSERT INTO sequence_data VALUES (1,2);
>
> table type possible_keys key key_len ref rows Extra
> sequence_data ref sidid sidid 4 const 1 where used; Using
>index; Using filesort
Hi!
MySQL is still using filesort, because of "ORDER BY sid desc,id desc".
Simply, setting DESC disables usage of index ...
MySQL 4.0 will fix this, however ....
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaca, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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
