MySQL EXPLAIN output explanation

Question

I have a slow MySQl query that takes about 15 seconds to run. So I did some investigation and discovered I can use the EXPLAIN statement to see where the bottleneck is. So I did that, but really can't decipher these results.

If I had to take a stab, I would say the first line is a problem as there are null values for the keys. However, if that is so, I can't understand why as the classType1 table IS indexed on the appropriate columns.

Could someone please offer some explanation as to where the problems might lay? Thanks so much.

EDIT: Ok, I have added the query as well hoping that it might offer some more light to the issues. Unfortunately I just won't be able to explain to you what it's doing, so if any help could be offered based on what's provided, that would be great.

   id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
    1, 'PRIMARY', 'classType1', 'system', 'PRIMARY', '', '', '', 1, 'Using temporary; Using filesort'
    1, 'PRIMARY', 'user', 'const', 'PRIMARY', 'PRIMARY', '4', 'const', 1, 'Using index'
    1, 'PRIMARY', 'class1', 'ref', 'IX_classificationType,IX_classificationValue,IX_classificationObjectType,IX_classificationObjectId', 'IX_classificationObjectId', '8', 'const', 3, 'Using where'
    1, 'PRIMARY', 'classVal1', 'eq_ref', 'PRIMARY', 'PRIMARY', '4', 'ccms.class1.classificationValue', 1, 'Using where; Using index'
    1, 'PRIMARY', 'class2', 'ref', 'IX_classificationType,IX_classificationValue,IX_classificationObjectType,IX_classificationObjectId', 'IX_classificationValue', '4', 'ccms.class1.classificationValue', 368, 'Using where'
    1, 'PRIMARY', 'album', 'eq_ref', 'PRIMARY,IX_albumType,IX_albumIsDisabled,IX_albumIsActive,IX_albumCSI,IX_albumOwner,IX_albumPublishDate', 'PRIMARY', '4', 'ccms.class2.classificationObjectId', 1, 'Using where'
    1, 'PRIMARY', 'profile', 'eq_ref', 'PRIMARY,IX_profileUserId', 'PRIMARY', '4', 'ccms.album.albumOwnerId', 1, 'Using where'
    1, 'PRIMARY', 'albumUser', 'eq_ref', 'PRIMARY,IX_userIsAccountPublic', 'PRIMARY', '4', 'ccms.profile.profileUserId', 1, 'Using where'
    1, 'PRIMARY', 'photo', 'eq_ref', 'PRIMARY,FK_photoAlbumId', 'PRIMARY', '8', 'ccms.album.albumCoverPhotoId', 1, 'Using where'
    2, 'DEPENDENT SUBQUERY', 'class3', 'ref', 'IX_classificationObjectType,IX_classificationObjectId', 'IX_classificationObjectId', '8', 'ccms.class2.classificationObjectId', 1, 'Using where'
    3, 'DEPENDENT SUBQUERY', 'class4', 'ref', 'IX_classificationType,IX_classificationValue,IX_classificationObjectType,IX_classificationObjectId', 'IX_classificationObjectId', '8', 'const', 3, 'Using where'

Query is...

SELECT   profileDisplayName,albumPublishDate,profileId,albumId,albumPath,albumName,albumCoverPhotoId,photoFilename,fnAlbumGetNudityClassification(albumId) AS albumNudityClassification,fnAlbumGetNumberOfPhotos(albumId,1,0) AS albumNumberOfPhotos,albumDescription,albumCSD,albumUSD,photoId,fnGetAlbumPhotoViewCount(albumId) AS albumNumberOfPhotoViews
 FROM    user
 -- Join User Classifications
 INNER JOIN classification class1
 ON class1.classificationObjectId = user.userId AND class1.classificationObjectType = 1
 INNER JOIN classificationType classType1
 ON class1.classificationType = classType1.classificationTypeId
 INNER JOIN classificationTypeValue classVal1
 ON class1.classificationValue = classVal1.classificationTypeValueId
 -- Join Album Classifications
 INNER JOIN classification class2
 ON class2.classificationObjectType = 3
 AND class1.classificationType = class2.classificationType AND class1.classificationValue = class2.classificationValue
 INNER JOIN album
 ON album.albumId = class2.classificationObjectId
 AND albumIsActive = 1
  AND albumIsDisabled = 0
 LEFT JOIN profile
 ON albumOwnerId = profileId AND albumOwnerType = 0
 LEFT JOIN user albumUser
 ON albumUser.userId = profileUserId
 AND albumUser.userIsAccountPublic = 1
 LEFT JOIN photo
  ON album.albumId = photo.photoAlbumId AND photo.photoId = album.albumCoverPhotoId
 WHERE   0 =
 (
 SELECT  COUNT(*)
 FROM    classification class3
 WHERE   class3.classificationObjectType = 3
 AND class3.classificationObjectId = class2.classificationObjectId
 AND NOT EXISTS
 (
 SELECT  1
 FROM    classification class4
 WHERE   class4.classificationObjectType = 1
 AND class4.classificationObjectId = user.userId
 AND class4.classificationType = class3.classificationType AND class4.classificationValue = class3.classificationValue
 )
 )
 AND class1.classificationObjectId = 8
 AND (albumPublishDate <= {ts '2011-01-28 20:48:39'} || albumCSI = 

 8)
   AND album.albumType NOT IN (1)


   AND fnAlbumGetNumberOfPhotos(albumId,1,0) > 0

 AND albumUser.userIsAccountPublic IS NOT NULL
 ORDER BY    albumPublishDate DESC
 LIMIT 0, 15

