The nice thing about InnnoDB is that it won't have to access the data portion of the file if it doesn't have to. So if all the information you are retrieving is contained in an index, it only accesses the index to get the information it needs. The data portion is never access, and thus never locked.
Something like this is probably going on. All the information it needs for the 100,000 records is contained in the index, the the data portion is never accessed until it needs to retrieve the 60,000 records. That's a simplistic overview of what could be going on. But it sounds like your issue has been resolved. Interesting, your temp1 attached file shows mysql switched from using the org_date index to the organization index. Brent Baisley 2009/3/12 Carl <c...@etrak-plus.com>: > Brent, > > After a delay while I was busy killing alligators, I did as you suggested > (added a composite index of date and organization_serial on > journal_entry_master... in the spirit of your suggestion, anyway.) The > results were interesting: > > 1. In my test environment, I could not force a locked file even though I > opened the dates up to cover 2+ years and changed to an organization that > had more records. The 'Explain' is attached as temp1.txt. You will note > that it starts with 100,000+ records while the eventual set of records for > the report is 60,000 because the 100,000+ number includes some journmal > entries for refund/void/etc. transactions which we have no interest in. > > 2. I tried various combinations of indexes but couldn't seem to get any > better than the composite one on the journal_entry_master. I did not check > whether the other options would produce locked files. > > I am now going to put this into production and see if it will actually fly. > > I am still a little puzzled about how we could have a relatively large set > of records (100,000+) and yet not cause any table to be locked as the server > has only 8GB of memory. > > Thanks for all your help and Baron's suggestions also. > > Carl > > > > > > ----- Original Message ----- From: "Brent Baisley" <brentt...@gmail.com> > To: "Carl" <c...@etrak-plus.com> > Sent: Thursday, March 05, 2009 1:12 PM > Subject: Re: Select query locks tables in Innodb > > > Ok, so you have 687 unique organization serial numbers. That's not > very unique, on average it will only narrow down the table to 1/687 of > it's full size. This is probably the source of your locking problem > and where you want to focus. > InnoDB will lock on a query that doesn't use an index. It would have > to lock every record anyway, so why not lock the table? > 36,000 records still may be too large of a result set to do record > versioning. But, optimizing your query is the only way to go. > > Your date_effective is a lot more granular, so you may want to focus > on that. If you do a lot of these types of searches, you can try > creating a compound index on organization_serial+date_effective. > CREATE INDEX (org_date) ON > journal_entry_master(organization_serial,date_effective) > > MySQL would/should then use that query, which will narrow things down > quicker and better. It shouldn't have to try to do versioning on > 56,000 records while it tries to get the subset of that (36,000). > > Brent > > On Thu, Mar 5, 2009 at 6:02 AM, Carl <c...@etrak-plus.com> wrote: >> >> Brent, >> >> The query returns about 36,000 rows. The 56,000 rows from the >> journal_entry_master table is all the entries for organization 16 (they >> span >> more than the dates I have asked for.) >> >> SHOW INDEX FROM journal_entry_master shows 1,554,000+ for the primary >> index >> (auto-increment), 687 for the organization_serial (the one I am using), 18 >> for the organization_shift_start (I tried this before, i.e., starting with >> the organization_shift, but it quickly got mired down) and 777,000+ for >> the >> date_effective. >> >> If I understand correctly, you have suggested using the date index. The >> difficulty is the data contains many organizations and so the date range >> query returns 163,000+ rows. >> >> Also, I would expect scaling a query where I had to programatically cut it >> up would 1) be difficult and 2) wouldn't really solve the problem but >> would >> rather just shorten the time of the locks. I am not suggesting that I >> might >> not end up there, only hoping for a better solution. >> >> Thanks for all your insight and feel free to suggest away. >> >> Carl >> >> ----- Original Message ----- From: "Brent Baisley" <brentt...@gmail.com> >> To: "Carl" <c...@etrak-plus.com> >> Sent: Wednesday, March 04, 2009 4:23 PM >> Subject: Re: Select query locks tables in Innodb >> >> >> Is the result of the query returning 56,000+ rows? How many rows are >> you expecting to be returned once the query is finished running? >> Your date range is over a year. You may actually get much better >> performance (and avoid locking) by running more queries with a >> narrower date range and linking them through a UNION. It's using the >> organization index rather than the date index. >> I don't know your dataset, but typically you want your query to use >> the date index since that narrows down the data set better. >> >> You can run SHOW INDEX FROM journal_entry_master to see the >> distribution of your data in the index. The cardinality column will >> indicate the uniqueness of your data. The higher the number, the more >> unique values. >> >> Brent >> >> 2009/3/4 Carl <c...@etrak-plus.com>: >>> >>> Under stress (having transaction entered), the query shows that it is >>> still >>> locking the tables. I rewrote the query and tested it step by step but >>> could not tell whether tyhe partially complete query was locking tables >>> because it ran so fast. However, when I had all the pieces in the query >>> (copy attached), I could easily see it was locking tables using the >>> Server >>> Monitor in Navicat. >>> >>> 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 >>>> >>>> >>> >>> >>> -- >>> MySQL General Mailing List >>> For list archives: http://lists.mysql.com/mysql >>> To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com >>> >> >> > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=brentt...@gmail.com > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
