Restaurant booking subquery I have two database table name called "tablestr table" and "restbookingtable":

tablestr:



restbooking:



I want to retrieve bookingsection_id from the booking table with a few conditiona and need to remove from tablestr. I have a query for this, but I could achieve it by using subquery, which executes very slowly. 

SELECT `str_id` FROM (`rest_tablestr`) WHERE str_id NOT IN (
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.bookingsection_id, ",", n.n), ",", -1) value FROM rest_restaurantbooking t 
CROSS JOIN (
SELECT a.N + b.N * 10 + 1 n FROM (
SELECT 0 AS N
UNION ALL
SELECT 1
UNION ALL
SELECT 2
UNION ALL
SELECT 3
UNION ALL
SELECT 4
UNION ALL
SELECT 5 
UNION ALL 
SELECT 6 
UNION ALL
SELECT 7 
UNION ALL 
SELECT 8 
UNION ALL 
SELECT 9) a , (
SELECT 0 AS N
UNION ALL 
SELECT 1 
UNION ALL 
SELECT 2 
UNION ALL 
SELECT 3 
UNION ALL 
SELECT 4 
UNION ALL 
SELECT 5 
UNION ALL 
SELECT 6 
UNION ALL 
SELECT 7 
UNION ALL 
SELECT 8 
UNION ALL SELECT 9) 
b ORDER BY n ) n 
WHERE n.n &lt;= 1 + (LENGTH(t.bookingsection_id) -
LENGTH(REPLACE(t.bookingsection_id, ",", ""))) AND
t.res_id = 21 AND
t.booking_status not in ("cancelled","departed","noshow") AND
((t.bookingstart_time &lt;= "2015-06-12 19:45:00" AND t.bookingend_time &gt;= "2015-06-12 22:15:00") OR 
(t.bookingend_time &gt;= "2015-06-12 19:45:00" AND t.bookingend_time &lt;= "2015-06-12 22:15:00") OR
(t.bookingstart_time &gt;= "2015-06-12 19:45:00" AND t.bookingstart_time &lt;= "2015-06-12 22:15:00") OR 
(t.bookingstart_time &gt;= "2015-06-12 19:45:00" AND t.bookingend_time &lt;= "2015-06-12 22:15:00")) ) AND
`res_id` = '21' AND
`area_id` = '28' AND 
`wait_table` = 'no' AND
`availability` = 'yes';


Result Set:



When I execute the subquery and main query, the separate execution time is just 0.1 sec. But while executing both, execution time is takes more than 5 to 7 seconds. How can I optimize this query?
You could start with Formatting your code so that it is readable, I got this far

SELECT `str_id` 
    FROM (`rest_tablestr`) 
    WHERE str_id NOT IN (
        SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(t.bookingsection_id, ",", n.n), ",", -1) value 
            FROM rest_restaurantbooking t 
                CROSS JOIN (
                    SELECT a.N + b.N * 10 + 1 n 
                        FROM (
                            SELECT 0 AS N
                            UNION ALL
                            SELECT 1
                            UNION ALL
                            SELECT 2
                            UNION ALL
                            SELECT 3
                            UNION ALL
                            SELECT 4
                            UNION ALL
                            SELECT 5 
                            UNION ALL 
                            SELECT 6 
                            UNION ALL
                            SELECT 7 
                            UNION ALL 
                            SELECT 8 
                            UNION ALL 
                            SELECT 9) a 
                            , 
                        (
                            SELECT 0 AS N
                            UNION ALL 
                            SELECT 1 
                            UNION ALL 
                            SELECT 2 
                            UNION ALL 
                            SELECT 3 
                            UNION ALL 
                            SELECT 4 
                            UNION ALL 
                            SELECT 5 
                            UNION ALL 
                            SELECT 6 
                            UNION ALL 
                            SELECT 7 
                            UNION ALL 
                            SELECT 8 
                            UNION ALL SELECT 9) b 
                        ORDER BY n ) n 
                WHERE n.n &lt;= 1 + (LENGTH(t.bookingsection_id) - LENGTH(REPLACE(t.bookingsection_id, ",", ""))) 
                    AND t.res_id = 21 
                    AND t.booking_status not in ("cancelled","departed","noshow") 
                    AND ((t.bookingstart_time &lt;= "2015-06-12 19:45:00" AND t.bookingend_time &gt;= "2015-06-12 22:15:00") 
                        OR (t.bookingend_time &gt;= "2015-06-12 19:45:00" AND t.bookingend_time &lt;= "2015-06-12 22:15:00") 
                        OR (t.bookingstart_time &gt;= "2015-06-12 19:45:00" AND t.bookingstart_time &lt;= "2015-06-12 22:15:00") 
                        OR (t.bookingstart_time &gt;= "2015-06-12 19:45:00" AND t.bookingend_time &lt;= "2015-06-12 22:15:00")) 
    ) AND `res_id` = '21' 
    AND `area_id` = '28' 
    AND  `wait_table` = 'no' 
    AND `availability` = 'yes';


Indentation in SQL is important so that you know where you are when you are debugging the code.