I need a better search than this:
index=shop sourcetype="source1" | chart count by action,productId | append [search index=shop sourcetype="source2" | chart count by action,productId]
It is taking too much time to return results. Can someone help optimize my search?
Not an answer, because you got some nice hints already. But check the slides from this
.conf Session http://conf.splunk.com/session/2015/conf2015_JHarty_DuncanTurnbull_Splunk_UsingSplunkSearchLanguage_...
Why is it so slow? Is it purely the volume of data, or is it field extractions or what?
To find out, please run the search over some reasonable amount of time, let it finish (or stop it) then click on the job inspector for the search. Post back what the largest few consumers of time are - a couple of them should stand out as "This is where the search's time is being spent."
Also, compare the amount of time for the above search with the amount of time for the same search if you try running it in "Fast mode". (That's the drop down at the right side, "Fast mode", "Verbose mode" or "Smart mode".
There are lots of solutions, which ones will work best for your particular problem is probably dependent on those answers.
index=shop | stats c(sourcetype) by action,productId
you can constrain it to specific source types too
index=shop (sourcetype=sourcetype1 OR sourcetype=sourcetype2)| stats c(sourcetype) by action,productId