Question 22078 in Mysql Question posted in
The official MySQL documentation can be found here.

Multiple Mysql Select Statements to combine empty results in a JOIN statement

I am trying to combine two select statements because my original query was taking way to long with LEFT JOIN

Original Query:

SELECT 
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS 
  cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
 LEFT JOIN tech_schedule b on a.RequestId = b.cute_id 
  ) 
 LEFT JOIN techs ts on b.tech_id = ts.id 
 LEFT JOIN sts c on ( 
  c.id = a.stsCustomer OR c.code = a.stsCustomer 
  ) 
 LEFT JOIN status_cute stat on (
  stat.RequestId = a.RequestId
  )" 
 . $swhere . $orderByQuery . $limitQuery;

With the above query I was able to get all the rows but it took forever with over 10k rows

Modified Query:

SELECT 
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
  JOIN tech_schedule b on a.RequestId = b.cute_id 
  ) 
 JOIN techs ts on b.tech_id = ts.id 
 JOIN sts c on ( 
  c.id = a.stsCustomer OR c.code = a.stsCustomer 
  ) 
 JOIN status_cute stat on (
  stat.RequestId = a.RequestId
  )"
 . $swhere . $orderByQuery . $limitQuery;

With this query im able to get results faster but its missing rows that are ether empty or null I guess, How can I include the missing rows with a second query. I’ve read most of the other questions on here on the topic but I ended up being more confused.

Wanted something like the following Query:

