Search code examples
mysqlsqlsqlyog

Improving query speed: simple SELECT from SELECT in huge table

I have a table contains 3 columns : age , name , nickname I would like to get only the names (+age) where the name+age does not exist at all in nickname+age.

For example : if table : DETAILS contains 2 rows :

  1. age: 5 , name: suzi, nickname: suzi
  2. age:2 , name : gil, nickname: g

query will return : age:2 , name : gil

SELECT d1.AGE, d1.NAME
FROM DETAILS d1
WHERE d1.NAME NOT IN (SELECT d2.NICKNAME FROM DETAILS d2 WHERE d2.AGE = d1.AGE)

This query runs only on small data. Any idea how to improve it?

Solution

  • The critical point in SQL query performance is using index. So you have to have the index in the querying/joining columns and you need to use it (via join).

    E.g. query:

    SELECT DISTINCT D1.AGE, D1.NAME
FROM DETAILS D1 LEFT JOIN DETAILS D2 ON D1.AGE = D2.AGE
WHERE D1.NAME <> D2.NICKNAME

    Note that you have to create indexes on columns AGE, NAME, AND NICKNAME beforehand to fully benefit from this query.