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
>
>