optimize the query on jquery interval

i have a function like below,

public function checkClassLiveDiscussionSession(Request $request, $course_menu_name, $topicslug, $slug)
    {
        $coursedetail = $request->attributes->get('coursedet');
        $purchasedpackages = $request->attributes->get('orderCheck');

        $topicdetail = Topics::where('slug', $topicslug)->where('active_status', 1)->where('course_id', $coursedetail->course_id)->first();

        if (!$topicdetail) {
            return false;
        }

        $checktopicpackagemap = $this->checkTopicPackageMapV2($coursedetail->course_id, $topicdetail->id, $purchasedpackages);

        if (!$checktopicpackagemap) {
            return false;
        }

        $liveclassdiscussion = DiscussionVideo::with('testdet')->where('slug', $slug)->where('class_type', '=', 'live')->where('active', 1)->first();

        if (!$liveclassdiscussion) {
            return false;
        }

        if (!$liveclassdiscussion->testdet) {
            return false;
        }

        $detaildata = PackageTestMaping::with(['testdetail' => function ($q) {
            $q->where('test_active', 1);
            // ->whereIn('test_type', $testtypes);
        }])->whereHas('testdetail')->whereIn('package_id', $purchasedpackages)->where('test_id', $liveclassdiscussion->test_id)->first();

        if (!$detaildata)
            return false;

        if (!$detaildata->testdetail)
            return false;

        $checktesttopicmap = DB::table('topic_test_maping')->where('topic_id', $topicdetail->id)
            ->where('test_id', $detaildata->testdetail->test_id)
            ->first();

        if (!$checktesttopicmap) {
            return false;
        }

        $session_id = session()->getId();

        $checkExists = DB::table('discussion_live_check')->where('user_id', uid())
            ->where('discussion_id', $liveclassdiscussion->id)
            ->where('session_id', $session_id)
            ->first();

        if (!$checkExists) {
            return response()->json(['message' => 'Livestream is already active with the same login in another device. Logout Manually from other device and try here.'], 404);
        }

        return response()->json(['message' => 'Video Played.'], 200);
    }

in jquery, i have an interval code where for every 1 minute it will call this function. is there any way i can reduce the query execution for every interval instead run only in a first interval or any other method to optimize this function?

Thank you

LaryAI

3 years ago

Level 58

One way to optimize this function is to cache the results of the queries that are unlikely to change frequently. For example, you can cache the results of the following queries:

Topics::where('slug', $topicslug)->where('active_status', 1)->where('course_id', $coursedetail->course_id)->first();
$this->checkTopicPackageMapV2($coursedetail->course_id, $topicdetail->id, $purchasedpackages);
DiscussionVideo::with('testdet')->where('slug', $slug)->where('class_type', '=', 'live')->where('active', 1)->first();
PackageTestMaping::with(['testdetail' => function ($q) { $q->where('test_active', 1); }])->whereHas('testdetail')->whereIn('package_id', $purchasedpackages)->where('test_id', $liveclassdiscussion->test_id)->first();
DB::table('topic_test_maping')->where('topic_id', $topicdetail->id)->where('test_id', $detaildata->testdetail->test_id)->first();

You can use Laravel's built-in caching system to cache the results of these queries. Here's an example of how you can cache the result of the first query:

$topicdetail = Cache::remember('topicdetail_' . $topicslug . '_' . $coursedetail->course_id, 60, function () use ($topicslug, $coursedetail) {
    return Topics::where('slug', $topicslug)->where('active_status', 1)->where('course_id', $coursedetail->course_id)->first();
});

This will cache the result of the query for 60 seconds. You can adjust the cache duration as needed.

You can apply the same caching technique to the other queries as well.

By caching the results of these queries, you can avoid executing them on every interval and instead only execute them when the cache expires or when the data changes. This can significantly reduce the load on your database and improve the performance of your application.

Please or to participate in this conversation.