MySQL optimize multiple join query

Question

I have this query which runs forever, its already running for 30 minutes in MySQL Workbench and counting... Is it possible to optimize it?

SELECT `p`.`id`, `ev`.`id`
FROM `participants` as `p`
JOIN `tbl_student` as `tst` on `p`.`First Name` = `tst`.`First Name` AND `p`.`Last Name` = `tst`.`Last Name`
JOIN `tbl_student_session` as `tsts` on `tst`.`id` = `tsts`.`studentId`
JOIN `tbl_courses` as `tc` on `tc`.`sessions` LIKE CONCAT('%',`tsts`.`sessionId`,'%') 
JOIN `events` as `ev` on `tc`.`description` = `ev`.`body`

Tables:

participants: id, firstname, lastname
tbl_student: id, firstname, lastname 
tbl_student_session: sessionId, studentId
tbl_courses: id, sessions, description
events: id, body

Where tbl_courses.sessions containts the sessions in this format: session1, session2, .... ,sessionn etc

UPDATE

I set limit to 10 rows and here is the execution plan:

Any query optimization question requires the result of EXPLAIN .. statement for the query. Moreover, LIKE %% in your query is definitely not going to be able to use any index whatsoever. — Madhur Bhaiya
– Madhur Bhaiya, Commented Aug 29, 2019 at 6:54
Also there is clear evidence that you are using comma-separated values in a single field (tc.sessions), instead of having an actual relationship table that has one ID per row. This will never be fast until you fix your database design mistakes. — Tomalak
– Tomalak, Commented Aug 29, 2019 at 7:06
Sorry, there's no way to optimize the query unless the tables are designed in a way that can do the join lookups with indexes. Your insistence on the denormalized design is what's causing the poor performance. — Bill Karwin
– Bill Karwin, Commented Aug 29, 2019 at 7:20
Also it can lead to false results. Imagine your tc.sessions value is 11, and the tsts.sessionId is 123, 11411, 456. Probably not what you want, isn't it? — fancyPants
– fancyPants, Commented Aug 29, 2019 at 7:33

Rick James · Accepted Answer · 2019-08-29 16:54:56Z

1

Inefficient: ON tc.sessions LIKE CONCAT('%', tsts.sessionId, '%'). Are you trying to put an array into a cell? Don't. Instead, have rows in another table.

But, assuming your current structure, here are some indexes to help with performance:

participants:  INDEX(`Last Name`, `First Name`)
student:  INDEX(`Last Name`, `First Name`)
student_session:  INDEX(studentId)
courses:  INDEX(description)
courses:  INDEX(sessions)
events:  INDEX(body)

(You will eventually see why I removed "tbl_".)

answered Aug 29, 2019 at 16:54

Rick James

144k15 gold badges144 silver badges255 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rick James · Accepted Answer · 2019-08-29 16:55:27Z

0

try this query. if there are any improvements. I believe your session table is quite huge.

SELECT `p`.`id`, `ev`.`id`
FROM `participants` as `p`
JOIN `tbl_student` as `tst` on `p`.`First Name` = `tst`.`First Name` AND `p`.`Last Name` = `tst`.`Last Name`
JOIN `tbl_student_session` as `tsts` on `tst`.`id` = `tsts`.`studentId`
JOIN `events` as `ev` on `tc`.`description` = `ev`.`body`
where exists (
   select 1 from `tbl_courses`
where `sessions` LIKE CONCAT('%',`tsts`.`sessionId`,'%'))

edited Aug 29, 2019 at 16:55

Rick James

144k15 gold badges144 silver badges255 bronze badges

answered Aug 29, 2019 at 7:32

Ed Bangga

13k4 gold badges18 silver badges31 bronze badges

1 Comment

netdev Over a year ago

tbl_student_session has ~70000 entries

Collectives™ on Stack Overflow

MySQL optimize multiple join query

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related