Having issues now that my reactions table has passed 1 million rows

[vesper8]

I've started having some serious issues and I'm having a heck of a time troubleshooting and find the cause.

This is what my query looks like:

        $query = User::query()
        ->valid()
        ->with([
            'profile',
            'media',
            'chatrooms',
            'city',
            'loveReactant.reactions.reacter',
            'loveReactant.reactions.type',
        ]);
        
        /*
         * Do not include users that have one of the following reactions from you
         */
        $query->whereNotReactedBy($user, 'Like');
        $query->whereNotReactedBy($user, 'Dislike');
        $query->whereNotReactedBy($user, 'Icebreak');
        $query->whereNotReactedBy($user, 'Match');
        $query->whereNotReactedBy($user, 'Report');
        $query->whereNotReactedBy($user, 'Block');

        /*
         * Do not include users that have blocked you
         */
        $query->whereNotReactedTo($user, 'Block');

        $count = $query->count();

        $users = $query
            ->skip($skip)
            ->take($perPage)
            ->get();

This query is meant to return 12 records (out of thousands available that match the above query) but it will randomly only return 1 or 0 records, if I run the query multiple times, it randomly goes between 12 and 1/0.. even though I'm running this on localhost and there is nothing making changes to the DB in between runs.

I can't see any caching at play so I suspect that the large amount of reactions (over 1 million now) and the 7 calls to whereNotReactedBy and whereNotReactedTo must be at play.

If I comment out the
whereNotReactedBy and whereNotReactedTo the problem seems to go away.. but I do need my results filtered based on these reactions.

So what can I do? Would it make sense to have a whereNotReactedBy that can accept an array of reaction names in order to optimize further?

Any other idea what could be causing this abnormal issues I've started having?

Many thanks for any feedback..

[antonkomarev]

@vesper8 Method whereNotReactedBy wasn't designed to be called more than once. I've never tested it like that. Have you tried to look on raw query and execute it manually?

[antonkomarev]

You could try to modify whereNotReactedBy method to allow to pass an array of reaction types.

[antonkomarev]

@vesper8 did you solved an issue?