mysql search in multiple joined tables (7 tables with 6 joins)

Question

I'm trying to search in multiple tables. I followed normalization rules in designing my database. I have 7 tables explained in this schema.

I built a view to get the interesting columns out of these tables.

ALTER ALGORITHM = UNDEFINED DEFINER = `root`@`localhost` SQL SECURITY DEFINER VIEW `port_data_view` AS

SELECT `s`.`tel_subscriber_number` AS `phone`
    ,`s`.`tel_subscriber_name` AS `tel_subscriber_name`
    ,`thp`.`tel_subscriber_has_port_startdate` AS `Reservation_Date`
    ,`p`.`port_serialnum` AS `port_number`
    ,`p`.`port_current_speed` AS `port_speed`
    ,`p`.`idport` AS `idport`
    ,`p`.`port_status` AS `port_status`
    ,`isp`.`isp_en_name` AS `isp_en_name`
    ,`c`.`contract_enname` AS `contract_enname`
    ,`mdf`.`mdf_room_arname` AS `mdf_room_arname`
    ,`mdf`.`idmdf_room` AS `idmdf_room`
    ,`c`.`idcontract` AS `contract_id`
    ,`isp`.`idisp` AS `isp_id`
FROM (
    (
        (
            (
                (
                    (
                        `tel_subscriber` `s` INNER JOIN `tel_subscriber_has_port` `thp` ON (
                                (
                                    (`s`.`idtel_subscriber` = `thp`.`tel_subscriber_idtel_subscriber`)
                                    AND (`thp`.`tel_subscriber_has_port_status` = 'active')
                                    )
                                )
                        ) INNER JOIN `port` `p` ON ((`p`.`idport` = `thp`.`port_idport`))
                    ) INNER JOIN `isp_has_port` `ihp` ON (
                        (
                            (`p`.`idport` = `ihp`.`port_idport`)
                            AND (`ihp`.`isp_has_port_status` = 'Active')
                            )
                        )
                ) INNER JOIN `isp` ON ((`ihp`.`isp_idisp` = `isp`.`idisp`))
            ) INNER JOIN `mdf_room` `mdf` ON ((`mdf`.`idmdf_room` = `p`.`mdf_room_idmdf_room`))
        ) INNER JOIN `contract` `c` ON ((`c`.`idcontract` = `p`.`contract_idcontract`))
    )

as you can see there is 7 joins between these tables. when I try to search in this view about any factor (1 or multiple criteria), it's too slow (more than 10 minutes with no result). when I try to search in each table I get the result with maximum time of 5 seconds. The count of records in each table are:

mdf_room: 538
tel_subscriber: 798019
tel_subscriber_has_port: 790989
port: 797174
isp: 22
isp_has_port: 810676
contract: 25

I tried to explain the query in here.

what can I do to search with a proper time about the information I want?

Ccan you tell in english what information your query shoud return? — Meier, Aug 11 '15 at 22:42
@Meier this query is to build a view based on information from multiple tables. it contains the information I want to search about only. I have a search (filter) form including all of these columns. and the user can search on any criteria of them. — user3098586, Aug 11 '15 at 22:52
Do you have indices on the columns you're selecting on which the user can search? Adding restrictions to the view based on non-indexed columns may be the problem. — reaanb, Aug 11 '15 at 23:51

reaanb · Answer 1 · 2015-08-11T22:41:17.767

0

Use EXPLAIN on your query to understand MySQL's query plan. Create appropriate indices to prevent table scans. Here are some links for your reference:

Understanding the Query Execution Plan

MySQL Explain Explained

If you post the results of the EXPLAIN query, you may get more specific suggestions.

edited Aug 11 '15 at 22:41

answered Aug 11 '15 at 22:35

reaanb

9,806
2
23
37

I edited the question to show the results of EXPLAIN query. – user3098586 Aug 11 '15 at 22:47

score 0 · Accepted Answer · edited May 23 '17 at 12:29

Try to use Join with Subquery First.

The problem is that you are joining 7 tables (4 of them have about one million records) which will get mysql server crazy.

To overcome this problem try to join with the interesting data you want, by joining with subquery including the condition inside of it.

Example:

First case:

SELECT t1.*, t2.*
from table1 t1 join table2 t2 on (t1.id = t2.fkid)
where t2.attr1 = 'foo'

Second case:

select t1.*, t2_1.*
from table1 t1 join (select t2.* from table2 t2 where t2.attr1 = 'foo') t2_1
on t1.id = t2_1.fkid

when you have so many joins this would be useful. with no more than 2 or 3 joins the traditional join then search way would be useful too.

mysql search in multiple joined tables (7 tables with 6 joins)

2 Answers2