Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a problem with a very important query in my application.

It is very slow when adding group and order to it.

So the full query is:

SELECT 
    m.id, 
    m.title, 
    m.title_text, 
    m.content_text, 
    m.url, 
    m.root_url, 
    m.sub_type, 
    m.indexed, 
    f.foreign_id, 
    v.foreign_id, 
    v.created, 
    mfs.score, 
    Image.id, 
    Image.model, 
    Image.foreign_key, 
    Image.dirname, 
    Image.basename, 
    (REPLACE(REPLACE(m.host_url, 'http://www.', ''), 'http://', '')) AS Mention__plain_url 
FROM mentions AS m 
    JOIN mentioncache AS mc 
        ON (mc.mention_id = m.id AND mc.profile_id = 803) 
    LEFT JOIN attachments AS Image 
        ON (Image.foreign_key = m.id AND Image.model = 'Mention') 
    LEFT JOIN urlinfluranks AS u 
        ON (u.url = m.host_url) 
    LEFT JOIN favoureditems AS f 
        ON (f.model = "Mention" AND f.foreign_id = m.id AND f.owner_id = 803) 
    LEFT JOIN visiteditems AS v 
        ON (v.model = "Mention" AND v.foreign_id = m.id AND v.owner_id = 803) 
    LEFT JOIN mentionfeedscores AS mfs 
        ON (mfs.mention_id = m.id AND mfs.feed_id = '474737584865424564398208323289092') 
WHERE DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" 
GROUP BY m.id
ORDER BY m.indexed DESC 
LIMIT 10

With the group and the order the query takes about 2 - 3 seconds, without them just 0.002 seconds.

The explain of this query with group/order is:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Mentioncache    index   mention_id_profile_id,mention_id    mention_id_profile_id   8   NULL    32946   Using where; Using index; Using temporary; Using filesort
1   SIMPLE  Mention eq_ref  PRIMARY PRIMARY 4   clippingcroc_dev.Mentioncache.mention_id    1   Using where
1   SIMPLE  Image   ref foreign_key,model_foreign_key   model_foreign_key   66  const,clippingcroc_dev.Mention.id   1    
1   SIMPLE  Favoureditem    ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc_dev.Mentioncache.mention_id,const    2   Using index
1   SIMPLE  Visiteditem ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc_dev.Mention.id,const 2    
1   SIMPLE  Mentionfeedscore    ref mention_id,feed_id  mention_id  4   clippingcroc_dev.Mentioncache.mention_id    2    
1   SIMPLE  Urlinflurank    eq_ref  url url 765 clippingcroc_dev.Mention.host_url   1   Using index

Without group/order the explain is:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  Mentioncache    index   mention_id_profile_id,mention_id    mention_id_profile_id   8   NULL    32946   Using where; Using index
1   SIMPLE  Mention eq_ref  PRIMARY PRIMARY 4   clippingcroc_dev.Mentioncache.mention_id    1   Using where
1   SIMPLE  Image   ref foreign_key,model_foreign_key   model_foreign_key   66  const,clippingcroc_dev.Mention.id   1    
1   SIMPLE  Favoureditem    ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc_dev.Mentioncache.mention_id,const    2   Using index
1   SIMPLE  Visiteditem ref model_foreign_id_owner_id   model_foreign_id_owner_id   163 const,clippingcroc_dev.Mention.id,const 2    
1   SIMPLE  Mentionfeedscore    ref mention_id,feed_id  mention_id  4   clippingcroc_dev.Mentioncache.mention_id    2    
1   SIMPLE  Urlinflurank    eq_ref  url url 765 clippingcroc_dev.Mention.host_url   1   Using index

So it seems the reason for the performance-difference is the “Using temporary; Using filesort” in the mentioncache-table.

I tried to set different indexes to the tables to get MySQL to use the existing indexes for group/order. But I don’t get it.

Please help me to get a better performance of this query when using group/order.

Tell me if you need more information about the structure/indexes of the tables.

Edit

It seems the reason for my slowly query is that MySQL can’t use the indexes for group and order.

So I tried to get MySQL to use the indexes in the mentions table.

Unfortunately the “first” table the MySQL optimizer uses is the mentioncache table which doesn’t have these indexes.

So is there a possibility to change the “order” of the tables the optimizer works with?

As an alternative I moved the JOIN to the mentioncache table to a subquery like this:

SELECT SQL_NO_CACHE 
    m.id, 
    m.title, 
    m.title_text, 
    m.content_text, 
    m.url, 
    m.root_url, 
    m.sub_type, 
    m.indexed
FROM mentions AS m 
WHERE 
    m.id IN (SELECT mention_id FROM mentioncache WHERE profile_id = 803) 
    AND DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" 
GROUP BY m.id 
ORDER BY m.indexed DESC 
LIMIT 10, 10

This query runs in about 0.02 seconds and is much faster than the first one and it uses the right indexes (there is no using temporary/using filesort in the explain).

Unfortunately this query slows down rapidly when another join table is added:

