Please post to the list not to me personnally. ---------------------------- Original Message ---------------------------- Subject: RE: [PART 2/2] InnoDB - Different EXPLAINs for same query From: "John Anderson" <[EMAIL PROTECTED]> Date: Thu, January 18, 2007 10:24 To: "William R. Mussatto" <[EMAIL PROTECTED]> --------------------------------------------------------------------------
I optimized every table after I first imported the data. The tables were probably in use, off and on for testing, for about a week after the optimize table was ran on every table before I noticed this problem. I'm not saying the problem didn't exist within that week, I'm just saying I didn't notice it ;) . Another thing. Does the query optimizer keep any sort of statistics and use them to make decisions for future queries on the same table? If so, then that could be the problem because we have certain fields, containing only numbers, but were previously setup as varchars for some unknown reason. I changed them all to int types but some queries in obscure parts of our applications are still querying this field as if it were a character field, using LIKE, etc. I'm slowly but sure tracking those down and fixing them, I'm just curious if that could have anything to do with this strange behavior. Thanks, John A. -----Original Message----- From: William R. Mussatto [mailto:[EMAIL PROTECTED] Sent: Wednesday, January 17, 2007 5:17 PM To: mysql@lists.mysql.com Subject: Re: [PART 2/2] InnoDB - Different EXPLAINs for same query 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
