CODEWITHSUNDEEP
mysqlsql
ArrayOutOfBound
ArrayOutOfBound

Reputation: 2628

Query to bring count from comma separated Value

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


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                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.

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

Upvotes: 1

Views: 222

Answers (3)

Codesen
Codesen

Reputation: 7822

You can find it in a single like below

SELECT COUNT(PL.EmailId)
  FROM OrderList PL 
 WHERE FIND_IN_SET(2, CSVProductIds)

Upvotes: -1

Bill Karwin
Bill Karwin

Reputation: 562310

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.

Upvotes: 6

Mahdi Tahsildari
Mahdi Tahsildari

Reputation: 13582

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.

Upvotes: 0

Related Questions