Are there faster alternatives to the CROSS JOIN model used below?

All I know is CROSS JOIN, but sometimes I look on StackOverflow for query support and find some answer mentioning that this is not the best join. I don’t know if they’re saying that due to an opinion on what would be easier on the developer, or if CROSS JOIN is functionally inferior to its JOIN brethren. Please tell me if refactoring with another JOIN model (LEFT? INNER? x?) should result in better performance for the query example below. (Constructive criticism towards other optimizations I may not even be aware to ask about would also not go amiss)

SELECT DISTINCT relationshipNameValue.value, proper_relationship_defined.relationship,
                commonRelationshipParentNameValue.value, common_relationship_defined.parent, 
                commonRelationshipChildNameValue.value, common_relationship_defined.child, 
                parentNameValue.value, proper_relationship_defined.parent, 
                text_value.value, proper_relationship_defined.child 
           FROM proper_relationship_defined 
     CROSS JOIN common_relationship_defined 
     CROSS JOIN proper_relationship_defined AS commonRelationshipParentName 
     CROSS JOIN proper_relationship_defined AS commonRelationshipChildName 
     CROSS JOIN proper_relationship_defined AS relationshipName 
     CROSS JOIN proper_relationship_defined AS parentName 
     CROSS JOIN proper_relationship_defined AS childName 
     CROSS JOIN text_value AS commonRelationshipParentNameValue 
     CROSS JOIN text_value AS commonRelationshipChildNameValue 
     CROSS JOIN text_value AS relationshipNameValue 
     CROSS JOIN text_value AS parentNameValue 
     CROSS JOIN text_value 
     CROSS JOIN concept AS childTypeCheck 
     WHERE proper_relationship_defined.parent = 65 
       AND commonRelationshipParentName.relationship = 30 
       AND commonRelationshipParentName.parent = common_relationship_defined.parent 
       AND commonRelationshipParentName.child = commonRelationshipParentNameValue.id 
       AND commonRelationshipChildName.relationship = 30
       AND commonRelationshipChildName.parent = common_relationship_defined.child 
       AND commonRelationshipChildName.child = commonRelationshipChildNameValue.id 
       AND relationshipName.relationship = 30 
       AND relationshipName.parent = proper_relationship_defined.relationship 
       AND relationshipName.child = relationshipNameValue.id 
       AND parentName.relationship = 30 
       AND parentName.parent = proper_relationship_defined.parent 
       AND parentName.child = parentNameValue.id 
       AND text_value.id = proper_relationship_defined.child 
       AND childTypeCheck.id = proper_relationship_defined.child 
       AND childTypeCheck.type = 2

Answer

CROSS JOIN generates a lot of rows, if there is no filtering. For that reason, it tends to be discouraged. Sometimes it is necessary.

In your case, you have bona fide joins. And you should learn how to use them.

For instance, for this condition:

. . .
      commonRelationshipParentName CROSS JOIN
      common_relationship_defined
. . .
WHERE commonRelationshipParentName.parent = common_relationship_defined.parent 

is better written as:

commonRelationshipParentName JOIN
common_relationship_defined
ON commonRelationshipParentName.parent = common_relationship_defined.parent

Why is this better? It puts the connection between the tables right next to where the tables are defined in the JOIN clause. Most databases enforce the presence of ON, which helps ensure that you don’t accidentally forget a condition.

That said, the MySQL optimizer is smart enough to turn your syntax into JOIN. However, I strongly recommend that you learn the proper, standard, readable JOIN syntax. Then you can learn about outer joins, which makes this syntax more powerful.