Description:
Just to set the context a little, I'm trying to use queries with mysql that use Late row lookup as shown in this article
https://explainextended.com/2009/10/23/mysql-order-by-limit-performance-late-row-lookups/
but that's a story for another day but the idea is that you do a key search on the table and then join it onto the whole table to force a late row lookup and the problem is coming from my LINQ queries when joined together.
--Note--
The actual query selects around 15 columns, I've just shortened it to this example, it has an effect on the search as the dataset grows in size and it shouldn't be selecting all of the fields but i suspect there's an error in my join.
Any help is much appreciated.
How to repeat:
Select one col1 (the key) from table with a where, Skip, Take and Order by then inner join this onto the same table with only an order by but with a selection of different fields e.g. col1, col2, col3. After inner joining the two queries the query the first select is lost and looks like select col1, col2, col3 from select col1, col2, col3.
more detailed example
- Key search query --
Calling Code
IQueryable<int> keySearch = _defaultQueryFactory.Load(ContextEnums.ClientContext, MapEntityToDTO(), whereStatement, clientID).OrderBy(orderBy).Skip(startRow).Take(pageSize).Select(x => x.ID);
Resulting Query
SELECT
`Extent1`.`Sys_InvoiceID`
FROM `tblinvoice` AS `Extent1`
WHERE 3 = `Extent1`.`FK_StatusID`
ORDER BY
`Extent1`.`InvoiceDate` ASC LIMIT 0,430
-- Full Table Search --
Calling Code
IQueryable<InvoiceDTOModel> tableSearch = _defaultQueryFactory.Load(ContextEnums.ClientContext, MapEntityToDTO(), null, clientID, true).OrderBy(orderBy);
Resulting Query
SELECT
`Extent1`.`ID`,
`Extent1`.`C1`,
`Extent1`.`C2`,
`Extent1`.`C3`,
`Extent1`.`C4`,
`Extent1`.`C5`,
`Extent1`.`C6`,
`Extent2`.`SID`,
`Extent2`.`S1,
`Extent2`.`S2`,
`Extent2`.`S3`,
`Extent3`.`EID`,
`Extent3`.`E1`,
`Extent4`.`DID`,
`Extent4`.`D1`,
`Extent4`.`D2`,
`Extent4`.`D3`,
`Extent4`.`D4`,
`Extent4`.`D5`
FROM `tbl1` AS `Extent1` INNER JOIN `tbl2` AS `Extent2` ON `Extent1`.`SID` = `Extent2`.`SID` INNER JOIN `tbl3` AS `Extent3` ON `Extent1`.`EID` = `Extent3`.`EID` LEFT OUTER JOIN `tbl4` AS `Extent4` ON `Extent1`.`ID` = `Extent4`.`DID`
ORDER BY
`Extent1`.`C4` ASC
-- Joining the Two Together --
Calling Code
keySearch.Join(tableSearch, key => key, table => table.ID, (key, table) => table).OrderBy(orderBy).ToListAsync();
Resulting Query
SELECT
`Join3`.`ID`,
`Join3`.`C1`,
`Join3`.`C1`,
`Join3`.`C1`,
`Join3`.`C1`,
`Join3`.`C1`,
`Join3`.`C1`,
`Join3`.`SID`,
`Join3`.`S1,
`Join3`.`S2`,
`Join3`.`S3`,
`Join3`.`EID`,
`Join3`.`E1`,
`Join3`.`DID`,
`Join3`.`D1`,
`Join3`.`D2`,
`Join3`.`D3`,
`Join3`.`D4`,
`Join3`.`D5`
FROM (
`Extent1`.`ID`,
`Extent1`.`C1`,
`Extent1`.`C2`,
`Extent1`.`C3`,
`Extent1`.`C4`,
`Extent1`.`C5`,
`Extent1`.`C6`
FROM `tblinvoice` AS `Extent1`
WHERE 3 = `Extent1`.`EID`
ORDER BY
`Extent1`.`C4` ASC LIMIT 0,430) AS `Limit1` INNER JOIN (SELECT
`Extent1`.`ID`,
`Extent1`.`C1`,
`Extent1`.`C2`,
`Extent1`.`C3`,
`Extent1`.`C4`,
`Extent1`.`C5`,
`Extent1`.`C6`,
`Extent2`.`SID`,
`Extent2`.`S1,
`Extent2`.`S2`,
`Extent2`.`S3`,
`Extent3`.`EID`,
`Extent3`.`E1`,
`Extent4`.`DID`,
`Extent4`.`D1`,
`Extent4`.`D2`,
`Extent4`.`D3`,
`Extent4`.`D4`,
`Extent4`.`D5`
FROM `tbl1` AS `Extent2` INNER JOIN `tbl2` AS `Extent3` ON `Extent2`.`SID` = `Extent3`.`SID` INNER JOIN `tblstatus` AS `Extent4` ON `Extent2`.`EID` = `Extent4`.`EID` LEFT OUTER JOIN `tbl3` AS `Extent5` ON `Extent2`.`ID` = `Extent5`.`DID`) AS `Join3` ON `Limit1`.`ID` = `Join3`.`ID`
ORDER BY
`Join3`.`C4` ASC
Basically the inner select brings back
FROM (
`Extent1`.`ID`,
`Extent1`.`C1`,
`Extent1`.`C2`,
`Extent1`.`C3`,
`Extent1`.`C4`,
`Extent1`.`C5`,
`Extent1`.`C6`
FROM `tblinvoice` AS `Extent1`
WHERE 3 = `Extent1`.`EID`
ORDER BY
`Extent1`.`C4` ASC LIMIT 0,430) AS `Limit1`
Instead of
FROM (
`Extent1`.`ID`,
FROM `tblinvoice` AS `Extent1`
WHERE 3 = `Extent1`.`EID`
ORDER BY
`Extent1`.`C4` ASC LIMIT 0,430) AS `Limit1`
Suggested fix:
being able to have an inner key search that only searches on the selected columns.
