Search code examples
mysqlperformancesubqueryquery-performance

mysql with few tables, subquery on one large table performs slow

We are experiencing slow performance with a query on mysql database and we are not sure if the query is wrong or maybe mysql or server is not good enough.

The query with a subquery returns some project details (3 fields) and filename of the latest taken picture of a online camera.

Info Table 'projects' contains 40 records. Table 'cameras' contains approx 40 records (1 project, multiple cameras possible) Table 'cameraimages' contains around 250000 (250 thousand) records. (1 camera can have thousands of images) Engine is InnoDb Database size is about 100Mb approx No indexes are added yet.

Version number mysql 8.0.15

This is the query

SELECT
    pj.title,
    pj.description,
    pj.city,
    (SELECT cmi.filename 
       FROM cameras cm
       LEFT JOIN cameraimages cmi ON cmi.cameraId = cm.id
      WHERE cm.projectId = pj.id
      ORDER BY cmi.dateRecording DESC 
      LIMIT 0,1) as latestfilename
FROM
    projects pj

It takes 40-50 seconds to return this data. That is to long for a webpage but I think it should take not that long at all. We tested the same query on another server, to compare. Same data, same query. That takes 25 seconds.

My questions are:

  1. Is this query to 'heavy/bad' and if it is, what query should perform better?
  2. Is there a way, or what should I check, to find out why this query runs better on an older/other server?

Hope someone can give some advice. Thnx!

Additional info

CREATE TABLE `cameras` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `guid` varchar(50) DEFAULT NULL,
  `title` varchar(50) DEFAULT NULL,
  `longitude` double DEFAULT NULL,
  `latitude` double DEFAULT NULL,
  `status` smallint(6) DEFAULT NULL,
  `cameraUid` varchar(20) DEFAULT NULL,
  `cameraFriendlyName` varchar(50) DEFAULT NULL,
  `projectId` int(11) DEFAULT NULL,
  `dateCreated` datetime DEFAULT NULL,
  `dateModified` datetime DEFAULT NULL,
  `address` varchar(100) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `createArchive` smallint(6) DEFAULT '0',
  `createDaily` smallint(6) DEFAULT '1',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=88 DEFAULT CHARSET=latin1

Columns cameraId,dateRecording is unique. One camera takes on picture at the time.

Solution

  • You're using a so-called dependent subquery. That's slow.

    I guess cameraimages.id is a primary key for your cameraimages file. That's a guess. You didn't provide enough information in your question to answer it with certainty.

    I also guess that the dateRecording values in cameraimages are in the same order as your autoincrementing primary key id values. That is, I guess you INSERT a record to that table at the time each image is captured.

    Let's break this down.

    You want the id of the most recent image from each project. How can you get that? Write a subquery to retrieve the largest, most recent id for each project.

                             SELECT cm.projectId,
                                MAX(cmi.id) imageId
                           FROM cameras cm
                           JOIN cameraimages cmi ON cmi.cameraId = cm.id
                          GROUP BY cm.projectId

    That subquery does the heavy lifting of searching your big table. It does it just once, not for every project, so it won't take as long.

    Then put that subquery into your query to retrieve the columns you need.

     SELECT 
       pj.title,
       pj.description,
       pj.city,
       cmi.filename latestfilename
  FROM projects pj
  JOIN (
                         SELECT cm.projectId,
                                MAX(cmi.id) imageId
                           FROM cameras cm
                           JOIN cameraimages cmi ON cmi.cameraId = cm.id
                          GROUP BY cm.projectId
       ) latest ON pj.id = latest.projectId
  JOIN cameraimages cmi ON cmi.imageId = latest.imageId

    This has a series of JOINs making a chain from projects to the latest subquery and from there to cameraimages.

    This depends on cameraimages.id values being in chronological order. It can still be done if they aren't in that order with a more elaborate query.