Query to bring count from comma seperated Value

Posted by Mugil on Stack Overflow See other posts from Stack Overflow or by Mugil
Published on 2012-12-17T04:44:25Z Indexed on 2012/12/17 5:03 UTC
Read the original article Hit count: 126

Filed under:

mysql

|

sql

|

query

|

mysqli

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

Developer IT