-1

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";
demo7up
  • 530
  • 5
  • 27
  • 2
    Edit 1st question, add tables aliases to ALL columns names. Do you really need in ALL joins to be LEFT? – Akina Nov 25 '22 at 09:01
  • I want to remove all left joins like in the second query. I just dont know how to create a not in for the second query to get all the rows. – demo7up Nov 25 '22 at 09:03
  • 1
    @demo7up thats.. a lot of column and the table `cert_request_cute` looks very denormalized. not to mention the amount of `mediumtext` column.. if a table does not fit in memory, it might need to be directly accessed on disk. which is bad most of the time given disk is slower than ram. not to mention indexes. i see several columns are used in the join but not indexed, it might be one of the culprit. also, it might be helpful if you can run `explain` on the sql query to see what the dbms actually face. – Bagus Tesa Nov 25 '22 at 09:38
  • 1
    also, there is a [DBA Stackexchange](https://dba.stackexchange.com/questions/75091/why-are-simple-selects-on-innodb-100x-slower-than-on-myisam) that deals specifically with query stuffs. – Bagus Tesa Nov 25 '22 at 09:41
  • @BagusTesa I did not know about the DBA its yeah its messy its an old db ive been working on. – demo7up Nov 25 '22 at 09:43
  • explain screenshot https://imgur.com/a/4GVY8SI – demo7up Nov 25 '22 at 09:47
  • 1
    `SELECT .. , name as Engineer, ..` - what table this `name` column is taken from? – Akina Nov 25 '22 at 09:53
  • @akina Techs table added. – demo7up Nov 25 '22 at 09:55
  • 1
    You have 3 join branches in your query (`a->b->ts`, `a->c` and `a->stat`). But you get the output columns from1st branch only. So two other branches are excess and must be removed. Additionally `DISTUNCT` should become excess. – Akina Nov 25 '22 at 09:57
  • @BagusTesa while going over your comment I changed medium text to text and indexed all the tables referenced it went from a Load:600+s to Load: 2.58s I love you! – demo7up Nov 25 '22 at 10:05
  • 1
    @demo7up O.o ehh, if i can be of help thats nice. glad it worked out, though, [`Text` is smaller than `MediumText` in capacity](https://stackoverflow.com/a/13932834). so, i hope you take that into accounts. though i highly doubt someone would have a name longer than 50k+ characters. that being said you have to watch the `description` column. – Bagus Tesa Nov 25 '22 at 10:10
  • @BagusTesa do you have a suggestion for the desc column as it can get rather large and please post your remarks as an answer so i can accept it., – demo7up Nov 25 '22 at 10:12
  • `LEFT` says to provide `NULLs` when righthand rows are missing. – Rick James Nov 26 '22 at 05:18
  • `$swhere . $orderByQuery . $limitQuery;` -- We really need to see in order to talk about performance. – Rick James Nov 26 '22 at 05:19
  • Are those `...Dates` just dates?? If so, use `DATE`, _not_ `MEDIUMTEXT`. – Rick James Nov 26 '22 at 05:21
  • Ask 1 specific question. Please use minimal mnemonic table aliases. crc & ts, not a & b. Debug questions require a [mre]. This isn't minimal & it can't be cut & pasted & run. (No need for all those columns.) Please clarify via edits, not comments. Please delete & flag obsolete comments. "something like" is not helpful. When asking about performance, then like everything asked about, research & reflect research. [research effort](https://meta.stackoverflow.com/q/261592/3404097) [“help me"](https://meta.stackoverflow.com/q/284236/3404097) [ask] [Help] This is all basic debugging & communication. – philipxy Nov 28 '22 at 04:09

2 Answers2

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.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • I created the keys and refactored the query to : posted above – demo7up Nov 26 '22 at 05:27
  • Seemed like -> c.id = a.stsCustomer.astsCustomer OR c.code.c.code = a.stsCustomer caused an insane amount of recursion – demo7up Nov 26 '22 at 05:31
  • @demo7up - `OR` does not involve "recursion". (Perhaps a terminology problem?) – Rick James Nov 26 '22 at 05:40
  • maybe, but the OR statement in the original Query was checking against a.RequestId & a.* -> a is a table with 89 columns and about 20k rows with the OR statement the page load was at 9s without it 2-3s then i started trying more statements and eliminating things one by one that weren't required and its down to 200-400ms, I inherited this mess lol. its was a mysql 5 db which ive been working slowly to convert to mysql 7 that is also the reason for the messy column types – demo7up Nov 26 '22 at 06:10
  • @demo7up - Please edit your Question. I don't see any reference to `sts`. And `WHERE b.cute_id, ts.id, a.stsCustomer,` is syntactically wrong -- `WHERE` respects a boolean expression, not a comma-list of columns. – Rick James Nov 26 '22 at 15:42
  • I literally said right after that code block "I know the above query is absolutely wrong but I just dont know or understand how to put it together." – demo7up Nov 27 '22 at 02:15
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.

demo7up
  • 530
  • 5
  • 27