MySQL EXPLAIN output explanation

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,fnGetAlbumPhotoVie开发者_如何学JAVAwCount(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

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

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