title: Fastest most/efficient way to do pagination with SQL searching DB2
body: <p>Right now I do two separate SQL statements, one doing a <code>SELECT COUNT(*)</code> on basically the same criteria as the search statement. I am not the best at making these statements and sometimes are a little slow and I would like to know if there is a better way to be doing what I do. Possibly doing only one SQL statement and some more work in PHP? Here is an example "search contains" I have the statements for.</p>
  
  <p>On the second statement you will see the X between Y, that's partially calculated by the result from the first row count statement.</p>
  
  <p>SQL Row Count: </p>
  
  <pre><code>SELECT COUNT(*) 
  FROM itemmast 
  LEFT OUTER JOIN itemweb 
  ON iline = line 
  AND iitem = item 
  JOIN linemst 
  ON iline = lline 
  LEFT OUTER JOIN custord 
  ON opline = iline 
  AND opitem = iitem 
  AND opcust = '12345' 
  LEFT OUTER JOIN ordwdtl 
  ON owline = iline 
  AND owitem = iitem 
  AND owusr ='user' 
  AND owcust ='12345' 
  WHERE ico = 01 
  AND iecomm = 'Y'  
  AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
       OR LINE LIKE '%FOO%' 
       OR UPPER(MFGNAME) LIKE '%FOO%' 
       OR UPPER(ITEM) LIKE '%FOO%' 
       OR UPPER(PRODNAME) LIKE '%FOO%' 
       OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
       OR UPPER(IMFGNO) LIKE '%FOO%' 
       OR UPPER(IITEM) LIKE '%FOO%') 
  </code></pre>
  
  <p>SQL Search:  </p>
  
  <pre><code>SELECT * 
  FROM (SELECT iline AS line, iitem AS item, rownumber() OVER (ORDER BY item) AS ROW_NUM 
        FROM itemmast 
        LEFT OUTER JOIN itemweb 
        ON iline = line 
        AND iitem = item 
        JOIN linemst 
        ON iline = lline 
        LEFT OUTER JOIN custord 
        ON opline = iline 
        AND opitem = iitem 
        AND opcust = '12345' 
        LEFT OUTER JOIN ordwdtl 
        ON owline = iline 
        AND owitem = iitem 
        AND owusr = 'user' 
        AND owcust = '12345' 
        WHERE ico = 01 
        AND iecomm = 'Y' 
        AND (UPPER(ITEMDESC) || UPPER(PRODDESC)) LIKE '%FOO%' 
             OR LINE LIKE '%FOO%' 
             OR UPPER(MFGNAME) LIKE '%FOO%' 
             OR UPPER(ITEM) LIKE '%FOO%' 
             OR UPPER(PRODNAME) LIKE '%FOO%' 
             OR UPPER(IDESC1 || IDESC2) LIKE '%FOO%' 
             OR UPPER(IMFGNO) LIKE '%FOO%' 
             OR UPPER(IITEM) LIKE '%FOO%')) 
        AS TEMP 
  WHERE ROW_NUM BETWEEN 0 AND 25
  </code></pre>