SELECT SQL_NO_CACHE 
    m.id, 
    m.title, 
    m.title_text, 
    m.content_text, 
    m.url, 
    m.root_url, 
    m.sub_type, 
    m.indexed
FROM 
    mentions AS m 
    LEFT JOIN mentionfeedscores AS mfs 
        ON (mfs.mention_id = m.id AND mfs.feed_id = '474737584865424564398208323289092') 
WHERE 
    m.id IN (SELECT mention_id FROM mentioncache WHERE profile_id = 803) 
    AND DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" 
GROUP BY m.id 
ORDER BY m.indexed DESC 
LIMIT 10, 10

In this case the query takes again nearly 2 seconds to return the results.

So I don’t know what to do to get a better performance of my query.

share|improve this question
    
I changed the JOIN of the mentioncache-table to a straight-join, but this makes the query much more slowly (nearly about 5 seconds). But in the straight-join case with the group/order ob the same field (Mention.id) the query is fast - maybe because mysql now can use (same) index for group and order? –  Timo Oct 16 '12 at 10:00
    
What indexes do you have on the mentionfeedscores table? Why is the "AND Mentionfeedscore.feed_id = '474737584865424564398208323289092'" in the JOIN clause and not the WHERE clause? –  Petter Brodin Oct 16 '12 at 10:04
    
The tables has the following indexes: PRIMARY id, INDEX mention_id, INDEX feed_id. The condition is in the on-part because i thought its better to join less data. –  Timo Oct 16 '12 at 10:17
    
I'm not too sure about MySQL, but it seems weird the query optimizer wouldn't know how to use the WHERE clause correctly. I'm not entirely sure about how MySQL indexes work, but I'm guessing mention_id and feed_id should be grouped into one index. I'm not sure about which column should come first, but try both combinations and see if it has any impact on the performance. –  Petter Brodin Oct 16 '12 at 10:31
    
Seems that index-changing in the mentionfeedscores-table does not change the performance of this query. –  Timo Oct 16 '12 at 10:47

1 Answer 1

I can see couple things that should improve your query performance.

1 As you already found out there is absolutely no need to join mentioncache. Using EXISTS seems more natural (or IN as you did, but EXISTS may work better from performance point of view).

2 DATE(m.indexed) BETWEEN "2012-09-16" AND "2012-10-16" can be rewritten to m.indexed between "2012-09-16" AND "2012-10-16 23:59:59", so mysql can use index.

3 urlinfluranks doesn't seem to be used anywhere except in LEFT JOIN, why do you need it?

4 f.foreign_id can be either null or m.id, and this is the only reference to favoureditems table, I'd rather use subquery in this case.

Finally, I think you can get the same results without GROUP BY m.id (as far as I understood , mentions.id a primary key).

SELECT   
m.id, m.title, m.title_text, m.content_text, m.url,m.root_url,m.sub_type,m.indexed,  
CASE 
 WHEN EXISTS 
    (SELECT NULL FROM favoureditems f WHERE f.model = "Mention" 
    AND f.foreign_id = m.id AND f.owner_id = 803) THEN m.id 
END AS f.foreign_id,
, v.foreign_id, v.created, mfs.score,  
Image.id,Image.model,Image.foreign_key, Image.dirname,Image.basename,  
(REPLACE(REPLACE(m.host_url, 'http://www.', ''), 'http://', '')) AS Mention__plain_url  
FROM mentions AS m  

LEFT JOIN 
(
  SELECT id,model,foreign_key,dirname,basename 
  FROM attachments Image  
  WHERE model = 'Mention'
  GROUP BY foreign_key
 )Image  ON (Image.foreign_key = m.id)      

LEFT JOIN 
(
   SELECT v.foreign_id, v.created 
   FROM visiteditems AS v  
   WHERE (v.model = "Mention"  AND v.owner_id = 803)  
    GROUP BY v.foreign_id
)v ON (v.foreign_id = m.id)
LEFT JOIN 
(
   SELECT mention_id,score
   FROM mentionfeedscores mfs  
   WHERE mfs.feed_id = '474737584865424564398208323289092'
   GROUP BY mention_id
)mfs ON (mfs.mention_id = m.id )

WHERE m.indexed BETWEEN "2012-09-16" AND "2012-10-16 23:59:59"  
   AND EXISTS 
  (
     SELECT NULL FROM mentioncache mc  
      WHERE mc.mention_id = m.id AND mc.profile_id = 803  
   )    
ORDER BY m.indexed DESC  
LIMIT 10
share|improve this answer
    
Thanks for your tips! Unfortunately even a simpler version of your query (without the case when construct and the join on the mentionfeedscores-table) returns a result after 400 (!) seconds. Do you have any idea why the performance is so bad? –  Timo Oct 16 '12 at 20:15
    
I'd try removing all LEFT JOINs and adding them one by one... –  a1ex07 Oct 16 '12 at 23:32
    
I don't know why but the query is even slow just with one more join table. I tested it with the attachment-table and the visiteditems-table - in both cases the query performs really slow. It seems that the structure of the joins with the subquery is very slowly. With a "normal" join without the subquery the performance is much better. –  Timo Oct 17 '12 at 5:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.