Am trying to optimize the performance of a dashboard that was built some time back. The existing dashboard has been created by joining atleast 10 lookup files in the same query for a panel. And over time the lookup has increased in size going more than 1,00,000 rows. This has caused a lot of problems in the join conditions made in the query.
I would like to understand what is the search performance differnce when data is from a lookup or loaded from index.
Does lookup command have limitations like the join command like, what is the max limit for a lookup command. Can it be used instead of join when data is from a lookup.
Is there a difference in join limit set in limits.conf for data from Index and data from lookup. I have a scenario where the limits.conf default value for join and subquery has been increased and also am using max=0 in my join. But the results are not coming as expected. It works perfectly when i optimize the subquery having lookup to have less than 50,000 rows.