开发者

How can I keep queries like this out of mysql_slow.log?

I have the following query I need help with.

SELECT a.artist, a.facepic
FROM artists_vs AS v, artists AS a 
WHERE v.genreid =0 
AND (v.artistid1 = a.artistid OR v.artistid2 = a.artistid)
ORDER BY v.bid DESC 
LIMIT 20

All the fields in the query are indexed, but when I run an explain I get the following:

1   SIMPLE  v   ref genreid,artistid1,artistid2 genreid 4   const   15  Using temporary; Using filesort
1   SIMPLE  a   ALL PRIMARY NULL    NULL    NULL    18165   Range checked for each record (index map: 0x1)

I need to have log-queries-not-using-indexes enabled in my.cnf

开发者_如何学编程

Can anyone suggest how I could rewrite this query to keep it out of the slow log?


you can try something like this:

SELECT 
    a.artist, 
    a.facepic,
    V.bid
FROM artists_vs AS v
inner join artists as a on v.artistid1 = a.artistid
WHERE v.genreid = 0 
union
SELECT 
    a.artist, 
    a.facepic,
    v.bid
FROM artists_vs AS v
inner join artists as a on v.artistid2 = a.artistid
WHERE v.genreid = 0 
ORDER BY bid DESC 
LIMIT 20
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