SELECT 
 DISTINCT a.RequestId,
 a.*,
 str_to_date(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate,
 str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate,
 name as Engineer,
 Cancelled  

FROM 
 (
  cert_request_cute a
  JOIN tech_schedule b on a.RequestId = b.cute_id
 )

WHERE b.cute_id, ts.id, a.stsCustomer, a.RequestId NOT IN (
 DISTINCT a.RequestId, 
  a.*, str_to_date(RequestedTestDate, '%d-%b-%Y') AS 
  cRequestedTestDate, 
  str_to_date(ActualTestDate, '%d-%b-%Y') AS cActualTestDate, 
  name as Engineer, Cancelled 
 FROM ( 
  cert_request_cute a 
  JOIN tech_schedule.b.cute_id b on a.RequestId.b.cute_id = 
  b.cute_id 
 ) 
 JOIN techs.ts.id ts on b.tech_id.ts.id = ts.id 
 JOIN sts c on ( 
  c.id = a.stsCustomer.astsCustomer OR c.code.c.code = 
  a.stsCustomer 
 ) 
 JOIN status_cute stat on (
  stat.RequestId.a.RequestId = a.RequestId
 ) 
)" 
 . $swhere . $orderByQuery . $limitQuery;

I know the above query is absolutely wrong but I just dont know or understand how to put it together. I just want to create a second NOT IN and join the two tables to get the missing rows. Also is there a way to optimize the query period of better speed and results?

cert_request_cute Table:

CREATE TABLE `cert_request_cute` (
  `RequestId` int(10) NOT NULL,
  `stsCustomer` char(8) DEFAULT NULL,
  `stsCustomerOtherCode` char(3) DEFAULT NULL,
  `stsCustomerOtherDescription` mediumtext DEFAULT NULL,
  `FirstName` mediumtext NOT NULL DEFAULT '',
  `LastName` mediumtext NOT NULL DEFAULT '',
  `Email` mediumtext NOT NULL DEFAULT '',
  `Phone` mediumtext NOT NULL DEFAULT '',
  `stsHandle` mediumtext NOT NULL,
  `CertificationRequest` mediumtext DEFAULT NULL,
  `CertificationRequestDetails` mediumtext NOT NULL,
  `RequestDescription` mediumtext DEFAULT NULL,
  `RequestedTestDate` mediumtext DEFAULT NULL,
  `RequestedBetaDate` mediumtext DEFAULT NULL,
  `RequestedGlobalReleaseDate` mediumtext DEFAULT NULL,
  `BetaSiteXP` mediumtext NOT NULL,
  `BetaSite7` mediumtext NOT NULL,
  `BetaSiteXP-2` mediumtext NOT NULL,
  `BetaSite7-2` mediumtext NOT NULL,
  `FirstBetaSiteChoice` char(7) DEFAULT NULL,
  `SecondBetaSiteChoice` char(7) DEFAULT NULL,
  `ThirdBetaSiteChoice` char(7) DEFAULT NULL,
  `ApplicationName` mediumtext DEFAULT NULL,
  `ApplicationVersion` mediumtext DEFAULT NULL,
  `SCutePlatform` mediumtext DEFAULT NULL,
  `ApplicationLocalServer` char(25) DEFAULT NULL,
  `ApplicationLocalServerOther` mediumtext DEFAULT NULL,
  `OSAPI` mediumtext DEFAULT NULL,
  `OSAPIOther` mediumtext DEFAULT NULL,
  `NewOSAPI` mediumtext DEFAULT NULL,
  `WANProtocol` mediumtext DEFAULT NULL,
  `WANProtocolOther` mediumtext DEFAULT NULL,
  `NewWANProtocol` mediumtext DEFAULT NULL,
  `Gateway` mediumtext DEFAULT NULL,
  `GatewayOther` mediumtext DEFAULT NULL,
  `SCuteLAN` mediumtext DEFAULT NULL,
  `LANProtocol` mediumtext DEFAULT NULL,
  `CommunicationCard` mediumtext DEFAULT NULL,
  `GatewayOS` mediumtext DEFAULT NULL,
  `RoutingProtocol` mediumtext DEFAULT NULL,
  `RegisteredAddressing` char(5) DEFAULT NULL,
  `AdditionalInformation` mediumtext DEFAULT NULL,
  `MainFirstName` mediumtext DEFAULT NULL,
  `MainLastName` mediumtext DEFAULT NULL,
  `NetworkConfiguratorFirstName` mediumtext NOT NULL,
  `NetworkConfiguratorLastName` mediumtext NOT NULL,
  `NetworkConfiguratorEmail` mediumtext NOT NULL,
  `NetworkConfiguratorPhone` mediumtext NOT NULL,
  `OperationsManagerFirstName` mediumtext NOT NULL,
  `OperationsManagerLastName` mediumtext NOT NULL,
  `OperationsManagerEmail` mediumtext NOT NULL,
  `OperationsManagerPhone` mediumtext NOT NULL,
  `TechSupportFirstName` mediumtext NOT NULL,
  `TechSupportlastName` mediumtext NOT NULL,
  `TechSupportEmail` mediumtext NOT NULL,
  `TechSupportPhone` mediumtext NOT NULL,
  `stsManagerFirstName` mediumtext NOT NULL,
  `stsManagerLastName` mediumtext NOT NULL,
  `stsManagerEmail` mediumtext NOT NULL,
  `stsManagerPhone` mediumtext NOT NULL,
  `SAccountManagerFirstName` mediumtext NOT NULL DEFAULT '',
  `SAccountManagerLastName` mediumtext NOT NULL DEFAULT '',
  `SAccountManagerEmail` mediumtext NOT NULL DEFAULT '',
  `SAccountManagerPhone` mediumtext NOT NULL DEFAULT '',
  `PrimaryContactFirstName` mediumtext NOT NULL,
  `PrimaryContactLastName` mediumtext NOT NULL,
  `PrimaryContactEmail` mediumtext NOT NULL,
  `PrimaryContactPhone` mediumtext NOT NULL,
  `CompanyAddress` mediumtext NOT NULL,
  `CompanyWebsite` mediumtext NOT NULL,
  `RequestedDate` mediumtext NOT NULL,
  `ActualTestDate` mediumtext DEFAULT NULL,
  `TestDays` char(2) DEFAULT NULL,
  `PPMNumber` mediumtext DEFAULT NULL,
  `Comments` mediumtext DEFAULT NULL,
  `stsUsers` mediumtext NOT NULL COMMENT 'user id of sts',
  `Cancelled` set('yes','no') NOT NULL DEFAULT 'no',
  `TestingType` mediumtext NOT NULL,
  `has_url` set('Yes','No') NOT NULL,
  `RequestType` mediumtext NOT NULL,
  `OperatingSystem` mediumtext NOT NULL,
  `complete` int(1) NOT NULL,
  `Price` mediumtext NOT NULL,
  `UpdatePrice` mediumtext NOT NULL DEFAULT '-',
  `BillingCompanyName` mediumtext NOT NULL,
  `BillingName` mediumtext NOT NULL,
  `BillingEmail` mediumtext NOT NULL,
  `BillingPhone` mediumtext NOT NULL,
  `ProductOwner` mediumtext NOT NULL COMMENT 'XS only',
  `CostCenter` mediumtext NOT NULL COMMENT 'XS only',
  `BudgetCode` mediumtext NOT NULL COMMENT 'XS only',
  `Reminded` set('yes','no') NOT NULL,
  `has_ssl` set('Yes','No') DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 PACK_KEYS=0;

Tech Schedule Table:

CREATE TABLE `tech_schedule` (
  `tech_id` int(10) NOT NULL,
  `b_date` mediumtext NOT NULL,
  `cute_id` int(10) NOT NULL,
  `cuss_id` int(10) NOT NULL,
  `cuss_sbd_id` int(11) NOT NULL,
  `book` set('yes','no') NOT NULL,
  `cupps_id` int(6) NOT NULL,
  `hardware_id` int(6) NOT NULL,
  `pos_id` int(3) NOT NULL,
  `realtime_id` int(10) NOT NULL,
  `sec_tech_id` int(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Techs Table:

CREATE TABLE `techs` (
  `id` int(11) NOT NULL,
  `Type` text DEFAULT NULL,
  `name` text NOT NULL,
  `email` text NOT NULL,
  `Phone` text DEFAULT NULL,
  `Title` text DEFAULT NULL,
  `active` enum('yes','no') NOT NULL DEFAULT 'yes'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Refactored Query: Page Load 200ms

SELECT
    a.*,
    STR_TO_DATE(RequestedTestDate, '%d-%b-%Y') AS cRequestedTestDate,
    STR_TO_DATE(ActualTestDate, '%d-%b-%Y') AS cActualTestDate,
    ts.NAME AS Engineer

FROM ( SELECT *
        FROM cert_request_cute 
    )a
LEFT JOIN tech_schedule b ON a.RequestId = b.cute_id
LEFT JOIN techs ts ON b.tech_id = ts.id
LEFT JOIN airlines c on c.code = a.AirlineCustomer
LEFT JOIN cert_status_cute stat on stat.RequestId = a.RequestId
$swhere
GROUP BY a.RequestId
$orderByQuery
$limitQuery";
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After making sure each called column had an index and refactoring my query a bit (code above) I reduced the Load Time from 9s to 300-400ms.


  2. 0

    What! No keys? Every table needs at least a PRIMARY KEY.

    First and foremost, change

    FROM ( SELECT *
        FROM cert_request_cute 
    )a

    to

    FROM cert_request_cute AS a

    These may be useful:

    a:  INDEX(RequestId,  stsCustomer)
b:  INDEX(cute_id,  tech_id)
c:  INDEX(code)
stat:  INDEX(RequestId)

    Don’t LEFT JOIN to airlines if you don’t need to — it slows things down.

    You seem to be allowing the user to enter only a single filter criteria. Don’t you want to allow multiple ones added together? It would take only a little more code to allow such.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search