2
\$\begingroup\$

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

tablestr:

enter image description here

restbooking:

enter image description here

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 <= 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 <= "2015-06-12 19:45:00" AND t.bookingend_time >= "2015-06-12 22:15:00") OR 
(t.bookingend_time >= "2015-06-12 19:45:00" AND t.bookingend_time <= "2015-06-12 22:15:00") OR
(t.bookingstart_time >= "2015-06-12 19:45:00" AND t.bookingstart_time <= "2015-06-12 22:15:00") OR 
(t.bookingstart_time >= "2015-06-12 19:45:00" AND t.bookingend_time <= "2015-06-12 22:15:00")) ) AND
`res_id` = '21' AND
`area_id` = '28' AND 
`wait_table` = 'no' AND
`availability` = 'yes';

Result Set:

enter image description here

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?

\$\endgroup\$
5
  • 3
    \$\begingroup\$ Welcome to Code Review! This could be an interesting question, but as it stands, it is an incomplete question. Please describe what the intended purpose of the query is, so that we can understand what we are optimizing. If you could describe the schema, including what keys and indexes there are, and also edit the title, that would be appreciated. \$\endgroup\$ Jun 12, 2015 at 17:52
  • 1
    \$\begingroup\$ @200_success i explained my query. \$\endgroup\$
    – Arockiaraj
    Jun 12, 2015 at 18:14
  • \$\begingroup\$ do you have a result set that we could see? \$\endgroup\$
    – Malachi
    Jun 12, 2015 at 18:22
  • \$\begingroup\$ You've already mentioned the desire for optimization, so it doesn't need to be in the title. \$\endgroup\$
    – Jamal
    Jun 12, 2015 at 18:25
  • \$\begingroup\$ @Lyle'sMug i have added resultset \$\endgroup\$
    – Arockiaraj
    Jun 12, 2015 at 18:30

1 Answer 1

1
\$\begingroup\$

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 <= 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 <= "2015-06-12 19:45:00" AND t.bookingend_time >= "2015-06-12 22:15:00") 
                        OR (t.bookingend_time >= "2015-06-12 19:45:00" AND t.bookingend_time <= "2015-06-12 22:15:00") 
                        OR (t.bookingstart_time >= "2015-06-12 19:45:00" AND t.bookingstart_time <= "2015-06-12 22:15:00") 
                        OR (t.bookingstart_time >= "2015-06-12 19:45:00" AND t.bookingend_time <= "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.

\$\endgroup\$
1
  • \$\begingroup\$ I want to optimize query. how can i do \$\endgroup\$
    – Arockiaraj
    Jun 12, 2015 at 18:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.