Menu
Community /Pin to ProfileBookmark
@NogDogNov 22.2020 — #I've never used
If my brain was fresher, I might see a better way to do it purely in SQL, but at the moment it's not jumping out at me. :(@NogDogNov 22.2020 — #
If things are indexed as needed, that really shouldn't matter (most of the time). Make sure any columns used in the
Presumably
The result is that the DBMS does not have to search the entire table to find what it needs to know, but can instead do an optimized search against the index thingy (technical term ;) ).
SLOW SQL code..
Is there a way of simplifying this, in MYSQL Admin it took over 20 seconds to run, it’s been run inside a class so could I make it more generic but then filter results in PHP code?
“`
SELECT p.ID, p.ProductName, p.Price, p.SpecialPrice, p.ProductDetail, p.ProductImage, p.ProductStatus, p.ListingType, p.CategoryID, p.DateCreated, COUNT(i.InventoryID) As Qnty
FROM `products` p
LEFT JOIN `inventory` i ON i.ProductID = p.ID
WHERE i.OrderID IS NULL
group by p.ID
HAVING Qnty > 0 or p.ListingType = 0
Sign in
to post a comment10 Comments(s) ↴
0
@NogDogNov 22.2020 — #I've never used HAVINGp.ListingType = 0<i>
</i>while($row = $stmt->fetch()) {
if($row['ListingType'] != 0 and $row['Qnty'] == 0) {
continue;
}
// otherwise do whatever you do with each row
}
If my brain was fresher, I might see a better way to do it purely in SQL, but at the moment it's not jumping out at me. :(
0
@NogDogNov 22.2020 — #>@kiwis80#1625283 the inventory table has over 55,000 rows!
If things are indexed as needed, that really shouldn't matter (most of the time). Make sure any columns used in the
ONWHERE0
@NogDogNov 23.2020 — #Basically any column (or even group of columns) can be indexed, and each index can optionally be defined as unique. Your primary key for a table is a unique index, but any other column(s) can also be indexed, uniquely or not. Each index essentially creates another table-like structure that is optimized for finding/sorting values in that column. The reason you don't just index everything (normally?) is that every insert/update has to update those indexes, too, so you usually don't want to make it do more than it has to (though I don't think I've ever run into a situation where it mattered).Presumably
Product.IDinventoryProductIDinventory.ProductIDi.OrderIDwherep.ListingTypeThe result is that the DBMS does not have to search the entire table to find what it needs to know, but can instead do an optimized search against the index thingy (technical term ;) ).