1

Let say I have two tables and a SQL query like this

SELECT table_a.*, table_b.*
FROM table_a, table_b
WHERE table_a.id = ?
AND table_b.col_x = table_a.col_x
AND table_b.id = table_a.id;

Would there be any performance improvement by rewriting the SQL query to JOIN

SELECT table_a.*, table_b.*
FROM table_a
JOIN table_b on (table_b.id = table_a.id AND table_b.col_x = table_a.col_x)
WHERE table_a.id = ?;

Assuming that the tables have these current INDEXES

--table_a
create index table_a_index_z_id on table_a (col_z, id);

--table_a
create index table_a_index_x on table_a (col_x);

--table_b
create index table_b_index_id on table_b (id);

What extra indexes if any would improve the performance of the queries above?

EDIT

The primary keys for the tables are

alter table table_a add (constraint table_a_pk primary key (id, col_b, col_x))
// there is no PK on table_b 
  • 1
    The two queries are the same as far as SQL Server is concerned but might want to specify the RDBMS so we know for sure. As for the indexes, it really depends on the usage of the two tables. Are they write intensive, read intensive, lots of rows in table_a, fewer rows in table_b, etc. Without knowing more about the tables and their usage, it's going to be difficult to comment on indexes. – Shooter McGavin Nov 26 '18 at 17:41
  • 1
    You might get more traction if you tag the question with the RDBMS you are using. – Rick James Nov 27 '18 at 3:56
  • 1
    What is the PRIMARY KEY of each ttable? – Rick James Nov 27 '18 at 3:57
1

Your actual answer will depend on what database system you are working on. However, generically, the indexes may help depending on the sophistication of the query optimizer.

Both queries are technically identical. One uses the old SQL format (SQL-89?) for joins, while the other uses the explicit JOIN statement from SQL-92. However, the second form may give you better performance because of optimizer differences.

Additionally, it is more readable, and for certain complex queries, the old form LEFT and RIGHT JOIN equivalents can result in incorrect outputs. Since they are also disallowed in some database engines (including later versions of SQL Server), using the SQL-92 formats consistently, instead of having inner joins use the old form join and LEFT, RIGHT (and if implemented, CROSS and FULL OUTER) JOINs use the explicit form is potentially confusing. Using the explicit join syntax from SQL-92 is highly recommended.

The indexing needed for JOINing the two tables is suboptimal. The most efficient style of index for you would have an index on both id and col_x on both tables, since both fields are used in the table JOIN. This, however, can vary by the actual data stored in both tables.

For example, if table_b only contains a few dozen records (plus or minus, depending on record size, actual database system used, speed of disk and memory, etc.), then the system may choose to scan the table for matches no matter what indexes are created, simply because the table scan on such a small table is more efficient than looking rows up in the index, then finding the row on disk to read it. But, generically, the indexing I talked about above is the most efficient.

So now you see some of why indexing in databases is a bit of an art, even though the logic of it is straightforward. There are still other considerations in the real world. Welcome to the world of DBA experience!

1

Your first query is also a JOIN, only using the 'old' ANSI-89 syntax, instead of the explicit JOIN clause which was introduced in ANSI-92.

Most DBMS should treat both queries as equivalent, but you should check with your specific engine by executing both and examining the execution plan.

With regard to indexing, I would recommend that you try a composite index on (id, col_x) for both tables. I would also try and add a predicate to the WHERE clause:

WHERE table_a.id = ? AND table_b.id = ?

Even though this is logically redundant, and will not change the output as the join already filters out rows where the id is not the same, some query optimizers tend to better use indexes when the predicate is explicit and not implied, as they lack the logic to infer that. Check the execution plan, and you will know.

Happy holidays!

1
WHERE table_a.id = ?

So, table_a needs some kind of index on id. Does that happen to be the PRIMARY KEY?

AND table_b.col_x = table_a.col_x
AND table_b.id = table_a.id;

Now, table_b needs either of these:

INDEX(id, col_x)
INDEX(col_x, id)

The former would make your current (id) redundant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.