1

I have the following code:

select
    *
from
    table_1
join
    table_2
on
    table_1.col1 = table_2.col1
where
    table_2.col2 = 1

This query works and give me the results that I expect. Now I would like to optimize this query. The idea is that I try to reduce the second query before the joining the two table. In other words, I suppose that "removing" rows and joining smaller tables should be faster that joining big tables and then selecting from them what I need. I implement my idea in the following way:

select
    *
from
    table_1
join
    (
    select
        *
    from
        table_2
    where
        table_2.col2 = 1
    )
on
    table_1.col1 = table_2.col1

Surprisingly the second query is significantly slower than the first one. What am I doing wrong?

Roman
  • 124,451
  • 167
  • 349
  • 456
  • 2
    I would call that a very poor optimizer in Exasol. Reasonable databases would typically produce the same execution plan for these queries. (Perhaps it is built on MySQL which would have the same defect in most versions.) – Gordon Linoff Aug 27 '14 at 11:18

1 Answers1

2

You can see difference in query execution plan.

Without plan i can only assume: In your first example, you have 2 tables. Mysql optimizer have some data statistic and can correctly choose and use index.

In your second query you don't have a table, only query result and optimizer haven't data statistic. May be in your case, optimizer execute query without index or something like this.

I think, subquery in your case, is bad practice. You have simple query, you must use your first example.

al_kash
  • 87
  • 4