What you sent is important information to diagnosing your problem but it would have really helped if you had posted your query along with an EXPLAIN of it.
Also, it is a WELL KNOWN fact that InnoDB does NOT know exactly how many rows are in a table at any particular moment (because of the versioning system it uses to permit row-level locks) so any query like SELECT count(*) FROM Any_InnoDB_Table; could need to perform a full table scan to calculate that count. I am sure that is at least part of your slowdown. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Dale Fay <[EMAIL PROTECTED]> wrote on 11/19/2004 02:19:26 PM: > I was using myisam tables and converted them to innodb > with Alter table <table> TYPE=INNODB; A query that used > to take 23 minutes, does not complete in hours. There > about 33M rows in the table and I was doing a count of > the rows. Some queries with more conditions seem fine. > > Here is the table: > dspam_token_data | CREATE TABLE `dspam_token_data` ( > `uid` smallint(5) unsigned default NULL, > `token` char(20) default NULL, > `spam_hits` int(11) default NULL, > `innocent_hits` int(11) default NULL, > `last_hit` date default NULL, > UNIQUE KEY `id_token_data_01` (`uid`,`token`), > KEY `id_token_data_02` (`innocent_hits`) > ) TYPE=InnoDB | > > Below is the my.cnf file, with comments removed. > > Thanks, > Dale > > > [client] > #password = your_password > port = 3306 > socket = /tmp/mysql.sock > > # Here follows entries for some specific programs > > # The MySQL server > [mysqld] > port = 3306 > socket = /tmp/mysql.sock > skip-locking > key_buffer = 16M > max_allowed_packet = 16M > table_cache = 1024 > sort_buffer_size = 512K > read_buffer_size = 2M > myisam_sort_buffer_size = 5M > thread_cache = 8 > query_cache_size= 32M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 2 > wait_timeout=200 > > max_connections = 1000 > > # Replication Master Server (default) > # binary logging is required for replication > log-bin > > # required unique id between 1 and 2^32 - 1 > # defaults to 1 if master-host is not set > # but will not function as a master if omitted > server-id = 1 > > > > innodb_data_home_dir = /private2/local/var/dspamdb/ > innodb_data_file_path = ibdata1:3G:autoextend > > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > > > innodb_buffer_pool_size = 800M > innodb_additional_mem_pool_size = 20M > innodb_log_file_size = 100M > innodb_log_buffer_size = 20M > innodb_flush_method = nosync > innodb_flush_log_at_trx_commit = 2 > innodb_lock_wait_timeout = 10 > > > > # You can set .._buffer_pool_size up to 50 - 80 % > # of RAM but beware of setting memory usage too high > > > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > > > [mysqlhotcopy] > interactive-timeout > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