Thanks for positng the EXPLAIN output - that's useful. Can you please also post your query and the relevant table definitions, including indexes? SHOW CREATE TABLE ... Also it might be useful to simplify your query to the simplest example that demonstrates the problem by removing unnecessary joins that aren't relevant to your issue. — Mark Byers
– Mark Byers, Commented Jan 28, 2011 at 19:27
Hi Mark. If it was that easy to post the query I would have, but it's a large / complicated query that can not be simplified and joins on many tables. Is it not possible to offer suggestions where to look based on that output? — Paolo Broccardo
– Paolo Broccardo, Commented Jan 28, 2011 at 19:32

Zathrus Writer · Accepted Answer · 2011-01-28 20:23:28Z

1

without seeing the actual structure or query, I would look for 2 things...

I know you said they are... but... make sure all the appropriate fields are indexed

example: you have an index on field "active" (to filter out only active records) and another one (let's say primary key index) on id_classType1... unless you do a unique index on "id_classType1, active", a query similar to this:

SELECT * FROM classType1 WHERE id_classType1 IN (1,2,3) AND active = 1

... would need to either combine those indexes or look them up separately. However, if you have an index on both, id_classType1 AND active (and that index is a type UNIQUE), SQL will use it and find the combinations much quicker.

secondly, you seem to have dependent subqueries in your EXPLAIN statement, which can alone slow your query down quite a lot... have a look here for a possible workaround: http://forums.mysql.com/read.php?115,128477,128477

my first try would be to replace those subqueries by JOINs and then perhaps try to optimize it further by removing them altogether (if possible) or making a separate queries for those subqueries

EDIT

this query is more complex that any other I've ever seen, so take these as somehow limited tips:

try removing subqueries (just put anything you know will work and give results there temporarily)
I see a lot of INNER JOINS in the query, which can be quite slow, as they need to join all rows from both tables (source: http://dev.mysql.com/doc/refman/5.0/en/join.html) - maybe there's a way to replace them somehow?
also - and this is something I remember from the past (might not be true or relevant) - shouldn't WHERE-like statements be in the WHERE clause, not the JOIN clause? for example, I would put the following from the 1st JOIN into a WHERE section: class1.classificationObjectType = 1

that's just about all - and one question: how many rows do those table have? no need for exact numbers, just trying to see on approx. how many records the query runs, as it takes so long

edited Jan 28, 2011 at 20:23

answered Jan 28, 2011 at 19:45

Zathrus Writer

4,3315 gold badges30 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Paolo Broccardo Over a year ago

Hi Smejko - thanks for the feedback, I have updated the post with the query in the hope that might shine some light onto the problem area.

Paolo Broccardo Over a year ago

Hi Smejko. Thanks for the feedback. You were right about checking all fields being index. All fields in the JOINS and WHERE clause were. But there was one column that wasn't indexed on a table used in a function call in my column list. Adding the index there solved it. See my answer, I would still like to know how I could have spotted this other than process of elimination. Thanks again.

Zathrus Writer Over a year ago

there is a way of checking slow queries and queries that do not use indexes in MySQL using slow_query_log... see here: dev.mysql.com/doc/refman/5.0/en/…

Paolo Broccardo Over a year ago

Hi Smejko - yeah, I am aware of this. I could be wrong, but wouldn't it only have shown the original query, and not the sub queries performed during the function call?? OR if my query has 4 sub queries and 3 function calls with queries in, would it detail those additional 7 queries too??

Zathrus Writer Over a year ago

I can't answer that question, since I've never run into such logging needs... however it shouldn't be too hard to give it a try on your local machine, provided you remove the indexes you just added for a test

Paolo Broccardo · Accepted Answer · 2011-01-29 07:10:26Z

1

Ok ,so through process of elimination I managed to find the issue.

In my column list, I had a call: fnGetAlbumPhotoViewCount(albumId) AS albumNumberOfPhotoViews

One of the tables joined in that call had a column that was not indexed. Simple enough.

Now my question is, EXPLAIN could not show me that. If you look, there is in fact no reference to the pageview table or columns anywhere in the EXPLAIN output.

So what tool could I have used to weed out this issue??

Thanks

answered Jan 29, 2011 at 7:10

Paolo Broccardo

1,7546 gold badges34 silver badges53 bronze badges

1 Comment

Clement P Over a year ago

Doe EXPLAIN EXTENDED followed by a SHOW WARNINGS show the problematic procedure? If not, I suppose mysql doesn't show the execution plan of stored procedures.

Collectives™ on Stack Overflow

MySQL EXPLAIN output explanation

2 Answers 2

EDIT

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

EDIT

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related