Essentially, I want to get intersection between 2 tables based on matching IDs with the option of wildcard '*' to return all IDs:
Select * from A a inner join B b on b.id = a.id or b.id = '*'
Table A is fairly large (10 M rows), and I have setup the
id to be index already.
id is not used, and it does a full scan (Takes 20 secs).
If I don't allow the wildcard:
Select * from A a inner join B b on b.id = a.id
id index and takes only 300 ms
By separating into 2 select with Union helped. Detail here: http://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-ideahttps://stackoverflow.com/questions/5901791/is-having-an-or-in-an-inner-join-condition-a-bad-idea