SQL (any) Query for understanding query optimization
I am having a particularly slow request due to the large amount of information being bundled together. However, I needed to add a where clause in the form of id (select id from table).
I want to know if there is any gain from the next and more pressing, even will give the desired results.
select a.* from a where a.id in (select id from b where b.id = a.id)
as an alternative:
select a.* from a where a.id in (select id from b)
Update: MySQL There could be no more specific excuse for table a effectively joins between 7 different tables. using * for examples
Change, b is not selectable
a source to share
Your question was about the difference between these two:
select a.* from a where a.id in (select id from b where b.id = a.id)
select a.* from a where a.id in (select id from b)
The first is a correlated subquery. This can force MySQL to execute a subquery for every row a
.
The latter is an uncorrelated subquery. MySQL should be able to execute it once and cache the results to compare against each row a
.
I would use the latter.
a source to share
Both of the queries you mentioned are equivalent:
select a.*
from a
inner join b on b.id = a.id
Almost all optimizers will execute them in the same way.
You can post the actual execution plan and someone here can give you a way to speed it up. It helps if you specify which database server you are using.
a source to share
YMMV, but I've often found that using EXISTS instead of IN makes queries faster.
SELECT a.* FROM a WHERE EXISTS (SELECT 1 FROM b WHERE b.id = a.id)
Of course, without seeing the rest of the request and context, it might not make the request faster.
JOINING might be the preferred option, but if a.id appears more than once in the id b column, you will need to drop DISTINCT there and you will most likely fall back in terms of optimization.
a source to share
I would never use a subquery like this. The connection will be much faster.
select a.*
from a
join b on a.id = b.id
Of course, don't use select * (especially never use it when doing a join as it iterates over at least one field), and it wastes network resources sending unnecessary data.
a source to share
Select a.* from a
inner join (Select distinct id from b) c
on a.ID = c.AssetID
I tried all 3 versions and they ran about the same. Execution plan was the same (inner join, IN (with and without where clause in subquery), Exists)
Since you are not selecting any other fields from B, I prefer to use Where IN (Select ...). Anyone will look at the request and know what you are trying to do (only show in a if in b.).
a source to share
your problem is most likely in seven tables within "a"
make the FROM table contain "a.id" do the following join: inner join b to a.id = b.id
then join the other six tables.
you really need to show the whole query, list all indexes and the approximate row count of each table if you want real help
a source to share