[Solved] Get the cars that passed specific cameras based on individual search criteria
Looking to automatically optimize YOUR SQL query? Start for free.

EverSQL Database Performance Knowledge Base

Get the cars that passed specific cameras based on individual search criteria

Database type:

My question is related to this question but it differs because date time is different for each search filter.

MYSQL/MARIADB Schema and sample data:

CREATE DATABASE IF NOT EXISTS `puzzle` DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_unicode_ci;

USE `puzzle`;

DROP TABLE IF EXISTS `event`;

CREATE TABLE `event` (
  `eventId` bigint(20) NOT NULL AUTO_INCREMENT,
  `sourceId` bigint(20) NOT NULL COMMENT 'think of source as camera',
  `carCode` varchar(40) NOT NULL COMMENT 'ex: A',
  `carNumber` varchar(40) NOT NULL COMMENT 'ex: 5849',
  `createdOn` datetime DEFAULT NULL,
  PRIMARY KEY (`eventId`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


INSERT INTO `event` (`eventId`, `sourceId`, `carCode`, `carNumber`, `createdOn`) VALUES
    (1, 44,'A', '4456', '2016-09-20 20:24:05'),
    (2, 26,'B', '26484', '2016-09-20 20:24:05'),
    (3, 5,'A', '4456', '2016-09-20 20:24:06'),
    (4, 3,'C', '72704', '2016-09-20 20:24:15'),
    (5, 3,'D' ,'399606', '2016-09-20 20:26:15'),
    (6, 5, 'A', '4456', '2016-09-20 20:27:25'),
    (7, 44,'C', '72704', '2016-09-20 20:29:25'),
    (8, 3,'A' ,'4456', '2016-09-20 20:30:55'),
    (9, 44,'B' ,'26484', '2016-09-20 20:34:55'),
    (10, 26,'B' ,'4456', '2016-09-20 20:35:15'),
    (11, 3, 'C','72704', '2016-09-20 20:35:15'),
    (12, 3,'D', '399606', '2016-09-20 20:44:35'),
    (13, 26,'A' ,'4456', '2016-09-20 20:49:45');

Requirement:

User has two search filters.

In first search filter he says, get me all the cars that have sourceId in (44,3) for certain date and time. Suppose ( e.createdOn > '2016-09-20 20:24:00' and e.createdOn < '2016-09-20 20:35:00' )

In second search filter he says, get me all the cars that have sourceId in (26,3,5) for another date and time. Suppose ( e.createdOn > '2016-09-20 20:36:00' and e.createdOn < '2016-09-20 20:49:00' )

Now I want to get all (carNumbers, carCode) which are available in both search results.

The query need to be fast since the real table contains over 300 million records.

so far below is the maximum i could go with the query (its not even producing valid results)

    select distinct e1.carNumber, e1.carCode from event e1  

    inner join (select e2.carNumber, e2.carCode from event e2 where e2.sourceId in (44,3) ) e3 on e1.carNumber= e3.carNumber 
    inner join (select e2.carNumber, e2.carCode from event e2  where e2.sourceId in (44,3)  ) e4 on e1.carCode= e4.carCode 

   inner join (select e2.carNumber, e2.carCode from event e2 where e2.sourceId in  (26,3,5) ) e5 on e1.carNumber= e5.carNumber 
    inner join (select e2.carNumber, e2.carCode from event e2  where e2.sourceId in  (26,3,5) ) e6 on e1.carCode= e6.carCode

I wonder if SQL is able to solve this question or should I use backend coding?

MySQL / MariaDB version in use:

mariadb-5.5.50

mysql-5.5.51

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
  2. Prefer Direct Join Over Joined Subquery (query line: 8): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  3. Prefer Direct Join Over Joined Subquery (query line: 21): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  4. Prefer Direct Join Over Joined Subquery (query line: 34): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  5. Prefer Direct Join Over Joined Subquery (query line: 47): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, we recommend to replace subqueries with JOIN clauses.
  6. Replace Join With Exists To Avoid Redundant Grouping (modified query below): When a joined table isn’t used anywhere other than in the WHERE clause, it's equivalent to an EXISTS subquery, which often performs better. In cases where the DISTINCT or GROUP BY clause contains only columns from the Primary key, they can be removed to further improve performance, as after this transformation, they are redundant.
Optimal indexes for this query:
ALTER TABLE `event` ADD INDEX `event_idx_carnumber_carcode_sourceid` (`carNumber`,`carCode`,`sourceId`);
The optimized query:
SELECT
        DISTINCT e1.carNumber,
        e1.carCode 
    FROM
        event e1 
    WHERE
        (
            (
                (
                    (
                        1 = 1
                    ) 
                    AND (
                        1 = 1
                    )
                ) 
                AND (
                    1 = 1
                )
            ) 
            AND (
                1 = 1
            )
        ) 
        AND (
            EXISTS (
                SELECT
                    1 
                FROM
                    event e3 
                INNER JOIN
                    event e4 
                INNER JOIN
                    event e5 
                INNER JOIN
                    event e6 
                WHERE
                    (
                        (
                            (
                                (
                                    (
                                        (
                                            (
                                                e1.carNumber = e3.carNumber
                                            ) 
                                            AND (
                                                e3.sourceId IN (
                                                    44, 3
                                                )
                                            )
                                        ) 
                                        AND (
                                            e1.carCode = e4.carCode
                                        )
                                    ) 
                                    AND (
                                        e4.sourceId IN (
                                            44, 3
                                        )
                                    )
                                ) 
                                AND (
                                    e1.carNumber = e5.carNumber
                                )
                            ) 
                            AND (
                                e5.sourceId IN (
                                    26, 3, 5
                                )
                            )
                        ) 
                        AND (
                            e1.carCode = e6.carCode
                        )
                    ) 
                    AND (
                        e6.sourceId IN (
                            26, 3, 5
                        )
                    )
            )
        )

Related Articles



* original question posted on StackOverflow here.