title: slow sql query optimization,
body: <p>I know it maybe hard to optimize this query without the db table, but am a newbie and wont trust myself, It is taking a lot of time to excuse this like 50 seconds and i cant think of another way to do it, just seems long</p>
  
  <p>any tips are welcome to make this faster</p>
  
  <pre><code>SELECT SUM(x) AS d
  
  FROM     ( SELECT COUNT(*) AS x
             FROM ( SELECT DISTINCT id
                    FROM streamsignature
                    WHERE time &lt; '2013-01-03'
                      AND signature = 'v'
                      AND signaturelevel = 'check'
           ) AS subq 
  
  INNER JOIN files             ON subq.id  = files.id 
  INNER JOIN filedata fm       ON files.id = fm.id
  INNER JOIN filetag v         ON files.id = v.id 
  INNER JOIN filetype ft       ON files.id = ft.id
  
  LEFT  JOIN definitiondata dd ON files.id = dd.id
  
  WHERE (    ( NOT filename LIKE '%abc2%' AND filename LIKE '%abc%' )
          OR (     filename LIKE '%abc2%' AND fm.dset = 1           ) )
    AND v.type   BETWEEN 0 and 4
    AND v.length BETWEEN 3 and 7
    AND v.decoder    = 1
    AND v.lighting   = 'bright'
    AND NOT vmd.time = 'xx:xx:Xx'
    AND ft.country   = 'IQ'
  
  UNION
  
              i have a bunch of them like 4 with different conditions and 
            stuff
  </code></pre>