Query to bring count from comma seperated Value
- by Mugil
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
