We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

MySQL Database Design Performance And Query

MySQL DB Design Schema

Hi As you can see there are 2 kinda designs we've got here. Both of them are based on that there is only one row per main table or mains table row in the other relation tables. [1:1]

Design 1: there are null-able foreign-keys of tables; items, others, seconds in mains table

Design 2: there is foreign-key of main table in others

My Question is, which design is appropriate? For making Select SQL query, in "Design 1" there is a need of using UNION and for "Design 2" LEFT JOIN would be the choice I assume. So, in terms of performance through large amount of data which is suitable and how should be the sql query?

    SELECT mains.id as mainId, items.id as id, mains.title as title, items.name as name
    FROM items, mains where mains.itemId=items.id
    UNION
    SELECT mains.id as mainId, others.id as id, mains.title as title, others.name as name
    FROM others, mains where mains.otherId=others.id  
    UNION
    SELECT mains.id as mainId, seconds.id as id, mains.title as title, seconds.name as name
    FROM seconds, mains where mains.secondId=seconds.id

    ------------------------------

    SELECT  *
    FROM  mains
    LEFT JOIN  items ON mains.itemId=items.Id
    LEFT JOIN  seconds ON mains.secondId=seconds.id
    LEFT JOIN  others ON mains.otherId=others.id 

    ------------------------------

    SELECT main.id as mainId, item.id as id, main.title as title, item.name as name
    FROM item, main where main.id=item.mainId
    UNION
    SELECT main.id as mainId, other.id as id, main.title as title, other.name as name
    FROM other, main where main.id=other.mainId  
    UNION
    SELECT main.id as mainId, second.id as id, main.title as title, second.name as name
    FROM second, main where main.id=second.mainId

    ------------------------------

    SELECT  *
    FROM main mains
    LEFT JOIN item items ON mainmains.id=itemitemId=items.mainIdId
    LEFT JOIN second seconds ON mainmains.id=secondsecondId=seconds.mainIdid
    LEFT JOIN other others ON mainmains.id=otherotherId=others.mainIdid 

different sql query approach..! which one? performance?

P.S. needless to say others, seconds, other, second tables, each of them has like 10 relation tables which I omited in here..

I think the result will be closely the same, depending of the index you set.
The real prroblem are the relations, if you have a large amount of data you need to normalize all your joined table in one view or in an search engine like sphinx :
https://sphinxsearch.com/