0
SELECT it.uid,it.Name,COALESCE(sum(i.Qty),0)-COALESCE(sum(s.Qty),0) as stock
FROM items it
left outer join sales_items s on it.uid=s.ItemID
left outer join inventory i on it.uid=i.uid
group by s.ItemID,i.uid,it.UID;

This is my query. This query take 59 seconds. How can I speed up this query?


my tables -> items

      UID            Item
      5089           JAM100GMXDFRUT
      5090           JAM200GMXDFRUT
      5091           JAM500GMXDFRUT
      5092           JAM800GMXDFRUT

tables -> sales_items

- slno        ItemID         Item              Qty
- 9           5089           JAM100GMXDFRUT    5
- 10          5090           JAM200GMXDFRUT    2
- 11          5091           JAM500GMXDFRUT    1

tables -> inventory

- slno         uid            Itemname          Qty
- 102          5089           JAM100GMXDFRUT    10
- 200          5091           JAM500GMXDFRUT    15
- 205          5092           JAM800GMXDFRUT    20

This table has more than 6000 rows

MarcinJuraszek
  • 124,003
  • 15
  • 196
  • 263

3 Answers3

0

Put indexes on the join columns

sales_items ItemID

inventory uid

paparazzo
  • 44,497
  • 23
  • 105
  • 176
0

If I was designing something like this I would have a query and schema that looks like this. Take note of my Idx1 indexes. I don't know about MySql but Sql Server will make use of those indexes for the sum function and this is called a covered query.

select  Item.ItemID, Item.Name, IsNull(sum(inv.Quantity), 0) - IsNull(sum(s.Quantity), 0) as stock
from    Item
Left Join Inventory inv
On      Item.ItemID = inv.ItemID
Left Join Sales s
On      Item.ItemID = s.ItemID
Group by Item.ItemID, Item.Name

Create Table dbo.Location
(
    LocationID int not null identity constraint LocationPK primary key,
    Name NVarChar(256) not null
)

Create Table dbo.Item
(
    ItemID int not null identity constraint ItemPK primary key,
    Name NVarChar(256) not null
);

Create Table dbo.Inventory
(
    InventoryID int not null identity constraint InventoryPK primary key,
    LocationID int not null constraint InventoryLocationFK references dbo.Location(LocationID),
    ItemID int not null constraint InventoryItemFK references dbo.Item(ItemID), 
    Quantity int not null,
    Constraint AK1 Unique(LocationID, ItemID)
);

Create Index InventoryIDX1 on dbo.Inventory(ItemID, Quantity);

Create Table dbo.Sales
(
    SaleID int not null identity constraint SalesPK primary key,
    ItemID int not null constraint SalesItemFK references dbo.Item(ItemID),
    Quantity int not null
);

Create Index SalesIDX1 on dbo.Sales(ItemID, Quantity);
Mike Burdick
  • 838
  • 6
  • 5
0

Aside from indexes on the tables to optimize joins, you are also doing a group by of the S.ItemID instead of just using the IT.UID since that is the join basis, and part of the main FROM table of the query... if that is an available index on the items table, use that and you are done. No need to reference the sales_items or inventory column names in the group by.

Now, that being said, another problem you will run into the way you have it is a Cartesian result if you have more than one record for the same "item id" you are summing from sales_items and inventory as I have extremely simplified an example for you via

CREATE TABLE items (
  `uid` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(5) NOT NULL,
  PRIMARY KEY (`uid`)
);

CREATE TABLE sales_items (
  `sid` int(11) NOT NULL AUTO_INCREMENT,
  `itemid` int(11),
  `qty` int(5) NOT NULL,
  PRIMARY KEY (`sid`),
  KEY byItemAndQty (`itemid`,`qty`)
);

CREATE TABLE inventory (
  `iid` int(11) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `qty` int(5) NOT NULL,
  PRIMARY KEY (`iid`),
  KEY byItemAndQty (`itemid`,`qty`)
);

insert into items ( uid, name ) values ( 1, 'test' );

INSERT INTO sales_items ( sid, itemid, qty ) VALUES ( 1, 1, 1 );
INSERT INTO sales_items ( sid, itemid, qty ) VALUES ( 2, 1, 2 );

INSERT INTO inventory ( iid, uid, qty ) VALUES ( 1, 1, 13 );
INSERT INTO inventory ( iid, uid, qty ) VALUES ( 2, 1, 35 );

Simple 1 item, Sales items 2 records for Item 1.. Qty of 1 and 2, total = 3 Inventory 2 records for Item 1.. Qty of 13 and 35, total 38

SELECT 
      it.uid, 
      it.Name, 
      sum(i.Qty) as iQty,
      sum(s.Qty) as sQty,
      COALESCE( sum(i.Qty),0) - COALESCE(sum(s.Qty),0) as stock 
   FROM 
      items it 
         left outer join sales_items s 
            on it.uid = s.ItemID 
         left outer join inventory i 
            on it.uid = i.uid 
   group by 
      it.uid

So, the result of the query you MIGHT EXPECT the Stock to be

uid  name   iQty   sQty   stock
1    test   48     3      45
but in reality becomes
1    test   96     6      90

NOW... PLEASE TAKE NOTE OF MY ASSUMPTION, but see similar sum()s or count()s from multiple tables like this. I am assuming the ITEMS table is one record per item.
The Sales_Items actually has more columns than provided (such as sales details and every date/sales count could be tracked) and MAY CONTAIN Multiple sales record quantities for a given item id (thus matching my sample).. Finally, the Inventory table similarly could have more than one record per same item, such as purchases of incoming inventory tracked by date and thus multiple records per a given item id (also matching my example).

To prevent this type of Cartesian result, and can also increase speed, I would do pre-aggregates per secondary table and join to that.

SELECT 
      it.uid, 
      it.Name, 
      i.iQty,
      s.sQty,
      COALESCE( i.iQty,0) - COALESCE(s.sQty,0) as stock 
   FROM 
      items it 
         left join ( select itemid, sum( qty ) as SQty
                        from sales_items
                        group by itemid ) s
            on it.uid = s.ItemID 
         left join ( select uid, sum( qty ) as IQty
                        from inventory
                        group by uid ) i
            on it.uid = i.uid 
   group by 
      it.uid

And you get the correct values of

uid  name   iQty   sQty   stock
1    test   48     3      45

Yes, this was only for a single item ID to prove the point, but still applies to as many inventory items as you have and respective sales/inventory records that may (or not) exist for certain items.

DRapp
  • 47,638
  • 12
  • 72
  • 142