How can i optimize this query on a big database tables having million rows

Question

SELECT l.player_id,game_group_type,game_type,no_of_card,room_group_type,device_type 
FROM rummy_player_login_master k 
JOIN 
(

        SELECT no_of_card,game_group_type,game_type,room_group_type,player_id,login_id 
        FROM rummy_game_type_master i 
        JOIN 
        (
            SELECT  game_type_id,  room_group_type,player_id,login_id 
            FROM rummy_room_group_master g 
            JOIN 
            ( 
                SELECT room_group_id ,player_id,login_id 
                FROM rummy_room_domain_group_mapping e  
                JOIN 
                (
                    SELECT  common_id,player_id,login_id 
                    FROM rummy_room_master c 
                    JOIN 
                    (
                        SELECT room_id ,player_id,login_id 
                        FROM rummy_room_session_master a 
                        JOIN  
                        (
                            SELECT MAX(session_id) AS session_id,player_id,login_id 
                            FROM rummy_room_session_player_mapping 
                            GROUP BY player_id 
                        ) b 
                        ON a.session_id = b.session_id 
                    ) d 
                    ON c.room_id = d.room_id
                ) f 
                ON e.common_id =f.common_id
            ) h 
            ON g.room_group_id = h.room_group_id
        ) j 
        ON i.game_type_id = j.game_type_id

) l 
ON k.login_id = l.login_id 
GROUP BY l.player_id

Answer 1

Assuming I figured out which columns go to which tables correctly, this query should be equivalent (or even better, given the abuse of GROUP BY in your original).

SELECT 
rrspm.player_id,
rgtm.game_group_type,
rgtm.game_type,
rgtm.no_of_card,
rrgm.room_group_type,
rplm.device_type 
FROM rummy_room_session_player_mapping rrspm
LEFT OUTER JOIN rummy_room_session_player_mapping later_rrspm
ON later_rrspm.player_id = later_rrspm.player_id
AND later_rrspm.session_id > rrspm.session_id
INNER JOIN rummy_player_login_master rplm 
ON rrspm.login_id = rplm.login_id
INNER JOIN rummy_room_session_master rrsm 
ON rrsm.session_id = rrspm.session_id
INNER JOIN rummy_room_master rrm 
ON rrm.room_id = rrsm.room_id
INNER JOIN rummy_room_domain_group_mapping rrdgm
ON rrdgm.common_id = rrm.common_id
INNER JOIN rummy_room_group_master rrgm 
ON rrgm.room_group_id = rrdgm.room_group_id
INNER JOIN rummy_game_type_master rgtm
ON rgtm.game_type_id = rrgm.game_type_id
WHERE later_rrspm.session_id IS NULL

If you still need to improve performance, try the following:

ALTER TABLE rummy_room_session_player_mapping
ADD INDEX `player_session` (`player_id`,`session_id`)

The syntax in your original query looks like you had the same difficulty I had when I started working with databases, in that you are not correctly viewing your data as set based.

If my query works, I suggest you study what I did and understand how you don't need to do all those nested selects to get what you want. The database itself will put things in whatever order it needs to, you just need to tell it on what columns to join the appropriate tables together.