Mysql Subquery Optimization?

Question

Can I use this code in any different way to getting better performance?

I tried this way;

SELECT K.id, M.m_id, K.Sil, K.Banli, K.Foto, K.KullaniciAdi, K.Ad, K.Soyad, K.Gender,
(select rate_id,Rate,Zaman from ratings where UyeID=K.id ORDER BY rate_id DESC LIMIT 1)

But I cant do what I want.

This is working code. Giving results, that I want:

SELECT K.id, M.m_id, K.Sil, K.Banli, K.Foto, K.KullaniciAdi, K.Ad, K.Soyad, K.Gender,
       (  SELECT R.mr_id FROM mesajlarreply R 
          WHERE R.CID=M.m_id 
          ORDER BY R.mr_id DESC LIMIT 1
       ) as mr_id,
       (  SELECT R.Mesaj FROM mesajlarreply R 
          WHERE R.CID=M.m_id 
          ORDER BY R.mr_id DESC LIMIT 1
       ) as Mesaj,
       (  SELECT R.KayitZaman FROM mesajlarreply R 
          WHERE R.CID=M.m_id 
          ORDER BY R.mr_id DESC LIMIT 1
       ) as KayitZaman
FROM mesajlar M, kullanicilar K
WHERE
     CASE
         WHEN M.K1 =  '1'  THEN M.K2 = K.id
         WHEN M.K2 =  '1'  THEN M.K1 = K.id
     END
   AND ( M.K1 =  '1'  OR M.K2 =  '1' )
ORDER BY M.m_id DESC 
LIMIT 20

Could you post structures of both tables ? Or at least please append an information about primary keys primary keys - which columns are primary keys in these two tables ? — krokodilko, May 25 '14 at 06:06
Your edit makes this appear to be a new/different query. If so, you should be able to adapt my answer for the new query; if you get stuck, post a _new_ question. — Clockwork-Muse, May 27 '14 at 10:10
@Clockwork-Muse i posted a new question but nobodys help me. how can i do that? http://stackoverflow.com/questions/23901430/mysql-subquery-optimize?noredirect=1#comment36808675_23901430 — traBolic EM, Jun 03 '14 at 20:43
@traBolicEM - Because you haven't shown what _new_ problem you're facing. You seem to be expecting us to do everything (even minor edits) for you. You should be able to adapt my existing answer for your new question; I'd gladly tweak my answer if there were typos in your question, but we expect _you_ to handle changes on your end if you change tables or other columns. If the performance in my answer isn't good enough for **this** question, then post more details here (things like what indices you have, the results of an EXPLAIN plan), to get better/additional help. — Clockwork-Muse, Jun 03 '14 at 21:56

score 0 · Answer 1 · edited May 23 '17 at 11:50

Assuming that the ordering of the subqueries is supposed to be the same (not a typo), then the combination of the three is a greatest-n-per-group problem. MySQL has several existing solutions for this, and if the number of rows-per-group is small I think I prefer the style in this answer, which for your case would look like this:

SELECT rToUse.mr_id, rToUse.mesaj, rToUse.kayitZaman
FROM Mesajlarreply rToUse
LEFT JOIN Mesajlarreply rKnockout
       ON rKnockout.cId = rToUse.cId AND rToUse.mr_id < rKnockout.mr_id
WHERE rKnockout.mr_id IS NULL

(This works because when the rows are joined, the one with the greatest mr_id has no match. All other rows are then discarded due to rKnockout.mr_id IS NULL being true)
The fact that you're using three subqueries is probably doing a number on the system.

Your CASE statement evaluates to a simple set of Boolean conditions:

(M.k1 = '1' AND M.k2 = K.id) OR (M.k1 <> '1' AND M.k2 = '1' AND M.k1 = K.id)

... I'm going to assume that you don't have cases where both k1 and k2 equal '1' at the same time, so the M.k1 <> '1' (which was to preserve the semantics of the CASE if that is true) would be unnecessary. Note that ORs can still be a performance pain point. Too, if any of those columns are actually some numeric type, the joins will require conversion first, which will probably kill performance.

So, I'd probably start by writing the query about like this:

SELECT K.id, M.m_id, K.sil, K.banli, K.foto, K.kullaniciAdi, K.ad, K.soyad, K.gender,
       R.mr_id, r.mesaj, r.kayitZaman
FROM Mesajlar M
JOIN Kullanicilar K
  ON  (M.k1 = '1' AND M.k2 = K.id) 
      OR (M.k2 = '1' AND M.k1 = K.id)
LEFT JOIN (SELECT rToUse.cId, rToUse.mr_id, rToUse.mesaj, rToUse.kayitZaman
           FROM Mesajlarreply rToUse
           LEFT JOIN Mesajlarreply rKnockout
                  ON rKnockout.cId = rToUse.cId 
                     AND rToUse.mr_id < rKnockout.mr_id
           WHERE rKnockout.mr_id IS NULL) R
       ON R.cId = M.m_id
ORDER BY M.m_id DESC
LIMIT 20

(Untested, of course - no provided dataset)
I've used a LEFT JOIN for the subquery to preserve the given data semantics. If you have rows for all cases (or only care about those who do), it could be changed to an (INNER) JOIN. Depending on how your dataset is laid out (and what indices you have/don't have), this may not perform any better, but this should generally be friendlier to the optimizer.

Mysql Subquery Optimization?

1 Answers1