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
