List:General Discussion« Previous MessageNext Message »
From:John Anderson Date:January 18 2007 11:22pm
Subject:FW: [PART 2/2] InnoDB - Different EXPLAINs for same query
View as plain text  
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:mussatto@stripped]
> Sent: Wednesday, January 17, 2007 5:17 PM
> To: mysql@stripped
> 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/mysql?unsub=1
>
>
>


Thread
FW: [PART 2/2] InnoDB - Different EXPLAINs for same queryJohn Anderson19 Jan