Alternative to subsearch to search more than million entries

[gvreddy7]

Hi I have a sub search command which gives me the required results but is dead slow in doing so. I am having more than a million log entries that i need to search which is the reason why i am looking for an optimized solution. I have gone through answers asked for similar questions but not able to achieve what i need

I have a log which has transactions against an entry_id which always has a main entry and may or may not have subEntry

I want to find the count of version number for all the mainEntry log which has a subEntry

sample Query that i used
index=index_a [search index=index_a ENTRY_FIELD="subEntry"| fields Entry_ID] Entry_FIELD="mainEntry" | stats count by version

Sample data

Index=index_a
1) Entry_ID=abcd Entry_FIELD="mainEntry" version=1
Entry_ID=abcd ENTRY_FIELD="subEntry"
2)Entry_ID=1234 Entry_FIELD="mainEntry" version=1
3)Entry_ID=xyz Entry_FIELD="mainEntry" version=2
4)Entry_ID=lmnop Entry_FIELD="mainEntry" version=1
Entry_ID=lmnop ENTRY_FIELD="subEntry"
5)Entry_ID=ab123 Entry_FIELD="mainEntry" version=3
Entry_ID=ab123 ENTRY_FIELD="subEntry"

Please help in optimizing this

[gcusello]

Hi @gvreddy7,
at first you have a problem because there's a limit of 50,000 results in subsearches.
Anyway you could try a different approach:

index=index_a (ENTRY_FIELD="subEntry" OR Entry_FIELD="mainEntry")
| stats values(ENTRY_FIELD) AS ENTRY_FIELD values(version) count BY Entry_ID
| search ENTRY_FIELD="subEntry" AND Entry_FIELD="mainEntry"
| stats count by version

Ciao.
Giuseppe