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]
