Explain (copy as text and copy as Excel attached) seems to indicate that it is fairly good although the first step does get quite a few rows.
Does anyone have any ideas? TIA, Carl----- Original Message ----- From: "Baron Schwartz" <ba...@xaprb.com>
To: "Brent Baisley" <brentt...@gmail.com> Cc: "Carl" <c...@etrak-plus.com>; <mysql@lists.mysql.com> Sent: Tuesday, March 03, 2009 5:50 PM Subject: Re: Select query locks tables in Innodb
On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley <brentt...@gmail.com> wrote:A SELECT will/can lock a table. It almost always does in MyISAM (no insert/updates), almost never does in InnoDB. There is an exception to every rule. The problem is most likely in the 107488 rows part of the query. That's too many rows for InnoDB to keep a version history on so it's likely just locking the table.InnoDB does not do lock escalation a la SQL Server etc. I'd look at Perrin's suggestions, I think they are likely to be the problem. More importantly, what is the query? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=c...@etrak-plus.com
select *
from payment_to_fee_link_budget_account_detail_link,
journal_entry_master, journal_entry_type,
payment_to_fee_link_event, payment_to_fee_link, fees,
fees_event, fees_budget_account_detail_link, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial,
transaction_event, receipt_master, budget_account_detail,
budget_account_detail as ptfl_budget_account_detail, budget_account_master
where journal_entry_master.organization_serial = 16
and journal_entry_master.date_effective >= '2008-01-01'
and journal_entry_master.date_effective < '2009-03-31'
and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
and
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial =
journal_entry_master.journal_entry_master_serial
and payment_to_fee_link_budget_account_detail_link.date_effective
>= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective
< '2009-03-31'
and payment_to_fee_link_event.payment_to_fee_link_event_serial
=
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and transaction_event.transaction_event_serial = payment_to_fee_link_event.transaction_event_serial
and fees.fees_serial = payment_to_fee_link.fees_serial
and transactions.transactions_serial = fees.transactions_serial
and person.person_serial = transactions.person_serial
and receipt_master.receipt_serial =
transaction_event.receipt_serial
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial =
fees_event.fees_event_serial
and budget_account_detail.budget_account_detail_serial =
fees_budget_account_detail_link.budget_account_detail_serial
and ptfl_budget_account_detail.budget_account_detail_serial =
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
and budget_account_master.budget_account_serial =
budget_account_detail.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001
temp.XLS
Description: MS-Excel spreadsheet
1 SIMPLE journal_entry_master ref
PRIMARY,organization,journal_entry_type_serial,date_effective organization
4 const 56926 Using where
1 SIMPLE journal_entry_type eq_ref PRIMARY PRIMARY 4
PRODUCTION.journal_entry_master.journal_entry_type_serial 1
1 SIMPLE payment_to_fee_link_budget_account_detail_link ref
journal_entry,budget_account_detail_serial,event,date_effective journal_entry
4 PRODUCTION.journal_entry_master.journal_entry_master_serial 1
Using where
1 SIMPLE ptfl_budget_account_detail eq_ref PRIMARY PRIMARY 4
PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
1
1 SIMPLE payment_to_fee_link_event eq_ref
PRIMARY,payment,transaction_event PRIMARY 4
PRODUCTION.payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
1
1 SIMPLE payment_to_fee_link eq_ref PRIMARY,fees PRIMARY 4
PRODUCTION.payment_to_fee_link_event.payment_to_fee_link_serial 1
1 SIMPLE transaction_event eq_ref PRIMARY,receipt PRIMARY 4
PRODUCTION.payment_to_fee_link_event.transaction_event_serial 1
1 SIMPLE receipt_master eq_ref PRIMARY PRIMARY 4
PRODUCTION.transaction_event.receipt_serial 1
1 SIMPLE fees eq_ref PRIMARY,transactions PRIMARY 4
PRODUCTION.payment_to_fee_link.fees_serial 1
1 SIMPLE transactions eq_ref PRIMARY,person PRIMARY 4
PRODUCTION.fees.transactions_serial 1
1 SIMPLE person eq_ref PRIMARY PRIMARY 4
PRODUCTION.transactions.person_serial 1
1 SIMPLE regs ref transaction transaction 4
PRODUCTION.transactions.transactions_serial 1
1 SIMPLE fees_event ref PRIMARY,fees,event fees 4
PRODUCTION.payment_to_fee_link.fees_serial 1 Using where
1 SIMPLE fees_budget_account_detail_link ref
budget_account_detail_serial,fees_event fees_event 4
PRODUCTION.fees_event.fees_event_serial 1
1 SIMPLE budget_account_detail eq_ref PRIMARY,budget_account PRIMARY
4 PRODUCTION.fees_budget_account_detail_link.budget_account_detail_serial
1
1 SIMPLE budget_account_master eq_ref PRIMARY PRIMARY 4
PRODUCTION.budget_account_detail.budget_account_serial 1 Using where
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
