I have Two Tables One for Storing Products
and Other for Storing Orders List.
CREATE TABLE ProductsList(ProductId INT NOT NULL PRIMARY KEY,
ProductName VARCHAR(50))
INSERT INTO ProductsList(ProductId, ProductName)
VALUES(1,'Product A'),
(2,'Product B'),
(3,'Product C'),
(4,'Product D'),
(5,'Product E'),
(6,'Product F'),
(7,'Product G'),
(8,'Product H'),
(9,'Product I'),
(10,'Product J');
CREATE TABLE OrderList(OrderId INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
EmailId VARCHAR(50),
CSVProductIds VARCHAR(50))
SELECT * FROM OrderList
INSERT INTO OrderList(EmailId, CSVProductIds)
VALUES('
[email protected]', '2,4,1,5,7'),
('
[email protected]', '5,7,4'),
('
[email protected]', '2'),
('
[email protected]', '8,9'),
('
[email protected]', '4,5,9'),
('
[email protected]', '1,2,3'),
('
[email protected]', '9,10'),
('
[email protected]', '1,5');
Output
ItemName NoOfOrders
Product A 4
Product B 3
Product C 1
Product D 3
Product E 4
Product F 0
Product G 2
Product H 1
Product I 2
Product J 1
The Order List Stores the ItemsId as Comma separated value for every customer who places order.Like this i am having more than 40k Records in my dB table
Now I am assigned with a task of creating report in which I should display Items
and No of People ordered Items as Shown Below
I Used Query as below in my PHP to bring the Orders One By One
and storing in array.
SELECT COUNT(PL.EmailId)
FROM OrderList PL
WHERE CSVProductIds LIKE '2' OR
CSVProductIds LIKE '%,2,%' OR
CSVProductIds LIKE '%,2' OR
CSVProductIds LIKE '2,%';
1.Is it possible to get the same out put by using Single Query
2.Does using a like in mysql query slows down the dB when the table has more no of records i.e 40k rows
