How i can optimize this query in mysql?

Question

I have a query like this -

    SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u,
  interfacedescriptioninfo j 
WHERE u.interfacedescriptioninfoid = j.id
  AND u.deviceId = j.deviceId 
  AND u.interfaceNameAlias = j.name 
  AND j.toDevice NOT IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12) 
  AND u.deviceAlias IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12) 
  AND CONCAT(u.name, u.deviceAlias) NOT IN 
  (SELECT DISTINCT 
    CONCAT(v.name, fromDevice) 
  FROM
    vlaninterfaces v,
    interfacedescriptioninfo i 
  WHERE v.interfacedescriptioninfoid = i.id
    AND v.deviceId = i.deviceId 
    AND v.interfacenameAlias = i.name 
    AND v.deviceAlias IN 
    (SELECT 
      deviceAlias 
    FROM
      devices 
    WHERE siteId = 12) 
    AND v.interfacenameAlias LIKE '%ae%' 
    AND IF(
      i.toDevice IS NULL,
      i.interconnectedDevice,
      i.toDevice
    ) IN 
    (SELECT 
      deviceAlias 
    FROM
      devices 
    WHERE deviceClass = 'SWITCH' 
      AND siteId = 12)) 
GROUP BY CONCAT(u.name, u.deviceAlias) ;

I tried to use MINUS but found out in mysql we cant use such set operator.

In Explain plan, table vlaninterfaces using no key and scanning all rows.

Please let me know the alternate of NOT IN with example(if possible same query).

score 0 · Answer 1 · answered Jun 30 '14 at 12:59

Your query is quite complex so I'll only show part. It's possible to LEFT JOIN onto a table and check for null rather than using NOT IN. I.e. If we have the query:

 SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u,
  interfacedescriptioninfo j 
WHERE u.interfacedescriptioninfoid = j.id
  AND u.deviceId = j.deviceId 
  AND u.interfaceNameAlias = j.name 
  AND j.toDevice NOT IN 
  (SELECT 
    deviceAlias 
  FROM
    devices 
  WHERE siteId = 12)

Then we can join onto device alias with siteId 12 and check that the join wasn't successful in the where clause (note in the example below I also change the join on vlaninterfaces and interfacedescriptioninfo to be explicit - I find this more readable):

SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u
  INNER JOIN interfacedescriptioninfo j ON (u.interfacedescriptioninfoid = j.id AND u.deviceId = j.deviceId AND u.interfaceNameAlias = j.name )
  LEFT JOIN devices da ON (j.toDevice = da.deviceAlias AND da.siteId = 12)
WHERE 
   da.deviceAlias IS NULL

score 0 · Answer 2 · answered Jun 30 '14 at 13:05

Ok, this will need a lot of trial and error, but I always try to rewrite the IN clause to a join, thus avoiding an implicit subquery. Same goes for the NOT IN, that you can replace with a LEFT JOIN + WHERE something is NULL

SELECT 
  CONCAT(u.name, u.deviceAlias),
  u.name,
  u.deviceAlias,
  j.description,
  u.description AS vlanDescription 
FROM
  vlaninterfaces u 
  JOIN   interfacedescriptioninfo j ON u.interfacedescriptioninfoid = j.id AND u.deviceId = j.deviceId AND u.interfaceNameAlias = j.name 
  JOIN devices d2 ON d2.deviceAlias=u.deviceAlias AND d2.siteId=12
  LEFT JOIN devices d1 ON d1.deviceAlias=j.toDevice AND d1.siteId = 12
  LEFT JOIN  (SELECT DISTINCT v.name, fromDevice
              FROM
              vlaninterfaces v,
              JOIN  interfacedescriptioninfo i ON v.interfacedescriptioninfoid = i.id   AND v.deviceId = i.deviceId   AND v.interfacenameAlias = i.name 
              JOIN devices d3 ON d3.deviceAlias=v.deviceAlias AND d3.siteId=12
              JOIN devices d4 ON d4.deviceAlias=IF(i.toDevice IS NULL,  i.interconnectedDevice,  i.toDevice) AND d4.siteId=12 AND d4.deviceClass = 'SWITCH' 
              WHERE v.interfacenameAlias LIKE '%ae%' 
            ) as subquery ON u.name=subquery.name AND u.deviceAlias=subquery.fromDevice

WHERE d1.deviceAlias is NULL -- this replaces the first NOT IN
and subquery.name is NULL  -- this replaces the second NOT IN

querying by the concat of two fields is in fact discarding the indexes. You don't need to do that.

Finally, the grouping by concat of name and deviceAlias makes no sense here, for you don't have any aggregate functions in the select.

How i can optimize this query in mysql?

2 Answers2