CODEWITHSUNDEEP
sqldatabaseperformancecartesian-product
isnulltrue
isnulltrue

Reputation: 13

Improving SQL cartesian product performance by reducing columns

I have an SQL query which uses cartesian product on a large table. However, I only need one column from one of the tables. Would it actually perform better, if I selected only that one column before using the cartesian product?

So, in other words, would this:

SELECT A.Id, B.Id
FROM (SELECT Id FROM Table1) AS A , Table2 AS B;

be faster than this, given that Table1 has more columns than Id?:

SELECT A.Id, B.Id
FROM Table1 AS A , Table2 AS B;

Or does the number of columns not matter?

Upvotes: 1

Views: 725

Answers (2)

Carsten Massmann
Carsten Massmann

Reputation: 28236

Try it out yourself! But generally speaking having a subquery reduce the number of rows will help improve the performance. Your query should, however, be written differently:

select a.id aid, b.id bid from
(Select id from table1 where id=<specific_id>) a, table2 b

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

On most databases, the two forms would have the same execution plan.

The first could would be worse on a database (such as MySQL) that materializes subqueries.

The second should be better with indexes on the two tables . . . table1(id) and table2(id). The index would be used to get the value rather than the base data.

Upvotes: 1

Related Questions