Menu
I have two database tables: "places" and "translations". The translations of places names are made by selecting records from "places", which don't have the translations to the specified language yet:
SELECT `id`, `name`
FROM `places`
WHERE `id` NOT IN (SELECT `place_id` FROM `translations` WHERE `lang` = 'en')
This worked fine with 7 000 records of places, but crashed when the number of translations reached 5 000. Since then, the query takes about 10 seconds and returns the error:
2006 - MySQL server has gone away
As I understand, the main problem here is the subquery returning to many results, bu how could I solve it, if I need to select all the places which are not translated yet?
My plan B is to create a new boolean field in "places" table, called "translated", and reset it to "false", each time I change language - that would prevent for having subquery. However, maybe I could just modify my current SQL statement and prevent from adding additional field?
272
An alternative for IN and EXISTS is an INNER JOIN, while a LEFT OUTER JOIN with a WHERE clause checking for NULL values can be used as an alternative for NOT IN and NOT EXISTS.
When using NOT IN , the subquery returns a list of zero or more values in the outer query where the comparison column does not match any of the values returned from the subquery.
Using common table expressions (CTE): Common table expressions are an alternative to subqueries. You can learn more about this feature in the following article: CTEs in SQL Server; Querying Common Table Expressions.
An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery. Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
The obvious alternative:
SELECT
`id`, `name`
FROM
`places`
WHERE
NOT EXISTS (
SELECT 1 FROM `translations` WHERE `id` = `places`.`id` AND `lang` = 'en'
)
There should be a clustered composite index over (translations.id, translations.lang) (composite means: a single index over multiple fields, clustered means: the index governs how the table is sorted).
150
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
