CODEWITHSUNDEEP
phpmysqlsqlquery-optimization
JDW
JDW

Reputation: 69

SQL Optimize Left Outer Join Query

first of all thanks in advance for taking the time to read and post any replies, I appreciate it!

I have two tables, one is for Properties, where it stores information about each Property/Estate. Another table is for Translations, where it stores data such as title and description in each language the user decides to write.

In the Properties table, the t_reference column will store the "reference" of the translation in the Translations table. This is a numeric value and will be 0 if there is no translation for that specific item.

In the Translations table, the previous t_reference is stored as 'reference'. Table output:

id  int(11)
reference   mediumint(9)
lang    varchar(5)
is_default  tinyint(4)
t_title     varchar(255)
t_description   text

Now, to grab the information, I am using this query:

SELECT
    p.id,
    p.t_reference,
    p.category,
    IFNULL(tr.t_title, def.t_title) 'title',
    IFNULL(tr.t_description, def.t_description) 'description'
FROM properties p
LEFT OUTER JOIN translations tr
    ON (p.t_reference > 0 AND p.t_reference = tr.reference AND tr.lang = 'de_DE')
LEFT OUTER JOIN translations def
    ON (p.t_reference > 0 AND p.t_reference = def.reference AND def.is_default = 1)
WHERE p.visibility='1'

p is the properties table, tr is the translations table data IF the translation exists, def is the translations table data default language. The issue I am having is that, with 300~ properties, this query is increasing page-render speed by about 80ms.

I am trying to implement multi-language input for the customer. Before, the title and description of each property was simply saved in a column, but now, as the customer should have the option for entering specific text for different languages, instead of having hard-coded columns like "title_en" "title_de" for instance, I thought this would be the best way of doing it. The only issue is the speed of the query.

First of all, is there a way to improve this query to speed up the process?

Second, would it be considered bad practise to, instead of using a JOIN in the query itself. Grab all the property data first, and then run a separate query to the translations table searching by the reference.

Upvotes: 1

Views: 1845

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269973

Start by writing the query like this:

SELECT p.id, p.t_reference, p.category,
           COALESCE(tr.t_title, def.t_title) as title,
           COALESCE(tr.t_description, def.t_description)  as description
FROM properties p LEFT OUTER JOIN
     translations tr 
     ON p.t_reference = tr.reference AND
        tr.lang = 'de_DE' LEFT OUTER JOIN
        translations def
     ON p.t_reference = def.reference AND
        def.is_default = 1
WHERE p.visibility = 1 AND  -- guessing visibility is a number
      p.t_reference > 0     -- guessing this is a filtering condition

Note the changes:

  • No quotes around '1', because visibility is presumably a number.
  • Moving p.t_reference > 0 condition to the WHERE clause. Because this is on the first table, this is presumably a filtering condition.
  • I prefer COALESCE() to IFNULL() because the former is the ISO/ANSI standard function in SQL.

You want indexes on:

  • properties(visibility, t_reference)
  • translations(reference, lang, is_default).

Upvotes: 1

Related Questions