SEO

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

While I think optimize does an analyze, you may find that just an ANALYZE
will do it instead of a full OPTIMIZE:

http://dev.mysql.com/doc/refman/5.0/...yze-table.html


On 18/01/07, William R. Mussatto <mussatto@csz.com> wrote:
>
> 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=voiperster@gmail.com
>
>