How to Optimize SQL Queries for Tables with Comma-Separated Values?-Mysql Tutorial-php.cn

How to Optimize SQL Queries for Tables with Comma-Separated Values?

DDD

Release： 2024-12-18 00:24:11

Original

448 people have browsed it

How to Optimize SQL Queries for Tables with Comma-Separated Values?

Optimizing SQL Queries for Tables with Comma-Separated Column Values

When dealing with tables where a column contains comma-separated values, executing specific queries can be challenging. A common scenario is retrieving all related data when searching for specific values within the comma-separated column.

Issue:

You need to retrieve all usernames associated with a particular value in a comma-separated column. For instance, from the table structure below, you want to find all usernames when you search for "new1."

Tables:

CREATE TABLE tblA
(
id int NOT NULL AUTO_INCREMENT ,
user varchar(255),
category int(255),
PRIMARY KEY (id)
);

CREATE TABLE tblB
(
id int NOT NULL AUTO_INCREMENT ,
username varchar(255),
userid int(255),
PRIMARY KEY (id)
);

CREATE TABLE tblC
(
id int NOT NULL AUTO_INCREMENT ,
nname varchar(255),
userids varchar(255),
PRIMARY KEY (id)
);

INSERT INTO tblA (user, category ) VALUES
('1', '1'),
('1', '2'),
('1', '3'),
('1', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('2', '1'),
('3', '1'),
('2', '1'),
('4', '1'),
('4', '1'),
('2', '1');


INSERT INTO tblB (userid, username ) VALUES
('1', 'A'),
('2', 'B'),
('3', 'C'),
('4', 'D'),
('5', 'E');


INSERT INTO tblC (id, nname,userids ) VALUES
('1', 'new1','1,2'),
('2', 'new2','1,3'),
('3', 'new3','1,4'),
('4', 'new4','3,2'),
('5', 'new5','5,2');

Copy after login

Query:

select * where nname="new1" from  tblC
CROSS JOIN tblB
ON tblB.userid=(SELECT userids FROM substr(tblC.userids,','))

Copy after login

Limitations:

This query relies on the SUBSTR and FIND_IN_SET functions, which can be inefficient for large datasets. Moreover, it assumes only one comma-separated value per row, which may not always be the case.

Recommended Solution:

Database Normalization:

Normalize your database schema by creating a separate table for the comma-separated values. This eliminates the inefficiency of searching through strings and simplifies querying.

Example Schema:

CREATE TABLE tblC (
id int NOT NULL AUTO_INCREMENT ,
nname varchar(255),
PRIMARY KEY (id)
);

CREATE TABLE tblC_user (
c_id int NOT NULL,
userid int NOT NULL
);

INSERT INTO tblC (id, nname) VALUES
('1', 'new1'),
('2', 'new2'),
('3', 'new3'),
('4', 'new4'),
('5', 'new5');

INSERT INTO tblC_user (c_id, userid) VALUES
('1','1'),
('1','2'),
('2','1'),
('2','3'),
('3','1'),
('3','4'),
('4','3'),
('4','2'),
('5','5'),
('5','2');

Copy after login

Optimized Query:

select *
from tblC c
join tblC_user cu on(c.id = cu.c_id)
join tblB b on (b.userid = cu.userid)
where c.nname="new1"

Copy after login

Benefits:

Improved performance due to efficient join operations
Reduced complexity by eliminating string manipulation functions
Maintainability and flexibility in accommodating future changes to the data structure

The above is the detailed content of How to Optimize SQL Queries for Tables with Comma-Separated Values?. For more information, please follow other related articles on the PHP Chinese website!