Hi!
While extending our multi-thread test suite for MySQL I noticed a
that during some conditions simple SELECT's could sometimes take a
long time.
I found the problem to be the following:
If you do a FLUSH TABLE, CHECK table or REPAIR table, tables that are
waiting for a lock will have to reopen the table. In this case the
next query (just the next) will not use any keys for the table that
was forced to be reopened.
If you are using any of the above commands (for example mysqlhotcopy
uses FLUSH TABLES) this could explain why you sometimes get a query
that takes a long time performance (in this case MySQL will do a table
scan).
Here is a patch that fixes this:
(It will be in 3.23.37)
===== sql/sql_base.cc 1.72 vs edited =====
*** /tmp/sql_base.cc-1.72-26630 Tue Mar 27 13:05:48 2001
--- edited/sql/sql_base.cc Sun Apr 1 15:04:18 2001
***************
*** 940,962 ****
goto end;
}
! tmp.key_length=table->key_length;
! tmp.in_use=table->in_use;
! tmp.used_keys=tmp.keys_in_use;
! tmp.reginfo.lock_type=table->reginfo.lock_type;
! tmp.version=refresh_version;
! tmp.next=table->next;
! tmp.prev=table->prev;
!
! /* This list copies varibles set by open_table */
tmp.tablenr= table->tablenr;
- tmp.tmp_table= table->tmp_table;
tmp.used_fields= table->used_fields;
tmp.const_table= table->const_table;
tmp.outer_join= table->outer_join;
tmp.null_row= table->null_row;
tmp.status= table->status;
tmp.grant= table->grant;
if (table->file)
VOID(closefrm(table)); // close file, free everything
--- 941,967 ----
goto end;
}
! /* This list copies variables set by open_table */
tmp.tablenr= table->tablenr;
tmp.used_fields= table->used_fields;
tmp.const_table= table->const_table;
tmp.outer_join= table->outer_join;
tmp.null_row= table->null_row;
+ tmp.maybe_null= table->maybe_null;
tmp.status= table->status;
+ tmp.keys_in_use_for_query=tmp.used_keys=tmp.keys_in_use;
+
+ /* Get state */
+ tmp.key_length= table->key_length;
+ tmp.in_use= table->in_use;
+ tmp.reginfo.lock_type=table->reginfo.lock_type;
+ tmp.version= refresh_version;
+ tmp.tmp_table= table->tmp_table;
tmp.grant= table->grant;
+
+ /* Replace table in open list */
+ tmp.next=table->next;
+ tmp.prev=table->prev;
if (table->file)
VOID(closefrm(table)); // close file, free everything
(Basicly the above just sets one variable that we had forgot to set,
but the diff is a bit bigger than actually needed as I also added some
comments and rearranged the variable setting to be a little more logical)
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
