SELECT COUNT(*) FROM CP6 WHERE AOC = 1;
 SELECT COUNT(*) FROM CP6 WHERE AOC = 2;
 SELECT COUNT(*) FROM CP6 WHERE HOLD IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE ICI IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE OIN IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE TIN IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE MPTY IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE CLIP IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE CLIR IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE COLP IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE COLR IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE OICK IS NOT NULL;
 SELECT COUNT(*) FROM CP6 WHERE TICK IS NOT NULL; 

 SELECT
 SUM(IF(AOC=1, 1,0)) as value1,
 SUM(IF(AOC=2, 1,0)) as value2,
 SUM(IF(HOLD IS NOT NULL, 1,0)) as value3,
 SUM(IF(ICI IS NOT NULL, 1,0)) as value4,
 SUM(IF(OIN IS NOT NULL, 1,0)) as value5,
 SUM(IF(TIN IS NOT NULL, 1,0)) as value6,
 SUM(IF(MPTY IS NOT NULL, 1,0)) as value7,
 SUM(IF(CLIP IS NOT NULL, 1,0)) as value8,
 SUM(IF(CLIR IS NOT NULL, 1,0)) as value9,
 SUM(IF(COLP IS NOT NULL, 1,0)) as value10,
 SUM(IF(COLR IS NOT NULL, 1,0)) as value11,
 SUM(IF(OICK IS NOT NULL, 1,0)) as value12,
 SUM(IF(TICK IS NOT NULL, 1,0)) as value13
 FROM CP6 WHERE AOC=1 OR AOC=2 OR HOLD IS NOT NULL OR ICI IS NOT NULL OR OIN IS NOT NULL OR TIN IS NOT NULL OR MPTY IS NOT ⇒
 NULL OR CLIP IS NOT NULL OR CLIR IS NOT NULL OR COLP IS NOT NULL OR COLR IS NOT NULL OR OICK IS NOT NULL OR TICK IS NOT NULL;

• Keep the number of join-s to the minimum.

• Write queries that can be pushed down to the cluster.

• Optimize queries using the IN operator. See Example 6.

Instead of:
 
 SELECT COUNT(*) INTO NACTSUBSCNT FROM CP2 WHERE CSLOC = 0 OR
                 CSLOC = 3 OR
                 CSLOC = 4 OR
                 (CSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATECS)),\
'%y%m%d') ) ) OR
                 PSLOC = 0 OR
                 PSLOC = 3 OR
                 PSLOC = 4 OR
                 (PSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDA.eps)),\
'%y%m%d') ) );
 
when possible use:
 
 SELECT COUNT(*) INTO NACTSUBSCNT FROM CP2
         WHERE CSLOC IN (0,3,4) OR
         (CSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDATECS)),'%y%m%d')\
 ) ) OR
         PSLOC IN (0,3,4) OR
         (PSLOC = 5 AND ( DATE_SUB(NOW(), INTERVAL INTERVAL_DAY DAY) < STR_TO_DATE(hexStr2decStr(HEX(PURGEDA.eps)),'%y%m%d')\
 ) );

#!/bin/bash

. /home/cudb/oam/performanceMgmt/appCounters/scripts/appCountersLib.sh

QUERY="select NSUBSCNT,NGSUBSCNT,NUSUBSCNT from GRP_AUCSUBS;"

#DEBUG=1
mysql_query "$QUERY"

# Simple solution is to exit on failure
# More critical counters could follow a retry policy 
if [ $? != 0 ]
then
  logging_system 'error' "Query \"$QUERY\" failed with \"$ROWS\""
  exit 1
fi

GROUPID=AUCSUBS 
COUNTERID=AUCSUBS
format_counter $GROUPID $COUNTERID

exit $?

<?xml version="1.0" encoding="UTF-8"?>
<pmCntGroup xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns="http://www.ericsson.com/esa"
xsi:schemaLocation="http://www.ericsson.com/esa pmCounter.xsd"
active="yes">
  <identification>
    <groupId>AUCSUBS</groupId>
  </identification>
  <description>
    <groupDescr>AUC application subscriber counters</groupDescr>
    <groupInfo>These counters permits evaluate the use of 
certain functions of the AUC application</groupInfo>
  </description>
  <!--Counter collection with data source SCRIPT-->
  <!-- G1-->
  
  <cntCollection active="yes" activeSnmp="yes">
    <identification>
      <collectionId>AUCSUBS</collectionId>
    </identification>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NSUBSCNT
		</counterId>
      </identification>
      <description>
        <counterDescr>Number of subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NGSUBSCNT</counterId>
      </identification>
      <description>
        <counterDescr>Number of GSM subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <cntDefinition cntType="Gauge">
      <identification>
        <counterId>NUSUBSCNT</counterId>
      </identification>
      <description>
        <counterDescr>Number of UMTS subscribers</counterDescr>
        <counterInfo/>
      </description>
    </cntDefinition>
    <dataSource interval="900">
      <script>
	  <location>/cluster/home/cudb/oam/performanceMgmt/
appCounters/scripts/APCount_AUCSUBS.sh</location>
      </script>
    </dataSource>
  </cntCollection>
</pmCntGroup>