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
It can make sense to insert data in order. There's lots of caveats to this though. If the data isn't frequently updated and if you're using certain types of tables or indexes (e.g. IOT, clustered indexes).
Being in order means that if you're doing a range scan of the ordered column (e.g. BETWEEN x AND Y
) then the data is more likely to be in a contiguous set of blocks.
Best Answer
You only need a
CASE
expression in theORDER BY
, to divide the rows into two groups, so the ones with class 'A' or 'B' are put in first group (1
) and the all the others in the second (2
):