Take the 2-minute tour ×

I have Two Tables One for Storing Products and Other for Storing Orders List.

                          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');                        

                       EmailId VARCHAR(50),
                       CSVProductIds  VARCHAR(50))

INSERT INTO OrderList(EmailId, CSVProductIds)                       
               VALUES('PersonA@domain.com', '2,4,1,5,7'),
                     ('PersonB@domain.com', '5,7,4'),
                     ('PersonC@domain.com', '2'),
                     ('PersonD@domain.com', '8,9'),
                     ('PersonE@domain.com', '4,5,9'),
                     ('PersonF@domain.com', '1,2,3'),
                     ('PersonG@domain.com', '9,10'),
                     ('PersonH@domain.com', '1,5');


ItemName             NoOfOrders
Product A                3
Product B                3 
Product C                1
Product D                3
Product E                4
Product F                0
Product G                2 
Product H                1 
Product I                3 
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.

  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

share|improve this question
I actually I misunderstood the question sorry any way..! –  Chella Dec 17 '12 at 5:10

3 Answers 3

Yes, using LIKE with a leading wildcard forces it to do a table-scan, i.e. reads every row in the table. The bigger the table, the slower it will be. For example, it can be hundreds or thousands of times slower!

This is one of several reasons why storing comma-separated lists in a VARCHAR, and expecting to access individual elements, is a bad design for a relational database.

See also my answer to Is storing a comma separated list in a database column really that bad?

What you should do instead is define your OrderList table to store one pair of email and productid. If the order consists of several products, you have to store multiple rows. But it means you can always find the single product you're looking for, count how many people bought that product, etc. and you can make those queries fast by defining indexes.

The rule against storing lists in a single column is called First Normal Form.

share|improve this answer
+1 for your great knowledge, sorry I can't hit upvote 10 times –  Mahdi Tahsildari Dec 17 '12 at 5:11
+1 Nice explanation given. –  Vignesh Gopalakrishnan Dec 17 '12 at 5:54

1.Use regex, but consider that it only changes your application code not the db code.
2.Yes it usually does affect performance that is shortly mentioned here, and here's a solution that might help.

share|improve this answer

You can find it in a single like below

  FROM OrderList PL 
share|improve this answer
Whats wrong in this query? Explain it before giving the down voting... –  Codesen Dec 28 '12 at 7:27

Your Answer


