Mysql – How to improve this query so it’ll run quicker

MySQLperformancequery-performance

I've got this query:

    SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType
    FROM     macs m, network_devices n, vlans v, ports p
    WHERE    n.nd_ip_address = m.ipAddr
    AND      m.vlanID        = v.vlan_id
    AND      p.vlanID        = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType
    ORDER BY m.vlanID

Each of these tables have approximately 50.000 records. The query is taking north of 30min. Can I improve it to make it more efficient?

EDIT: Table definitions:
macs table
ipAddr, vlanID, macAddress, interfaceName

network_devices table
network_device_id, nd_location, nd_name, nd_ip_address, etc

vlans table
vlan_id, vlan_name

ports table
interfaceName, interfacePortType, nodeIP, interfaceDescription

I've limited to mostly what's required from the query.

There's no indexes configured atm nor execution plan as this is not an application per se. This is just a basic script to be run occasionally for internal purposes only and the data will become obsolete within a few days, so we just need to query temporarily to get some answer while we upgrade our network.

Thanks

Best Answer

You can try this:

SELECT m.vlanID, 
        v.vlan_name, 
        m.interfaceName, 
        count(1) as mac,
         n.nd_name, 
         p.interfaceDescription, 
         p.interfacePortType
    FROM  macs m 
    INNER JOIN network_devices n 
        ON n.nd_ip_address = m.ipAddr
    INNER JOIN vlans v  
        ON m.vlanID = v.vlan_id
    INNER JOIN ports p
        ON p.vlanID = m.vlanID
    GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType

A few things about the changes:

The cartesian join to inner join switch is purely cosmetic, unless the mysql implementation you're using doesn't support the cartesian inner join syntax, which would be very odd.
Removing the Order By clause should provide a performance bump, but depending on the way you're using the results may not be useful.
If m.macAddress is sometimes null, count(1) != count(m.macAddress)

These small optimizations stated, there is not a whole lot to optimize about this query itself. There is something about the context of the query that is causing the exceptional runtime.

Indexes on n.nd_ip_address, m.ipAddr,m.vlanID, v.vlan_id, and p.vlanID would certainly help substantially. If you have to run the query multiple times, it would be well worth the time invested in creating the indexes even on temporary data.

It's even likely that re-creating the indexes every time the query runs would cause the overall runtime to be much lower.

Related Solutions

MySql – How to improve this query

What are you trying to achieve here? I am not a MySQL programmer; however, as I understand SQL in general, every column that is not listed in the GROUP BY clause must be part of an aggregate like

SELECT
    MAX(id) AS max_id,
    chain, subchain, job_type
FROM
    `job_logs`
WHERE
    user_id = ? AND
    workflow_state = ?
GROUP BY
    chain, subchain, job_type

SELECT * in a grouping query will not work in most (if not all) SQL implementations.

UPDATE

In order to get the row with the highest id for each group you would have to embed the query above in an "outer" query.

SELECT *
FROM `job_logs`
WHERE id IN (
    SELECT
        MAX(id) AS max_id
    FROM
        `job_logs`
    WHERE
        user_id = ? AND
        workflow_state = ?
    GROUP BY
        chain, subchain, job_type 
)

This query is deterministic and should work with most SQL dialects.

Some query engines perform better with joins than with "IN subquery". You can give this a try

SELECT A.*
FROM
    `job_logs` A
    INNER JOIN (
        SELECT
            MAX(id) AS max_id
        FROM
            `job_logs`
        WHERE
            user_id = ? AND
            workflow_state = ?
        GROUP BY
            chain, subchain, job_type) B
    ON A.id = B.max_id;

Mysql – How to improve this query

First, since you already have a UNIQUE index that contains the user_id, you should be able to get rid of the id field, and use the UNIQUE index as the PRIMARY KEY:

CREATE TABLE `twitter_relationships` (
  `user_id` int(11) NOT NULL,
  `source_twitter_id` bigint(20) NOT NULL,
  `target_twitter_id` bigint(20) NOT NULL,
  `relationship_status` tinyint(1) NOT NULL,
  `status_change_date` int(11) unsigned DEFAULT NULL,
  PRIMARY KEY (`user_id`,`source_twitter_id`,`target_twitter_id`),
  KEY `target_status_and_change_date_index`
    (`user_id`,`target_twitter_id`,`relationship_status`,`status_change_date`),
  KEY `user_id_index` (`user_id`,`status_change_date`)
) ENGINE=InnoDB AUTO_INCREMENT=116597775 DEFAULT CHARSET=latin1
PARTITION BY HASH (user_id) PARTITIONS 1000;

Unfortunately, while this removes an index, it may increase storage requirements, due to the way that InnoDB indexes data. See "How Secondary Indexes Relate to the Clustered Index" in http://dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html

Second, while the source_and_target index has two of the three fields in your WHERE clause, MySQL will have to do an additional read to find the relationship_status.

Therefore, to improve performance, create an index that includes all three fields in your WHERE clause:

CREATE INDEX user_source_status ON twitter_relationships
    (`user_id`,`source_twitter_id`,`relationship_status`);

Then, if MySQL doesn't use this index automatically, you can force using it, with:

SELECT target_twitter_id 
 FROM `twitter_relationships` FORCE INDEX (user_source_status)
WHERE (`twitter_relationships`.`user_id` = ? 
   AND `twitter_relationships`.`source_twitter_id` = ?
   AND `twitter_relationships`.`relationship_status` = ?) 
LIMIT ?, ?

Lastly, you're missing the UNSIGNED attribute on the id, user_id, source_twitter_id, and target_twitter_id fields. I'm guessing these fields will never store negative values, so it would make sense to make them UNSIGNED.

Best Answer

Related Solutions

MySql – How to improve this query

Mysql – How to improve this query

Related Question