slow sql query optimization,

Question

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

any tips are welcome to make this faster

SELECT SUM(x) AS d

FROM     ( SELECT COUNT(*) AS x
           FROM ( SELECT DISTINCT id
                  FROM streamsignature
                  WHERE time < '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

Answer 1

I don't get something about your query, i don't know if I'm missunderstanding but:

( SELECT COUNT(*) AS x
           FROM ( SELECT DISTINCT id
                  FROM streamsignature
                  WHERE time < '2013-01-03'
                    AND signature = 'v'
                    AND signaturelevel = 'check'
         ) AS subq 

INNER JOIN files             ON subq.id

Is the number of rows the id for the files table? As you, on your join clause have

ON subq.id  = files.id

And the only selected field is x, and it is the row count

Anyway...

I think is more like indexes problems. You should really use EXPLAIN to find out wich indexes are missing.

For example:

EXPLAIN SELECT * FROM orderdetails d
INNER JOIN orders o ON d.orderNumber = o.orderNumber
INNER JOIN products p ON p.productCode = d.productCode
INNER JOIN productlines l ON p.productLine = l.productLine
INNER JOIN customers c on c.customerNumber = o.customerNumber
WHERE o.orderNumber = 10101G

executing that query will produce:

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: l
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 7
        Extra: 
********************** 2. row **********************
           id: 1
  select_type: SIMPLE
        table: p
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 110
        Extra: Using where; Using join buffer
********************** 3. row **********************
           id: 1
  select_type: SIMPLE
        table: c
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 122
        Extra: Using join buffer
********************** 4. row **********************
           id: 1
  select_type: SIMPLE
        table: o
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 326
        Extra: Using where; Using join buffer
********************** 5. row **********************
           id: 1
  select_type: SIMPLE
        table: d
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2996
        Extra: Using where; Using join buffer
5 rows in set (0.00 sec)`

If you look at the above result, you can see all of the symptoms of a bad query. But even if I wrote a better query, the results would still be the same since there are no indexes. The join type is shown as “ALL” (which is the worst), which means MySQL was unable to identify any keys that can be used in the join and hence the possible_keys and key columns are null. Most importantly, the rows column shows MySQL scans all of the records of each table for query. That means for executing the query, it will scans 7 × 110 × 122 × 326 × 2996 = 91,750,822,240 records to find the four matching results. That’s really horrible, and it will only increase exponentially as the database grows.

Now lets add some obvious indexes, such as primary keys for each table, and execute the query once again. As a general rule of thumb, you can look at the columns used in the JOIN clauses of the query as good candidates for keys because MySQL will always scan those columns to find matching records.

ALTER TABLE customers
    ADD PRIMARY KEY (customerNumber);
ALTER TABLE employees
    ADD PRIMARY KEY (employeeNumber);
ALTER TABLE offices
    ADD PRIMARY KEY (officeCode);
ALTER TABLE orderdetails
    ADD PRIMARY KEY (orderNumber, productCode);
ALTER TABLE orders
    ADD PRIMARY KEY (orderNumber),
    ADD KEY (customerNumber);
ALTER TABLE payments
    ADD PRIMARY KEY (customerNumber, checkNumber);
ALTER TABLE productlines
    ADD PRIMARY KEY (productLine);
ALTER TABLE products 
    ADD PRIMARY KEY (productCode),
    ADD KEY (buyPrice),
    ADD KEY (productLine);
ALTER TABLE productvariants 
    ADD PRIMARY KEY (variantId),
    ADD KEY (buyPrice),
    ADD KEY (productCode);

Let’s re-run the same query again after adding the indexes and the result should look like this:

********************** 1. row **********************
           id: 1
  select_type: SIMPLE
        table: o
         type: const
possible_keys: PRIMARY,customerNumber
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
********************** 2. row **********************
           id: 1
  select_type: SIMPLE
        table: c
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
        Extra: 
********************** 3. row **********************
           id: 1
  select_type: SIMPLE
        table: d
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 4
        Extra: 
********************** 4. row **********************
           id: 1
  select_type: SIMPLE
        table: p
         type: eq_ref
possible_keys: PRIMARY,productLine
          key: PRIMARY
      key_len: 17
          ref: classicmodels.d.productCode
         rows: 1
        Extra: 
********************** 5. row **********************
           id: 1
  select_type: SIMPLE
        table: l
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 52
          ref: classicmodels.p.productLine
         rows: 1
        Extra: 
5 rows in set (0.00 sec)

After adding indexes, the number of records scanned has been brought down to 1 × 1 × 4 × 1 × 1 = 4. That means for each record with orderNumber 10101 in the orderdetails table, MySQL was able to directly find the matching record in all other tables using the indexes and didn’t have to resort to scanning the entire table.

Answer 2

It's rather difficult to answer this question sufficiently well since there isn't enough background detail in the question. However, looking at the query some of these points might help you out:

• Try and perform fewer joins
• Avoid using LIKE queries with a wildcard prefix and suffix (i.e. '%thing%') - it will result in a full table scan, something that will cripple performance if there are a large number of rows
• Try and avoid sub-selects. They're not always a problem, but they might be indicative of approaching the query in the wrong way
• Use the Explain syntax to understand where you might be missing important indexes

Good luck!