Geek asks and answers

Entities and entities of MySQL: how to remove UNION?

I have these tables in the database:

  • objects (id, name, deleted)
  • entity_aliases (entity_id, alias_id)
  • aliases (id, name)

This query works fine, but slow due to UNION, notice how entity.name exchanges with aliases.name and moves to the last column:

SELECT entities.id, entities.name, entities.deleted, NULL AS main_name
FROM entities
UNION
SELECT entities.id, aliases.name, entities.deleted, entities.name AS main_name
FROM entities
JOIN entities_aliases ON entities_aliases.entity_id = entities.id
JOIN aliases ON entities_aliases.alias_id = aliases.id
ORDER BY name

This replacement is very important for me, I can’t delete it, but UNION will become slower and slower as the database develops in width (more fields and links) and depth (more records). How can I get rid of UNION?

Added: I need the result. For example, we have an entity 1-AAA and its aliases 1-BBB and 2-CCC. The output should be:

  • AAA
  • BBB (AAA)
  • CCC (AAA)
+3
source share
2

, , LEFT JOINS IFs:

SELECT e.id, IFNULL( a.name, e.name ) AS name, e.deleted, IF( a.name IS NULL, NULL, e.name ) AS main_name
FROM entities AS e
LEFT JOIN entities_aliases AS ea ON e.id = ea.entity_id
LEFT JOIN aliases AS a ON ea.alias_id = a.id;

, , , "" , ( ), main_name - null, , , . , !

+1

UNION ALL UNION:

SELECT *
FROM ((SELECT entities.id, entities.name, entities.deleted, NULL AS main_name
       FROM entities
      ) union all
      (SELECT entities.id, aliases.name, entities.deleted, entities.name AS main_name
       FROM entities e join
            entities_aliases ea
            ON ea.entity_id = e.id JOIN
            aliases a
            ON ea.alias_id = a.id
     )
    ) e
ORDER BY name

UNION ALL "" .

*, , ) . :

  • , entity.entity_id .
  • , alias.id .

, entity_aliases .

0

More articles:


All Articles