MySQL Forums
Forum List  »  Newbie

Re: Another newbie question
Posted by: Marc Hillman
Date: February 11, 2014 05:10AM

After all the great help from this forum, I have got the query to work. Thanks Peter. On 60,000 records however the server times out, so now I need to look for efficiency.

There is a common sub-expression.

AND b.ClientID='20005881'
AND (find_in_set('QSL_OK',b.FLAGS)>0
OR find_in_set('EQSL_OK',b.FLAGS)>0
OR find_in_set('LOTW_OK',b.FLAGS)>0)

Can I rely on the optimizer to recognise it, can I convert it to an expression (I don't know how to use expressions), or is there a way to help the optimiser recognise it? All I have is phpMyAdmin, so not much in the way of diagnostic tools.

SELECT CALLSIGN,
       c.Entity,
       QSO_DATE,
       TIME_ON,
       BAND,
       MODE,
       CASE
           WHEN MODE='CW' THEN 'CW'
           WHEN MODE IN ('AM',
                         'SSB',
                         'FM') THEN 'Phone'
           WHEN NOT MODE IN ('DSTAR',
                             'VOI') THEN 'Digital'
           ELSE 'Other'
       END AS MDA,
       CASE NOT EXISTS
                  (SELECT *
                   FROM QSO AS b
                   WHERE a.DXCC=b.DXCC
                     AND b.ClientID='20005881'
                     AND (find_in_set('QSL_OK',b.FLAGS)>0
                          OR find_in_set('EQSL_OK',b.FLAGS)>0
                          OR find_in_set('LOTW_OK',b.FLAGS)>0))
           WHEN TRUE THEN 'Y'
           ELSE 'N'
       END AS NC,
       CASE NOT EXISTS
                  (SELECT *,
                          CASE
                              WHEN MODE='CW' THEN 'CW'
                              WHEN MODE IN ('AM',
                                            'SSB',
                                            'FM') THEN 'Phone'
                              WHEN NOT MODE IN ('DSTAR',
                                                'VOI') THEN 'Digital'
                              ELSE 'Other'
                         END AS MDB
                   FROM QSO AS b
                   WHERE a.DXCC=b.DXCC
                     AND b.ClientID='20005881'
                     AND (find_in_set('QSL_OK',b.FLAGS)>0
                          OR find_in_set('EQSL_OK',b.FLAGS)>0
                          OR find_in_set('LOTW_OK',b.FLAGS)>0) HAVING MDA=MDB)
           WHEN TRUE THEN 'Y'
           ELSE 'N'
       END AS NM,
       CASE NOT EXISTS
                  (SELECT *
                   FROM QSO AS b
                   WHERE a.DXCC=b.DXCC
                     AND a.BAND=b.BAND
                     AND b.ClientID='20005881'
                     AND (find_in_set('QSL_OK',b.FLAGS)>0
                          OR find_in_set('EQSL_OK',b.FLAGS)>0
                          OR find_in_set('LOTW_OK',b.FLAGS)>0))
           WHEN TRUE THEN 'Y'
           ELSE 'N'
       END AS NB
FROM QSO AS a
JOIN DXCC AS c ON a.DXCC=c.DXCCnum
WHERE ClientID='20005881'
  AND find_in_set('QSL_RCVD',FLAGS)>0
  AND find_in_set('QSL_OK',FLAGS)=0
  AND find_in_set('EQSL_OK',FLAGS)=0
  AND find_in_set('LOTW_OK',FLAGS)=0
HAVING NC='Y'
  OR NB='Y'
  OR NM='Y'
ORDER BY Entity

Options: ReplyQuote


Subject
Written By
Posted
February 06, 2014 11:58PM
February 07, 2014 01:11AM
February 07, 2014 06:41AM
February 07, 2014 11:15AM
February 07, 2014 07:50PM
February 08, 2014 11:54AM
February 08, 2014 05:41PM
February 08, 2014 06:55PM
February 08, 2014 06:59PM
February 08, 2014 08:48PM
February 09, 2014 02:56AM
February 09, 2014 07:43PM
February 10, 2014 02:31PM
Re: Another newbie question
February 11, 2014 05:10AM
February 11, 2014 10:41AM
February 11, 2014 08:12PM
February 12, 2014 12:50AM
February 12, 2014 08:32AM
February 12, 2014 07:39PM
February 12, 2014 10:47PM
February 12, 2014 11:50PM
February 13, 2014 12:00AM
February 13, 2014 02:14AM
February 13, 2014 11:10PM
February 16, 2014 12:51AM
February 27, 2014 04:54AM
February 27, 2014 08:47PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.