Just a thought, did you try running Optimize Table from the MySQL Administrator. I'm thinking that when you restarted it re-examined the table statistics and was able to pick a better index. On Wed, January 17, 2007 14:31, John Anderson said: > > > mysql> SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as > recurring_cc_count, > > -> SUM(rb.grace_price) as recurring_cc, > > -> COUNT(sb.subscription_id) as single_cc_count, > > -> SUM(sb.initial_amt) as single_cc > > -> FROM customerdetail a > > -> LEFT JOIN recurringbilling rb > > -> ON a.subscription_id = rb.subscription_id > > -> LEFT JOIN singlebilling sb > > -> ON a.subscription_id = sb.subscription_id > > -> LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN > global.currencyCodes as cur) > > -> ON (a.subscription_id = ser.subscriptionId AND > ser.billedCurrencyCode = cur.currencyCode) > > -> WHERE client_accnum = '12345' > > -> AND a.trans_timestamp > > -> BETWEEN '20070108000000' AND '20070108235959'; > > +--------------------+--------------+-----------------+-----------+ > > | recurring_cc_count | recurring_cc | single_cc_count | single_cc | > > +--------------------+--------------+-----------------+-----------+ > > | 4 | 119.80 | 0 | NULL | > > +--------------------+--------------+-----------------+-----------+ > > 1 row in set (0.40 sec) > > mysql> explain SELECT SQL_NO_CACHE COUNT(rb.subscription_id) as > recurring_cc_count, > > -> SUM(rb.grace_price) as recurring_cc, > > -> COUNT(sb.subscription_id) as single_cc_count, > > -> SUM(sb.initial_amt) as single_cc > > -> FROM customerdetail a > > -> LEFT JOIN recurringbilling rb > > -> ON a.subscription_id = rb.subscription_id > > -> LEFT JOIN singlebilling sb > > -> ON a.subscription_id = sb.subscription_id > > -> LEFT JOIN (global.subscriptionExchangeRate as ser INNER JOIN > global.currencyCodes as cur) > > -> ON (a.subscription_id = ser.subscriptionId AND > ser.billedCurrencyCode = cur.currencyCode) > > -> WHERE client_accnum = '12345' > > -> AND a.trans_timestamp > > -> BETWEEN '20070108000000' AND '20070108235959'; > > +----+-------------+-------+--------+----------------------------------- > -----------------------------+-----------------+---------+-------------- > -----------------+------+--------------------------+ > > | id | select_type | table | type | possible_keys > | key | key_len | ref | rows | > Extra | > > +----+-------------+-------+--------+----------------------------------- > -----------------------------+-----------------+---------+-------------- > -----------------+------+--------------------------+ > > | 1 | SIMPLE | a | range | > client_idx,trans_idx,accno_trans_idx,accnumactive,accsubactive | > accno_trans_idx | 7 | NULL | 4 | Using > where; Using index | > > | 1 | SIMPLE | rb | eq_ref | PRIMARY > | PRIMARY | 8 | company.a.subscription_id | 1 | > | > > | 1 | SIMPLE | sb | eq_ref | PRIMARY > | PRIMARY | 8 | company.a.subscription_id | 1 | > | > > | 1 | SIMPLE | ser | ref | PRIMARY,billedCurrencyCode > | PRIMARY | 8 | company.a.subscription_id | 1 | > | > > | 1 | SIMPLE | cur | eq_ref | PRIMARY > | PRIMARY | 2 | global.ser.billedCurrencyCode | 1 | > Using index | > > +----+-------------+-------+--------+----------------------------------- > -----------------------------+-----------------+---------+-------------- > -----------------+------+--------------------------+ > > 5 rows in set (0.00 sec) > > > > mysql> show index from customerdetail; > > +----------------+------------+-----------------+--------------+-------- > ---------+-----------+-------------+----------+--------+------+--------- > ---+--------- > > + > > | Table | Non_unique | Key_name | Seq_in_index | > Column_name | Collation | Cardinality | Sub_part | Packed | Null | > Index_type | Comment > > | > > +----------------+------------+-----------------+--------------+-------- > ---------+-----------+-------------+----------+--------+------+--------- > ---+--------- > > ..........................SNIP........................................ > > | customerdetail | 1 | accno_trans_idx | 1 | > client_accnum | A | 17052 | NULL | NULL | | > BTREE | > > | > > | customerdetail | 1 | accno_trans_idx | 2 | > trans_timestamp | A | 49042196 | NULL | NULL | | > BTREE | > > ..........................SNIP........................................ > > +----------------+------------+-----------------+--------------+-------- > ---------+-----------+-------------+----------+--------+------+--------- > ---+---------+ > > > > The query executes orders of magnitude faster, and the EXPLAIN shows > why. MySQL has now chosen to use the accno_trans_idx index for the > customerdetail table which has much better cardinality (almost 1 key per > row). > > > > The obvious workaround is use FORCE INDEX(accno_trans_idx) in the query > for now, but I'm beginning to think this may be a bug. Has anyone else > had any similar issues? I haven't found anything like this in the bug > database though. If anyone doesn't see anything blatantly wrong with my > setup, I'll submit this as a bug. > > > > > > Further information: > > > > Here is how MySQL is configured, and the my.cnf I'm using. > > > > CC="gcc -m64" CXX="g++ -m64" \ > > ./configure --prefix=/usr \ > > --sbindir=/usr/sbin \ > > --libexecdir=/usr/sbin \ > > --infodir=/usr/share/man \ > > --mandir=/usr/share/info \ > > --libdir=/usr/lib64 \ > > --enable-shared \ > > --enable-static \ > > --enable-thread-safe-client \ > > --enable-local-infile \ > > --with-extra-charsets=all \ > > --with-gnu-ld \ > > --with-pthread \ > > --with-unix-socket-path=/tmp/mysql.sock \ > > --with-mysqld-user=mysql \ > > --without-debug \ > > --with-openssl=/usr \ > > --with-big-tables \ > > --with-archive-storage-engine \ > > --with-csv-storage-engine \ > > --with-blackhole-storage-engine \ > > --with-federated-storage-engine \ > > --with-berkeley-db \ > > --with-berkeley-includes=/usr/include \ > > --with-berkeley-libs=/usr/lib64 \ > > --without-extra-tools \ > > --with-mysqlmanager=no \ > > --with-ndbcluster \ > > --without-geometry > > > > > > ---------------------- > > [mysqld] > > #Directories > > datadir=/var/db/mysql > > socket=/tmp/mysql.sock > > log-error=/var/log/mysql/mysql.log > > pid-file=/var/run/mysqld/mysqld.pid > > tmpdir=/tmp > > > > #Replication > > server-id=127 > > #log-bin > replicate-ignore-db=mysql > > #log-slave-updates > > > > > > #Network > > max_connections=1024 > > max_allowed_packet=1024M > > net_buffer_length=16k > > > > #Files > > open_files_limit=8192 # Anything higher needs corresponding ulimit entry > > > > #Buffers > > join_buffer_size=128M > > key_buffer_size=512M > > key_buffer=512M > > large_pages > > max_heap_table_size=1024M > > myisam_sort_buffer_size=256M > > read_buffer_size=64M > > read_buffer=64M > > query_cache_size=32M > > query_cache_type=1 > > record_buffer=512 > > sort_buffer=512M > > table_cache=512 > > thread_cache=4M > > thread_stack=512K > > thread_cache_size=300 > > thread_concurrency=16 > > tmp_table_size=1G > > > #innodb > > innodb-table-locks=off > > transaction_isolation=REPEATABLE-READ > > innodb_buffer_pool_size=1024M > > innodb_log_buffer_size=8M > > innodb_flush_log_at_trx_commit=0 > > innodb_log_file_size=128M > > innodb_additional_mem_pool_size=32M > > innodb_thread_concurrency=16 > > innodb_commit_concurrency=4 > > innodb_flush_method=O_DIRECT > > innodb_open_files=8192 > > innodb_sync_spin_loops=32 > > innodb_thread_sleep_delay=1000 > > innodb_autoextend_increment=1024M > > innodb_file_per_table=TRUE > > > > [client] > > socket=/tmp/mysql.sock > > > > > > John Anderson > > > >
------- William R. Mussatto, Senior Systems Engineer http://www.csz.com Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
