How to improve ColdFusion MySQL query time?

Question

I have 15 queries that generate data for a table on a page dynamically for the purpose of reports. Each query takes between 250 and 900ms which means a page loading time of 4 to 13 seconds depending on server load. The loading time is causing some users to the think the page is not going to load at all.

I was wondering if there was some way I could streamline the queries to give a more acceptable loading time. Here is one of the queries:

             <cfquery datasource="MeetingDB"  name="One">
              SELECT COUNT( meetingID ) AS countatron
              FROM case_meeting
              WHERE meetingID
              IN (

              SELECT DISTINCT a.meetingID
              FROM case_meeting a
              INNER JOIN meeting b ON a.meetingID = b.meetingID
              WHERE b.categoryID =  '1' 
              AND SUBSTRING( meetingCode, 5, 2 ) 
              BETWEEN 12 
              AND 22 
              AND SUBSTRING( meetingCode, 7, 2 ) 
              BETWEEN 01 
              AND 12 
              AND SUBSTRING( meetingCode, 9, 2 ) 
              BETWEEN 01 
              AND 31
              )
              AND caseID
              IN (
              '1',  '2',  '3', '28',  '29',  '30', '39', '40', '45'
              )
              GROUP BY meetingID
              HAVING COUNT( caseID ) > 0 AND COUNT( caseID ) < 2
              </cfquery>

              <td><cfoutput> #One.recordcount# </cfoutput></td>

Answer 1

Try this query

SELECT COUNT( a.meetingID ) AS countatron
          FROM case_meeting a, case_meeting b
          WHERE a.meetingID = b.meetingID 
          AND b.categoryID =  '1' 
          AND SUBSTRING( b.meetingCode, 5, 2 ) 
          BETWEEN 12 
          AND 22 
          AND SUBSTRING( b.meetingCode, 7, 2 ) 
          BETWEEN 01 
          AND 12 
          AND SUBSTRING( b.meetingCode, 9, 2 ) 
          BETWEEN 01 
          AND 31
          AND b.caseID
          IN (
          '1',  '2',  '3', '28',  '29',  '30', '39', '40', '45'
          )
          GROUP BY a.meetingID
          HAVING COUNT( a.caseID ) = 1