Thanks to all of you.
The key was the 107488 rows. I restructured the query so that it started
with something smaller and it 1) runs faster (I'm guessing the reduced use
of temp space) and 2) did not seem to cause any locking problems (I will
test this under load today.)
I have attached a copy of the query which has been simplified in a couple of
ways (I don't really want every field from every row selected from every
table.) Also, the constants like organization_serial (16) and dates are
variables in the real version.
The explain now shows:
id table type possible_keys
key len ref rows
1 organization_shift ref PRIMARY, organization organization
4 const 5
1 organization_shift_start ref PRIMARY, organization_shift
organization_shift 4 organization_shift_serial 295
1 journal_entry_master ref PRIMARY, organization_shift_start
organization_shift_start 5 organization_shift_start_serial 52
Note that it now starts with 5 row, expands to 295 rows, etc. not the
100,000+ from before.
Again, thanks for all your help.
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
# sales from collections
(select *
from organization_shift, organization_shift_start, transaction_event,payment_to_fee_link_event,payment_to_fee_link, payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link, fees_event, budget_account_detail,
payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master, journal_entry_master, journal_entry_type, receipt_master, person, transactions
left join regs on regs.transactions_serial =
transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial =
organization_shift.organization_shift_serial
and organization_shift_start.date_effective >= '2008-01-01'
and organization_shift_start.date_effective < '2009-03-31'
#$P!{organizationShiftStartQuery}
and journal_entry_master.organization_shift_start_serial =
organization_shift_start.organization_shift_start_serial
and receipt_master.receipt_serial =
transaction_event.receipt_serial
and transactions.transactions_serial =
transaction_event.transactions_serial
and transactions.organization_serial = organization_shift.organization_serial
#$P!{itemSerials}
and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial =
transaction_event.transaction_event_serial
and
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
= payment_to_fee_link_event.payment_to_fee_link_event_serial
and
payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
and payments.payments_serial =
payment_to_fee_link.payments_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 (payments.payment_type_code_serial in (
1,2,3,4,5,8,24,6,7,12,13,23,25 ) # 1,2,3,4,5,8,24,6,7,12,13,23,25
or
payment_to_fee_link_budget_account_detail_link.description='Apply available
credit to customer accounts receivable')
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
fees_budget_account_detail_link.budget_account_detail_serial =
budget_account_detail.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_detail.budget_account_serial =
budget_account_master.budget_account_serial
and budget_account_master.budget_account_type_serial = 5001
and journal_entry_master.journal_entry_master_serial =
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial
and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
group by payment_to_fee_link_event.payment_to_fee_link_event_serial
)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
