Hi Matt, Thanks for the excellent play-by-play for my query. I can't believe I didn't see the problem - you're right exactly, its using the 'created' index instead of 'mid', and so loading every row into memory, which when the site is under high load causes wicked slowness.
This appears to be a semi-bug, since its only when the erroneous 'use index(type)' (a bug in my query-creation routine) appears, it uses the 'created' index. Removing the 'use index(type)' part causes it to correctly choose the 'mid' index instead. Hopefully the other queries I'm seeing the slow 'sending data' phase with are plagued by similar problems. I got too used to only looking to see whether the query in explain was using filesort/whatever, rather than contemplating what its doing to the rows its returning. I guess the complication involved with limiting and joining later confused me to the problem. Ciao, Kevin Watt Community Manager, Allpoetry.com What happened to the cow who went for a drive? He got a Moo_ving violation What do you call someone who is crazy about hot chocolate? A cocoa nut What do bees use to cut wood? Buzz saws Who eats at underwater resturants ? Suba diners How do really small people call each other ? On Microphones How do you fix a broken chimp? With a monkey wrench > -----Original Message----- > From: Matt W [mailto:[EMAIL PROTECTED] > Sent: Monday, September 29, 2003 7:00 PM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: Re: slow 'sending data' phase > > Hi Kevin, > > > ----- Original Message ----- > From: "Kevin" > Sent: Monday, September 29, 2003 2:21 PM > Subject: slow 'sending data' phase > > > > I've fixed my swapping issues, but the system continues to get stuck > in > > a 'sending data' phase from time to time. > > > > With mod_perl + mysql, this phase SHOULD be when mysql collects the > rows > > (after sorting, etc) and sends them to the perl handler for > processing. > > Yes, it may be sending rows, but it also needs to read/process them > before sending, which takes time (if filesort is used (not for your > query), rows will be read before and after the sort, I think). > > > > Any ideas why this phase would ever be taking 100-500 seconds? > > Yes, when a lot of data needs to be read. :-) I've had the same problem > on a smaller scale. :-( How many MB is your poems table? Let's check the > EXPLAIN and see if something can be changed... > > > > [snip] > > explained: > > > +-------+--------+---------------+---------+---------+-----------+------ > > --+-| table | type | possible_keys | key | key_len | ref | > > rows | Extra | > > > +-------+--------+---------------+---------+---------+-----------+------ > > --+-| poems | index | NULL | created | 4 | NULL | > > 272319 | Using where | > > | poets | eq_ref | PRIMARY | PRIMARY | 3 | poems.mid | > > 1 | | > > > +-------+--------+---------------+---------+---------+-----------+------ > > --+- > > It's easier to read that output here if you use \G at the end of the > query instead of ;. :-) > > OK, all 272,000 poems rows are being scanned (assuming mid isn't part of > the "created" index) and the index is being used for ORDER BY. Actually, > since there's no filesort, it will abort when and if the LIMIT is > satisfied. If mid isn't in the "created" index, MySQL needs to jump to > the data file for each row to check if mid matches the WHERE. If your > data file is too big to be cached in RAM by the OS and LIMIT rows aren't > found early, the disk seeks will REALLY slow it down. > > Do all the problem queries have WHERE poems.mid=<number> in them? Why > don't you try adding an index to poems.mid? Even if the WHERE matches a > couple thousand rows and filesort is used, it should be a lot faster > than reading the whole data file. If you're searching for a single mid > value (ref type in EXPLAIN) and you're using MySQL 4+, you can eliminate > filesort by creating a composite index on (mid, created) together. > > By the way, remove "use index (type)" from the query as there's nothing > in your example that would allow an index on type to be used anyway. > > > > Thanks for any help anyone can give me - this is driving me nuts! > > Yeah, see if indexing mid helps. > > Funny little riddles in your sig BTW. :-D > > > Matt > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
