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" <johnha@ccbill.com>
Date: Thu, January 18, 2007 10:24
To: "William R. Mussatto" <mussatto@csz.com>
--------------------------------------------------------------------------

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@csz.com]
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/mysql?unsub=johnha@ccbill.com