Our view is only returning a 1-5 rows but it is very slow. We added SQL_CACHE but it did not help. We added indices on each of the tables for SaleiD. The query is taking 3-5 seconds. I feel like it should be .5 seconds since it is only a few rows. What do you think? Any suggestions?
We also increased the buffer size:
myisam_sort_buffer_size = 64M
And added this, not sure what it is, but we heard it helped:
query_cache_type = 2 #ondemand use sql_cache after SELECT
Our SQL Query
SELECT * FROM plazsales_live.salelineview where SaleiD = 56528;
The View
CREATE
ALGORITHM = UNDEFINED
DEFINER = `dbuser`@`%`
SQL SECURITY DEFINER
VIEW `plazsales_live`.`salelineview` AS
SELECT DISTINCT SQL_CACHE
`plazsales_live`.`saleline`.`SaleLineID` AS `SaleLineID`,
`plazsales_live`.`saleline`.`SaleID` AS `SaleID`,
`plazsales_live`.`saleline`.`Tax` AS `tax`,
`plazsales_live`.`saleline`.`Price` AS `SaleLinePrice`,
`plazsales_live`.`saleline`.`Description` AS `SaleLineDescription`,
`plazsales_live`.`saleline`.`Qty` AS `qty`,
`plazsales_live`.`saleline`.`ReturnSaleLineID` AS `returnSaleLineID`,
`plazsales_live`.`saleline`.`ReturnQty` AS `returnQty`,
`plazsales_live`.`saleline`.`TicketID` AS `TicketID`,
`plazsales_live`.`saleline`.`discountRate` AS `discountRate`,
`plazsales_live`.`saleline`.`AssemblyID` AS `AssemblyIDFromFP`,
`plazsales_live`.`products`.`ProductIndex` AS `ProductIndex`,
`plazsales_live`.`products`.`ProductID` AS `ProductID`,
`plazsales_live`.`products`.`Name` AS `Name`,
`plazsales_live`.`products`.`Type` AS `Type`,
`plazsales_live`.`products`.`Description` AS `Description`,
`plazsales_live`.`products`.`PartNumber` AS `PartNumber`,
`plazsales_live`.`products`.`UPC` AS `UPC`,
`plazsales_live`.`products`.`Price` AS `Price`,
`plazsales_live`.`products`.`MSRP` AS `MSRP`,
`plazsales_live`.`products`.`ReorderAt` AS `ReorderAt`,
`plazsales_live`.`products`.`DepartmentID` AS `DepartmentID`,
`plazsales_live`.`products`.`DateAdded` AS `DateAdded`,
`plazsales_live`.`products`.`Status` AS `Status`,
`plazsales_live`.`products`.`Version` AS `Version`,
`plazsales_live`.`products`.`DiscountRate` AS `DiscountR`,
`plazsales_live`.`products`.`QtyOnHold` AS `QtyOnHold`,
`plazsales_live`.`products`.`qtyonOrder` AS `qtyonOrder`,
`plazsales_live`.`products`.`DefaultTaxAccount` AS `DefaultTaxAccount`,
`plazsales_live`.`assemblies`.`AssemblyID` AS `AssemblyID`,
`plazsales_live`.`assemblies`.`AutoConstruct` AS `AutoConstruct`,
`plazsales_live`.`assemblies`.`Deconstructable` AS `Deconstructable`,
`plazsales_live`.`assemblies`.`ShowItemsOnInvoice` AS `ShowItemsOnInvoice`,
`plazsales_live`.`assemblies`.`ShowPricesOnInvoice` AS `ShowPricesOnInvoice`,
`plazsales_live`.`assemblies`.`ShowItemsOnReceipt` AS `ShowItemsOnReceipt`,
`plazsales_live`.`assemblies`.`ShowPricesOnReceipt` AS `ShowPricesOnReceipt`
FROM
((`plazsales_live`.`saleline`
LEFT JOIN `plazsales_live`.`products` ON (((`plazsales_live`.`saleline`.`ProductID` = `plazsales_live`.`products`.`ProductID`)
AND (`plazsales_live`.`saleline`.`version` = `plazsales_live`.`products`.`Version`))))
LEFT JOIN `plazsales_live`.`assemblies` ON ((`plazsales_live`.`saleline`.`AssemblyID` = `plazsales_live`.`assemblies`.`AssemblyID`)))