ON Key and On Keys queries

SQL++
1

Query-1:
Select count(*) FROM A Join B ON KEYS A.itemid
is slower.
While writing the same query in other hand

Query-2:
Select count(*) FROM B Join A ON KEY For A
is faster.

To optimize the query, I have to put a if condition, If some fields are empty the Query-1 else Query-2.
Done all the required field index as well.

So what should be the fix to avoid both queries and best solution to achieve the same in a single query?

2

Well, to address your first query, why are you doing the join? Is A.itemid an array? Or are some entries named by A.itemid not actually present in B? If neither of these conditions are true, you shouldn’t need to do the join.

3

What is the version? Can you post exact query and indexes.

4

using Ver 4.5.1, Query is like :
Select count(*) FROM A Join B ON KEYS A.itemid Where UserId='9999’
Is doing fast if UserId is not exist else slow.

But below query is doing fast if userid is exist else slow:
Select count(*) FROM B Join A ON KEY A.itemid For B Where UserId=‘9999’

So I have to add a if condition to check if userId exist then run Query1 else Query2, so how can I avoid if condition and make it in a single query?

5

First one is Look UP join. It try to fetch the B.
Second one is Index Join, i.e Join A and B with index and when matched then only does Fetch.

Can you try First query with the following index.

CREATE INDEX ix1 ON A(UserId,itemid);
Select count(1) FROM A Join B ON KEYS A.itemid Where UserId='9999'