select count(*) is too slow

@Tray2 I agree with you . Maybe the problem is somewhere else

@jonhyknid It is most likely the foreign keys that isn't properly indexed, maybe he is using uuid. How ever it might also be faster to get the top visitors first, before hitting the teachers table.

Or there is no index on the visitable_type

@Tray2

Teacher migration:

Schema::create('teachers', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->json('name');
            $table->longText('email');
            $table->json('degree');
            $table->json('position');
            $table->json('body');
            $table->foreignId("branch_department_id")->constrained()->cascadeOnDelete()->cascadeOnUpdate();

        });

Visit table:

Schema::create('visits', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->ipAddress();
            $table->string('country')->nullable();
            $table->unsignedBigInteger('visitable_id')->nullable();
            $table->string('visitable_type')->nullable();
            $table->boolean('full_load')->default(false);
        });

@moham96 Dude, this is really bad. See my comments.

Schema::create('teachers', function (Blueprint $table) {
            $table->id(); //Good

            $table->timestamps(); //Good

            $table->json('name'); //Why the hell do you use json here? use string 255 characters is enough. 

            $table->longText('email'); //Why use long text here use string 255 characters is enough

            $table->json('degree'); //Why use json here, if more than one possible value, extract to another table.

            $table->json('position'); //Why use json here, same as for the degree, extract if more than one.

            $table->json('body'); //I guess this is some kind of summary, but text should be used,

            $table->foreignId("branch_department_id")->constrained()->cascadeOnDelete()->cascadeOnUpdate();

        });

I also suggest that you read this post.

https://tray2.se/posts/database-design

Adding index to visitable_type makes the sql query instant but the same code in PHP is still slow for some reason

Of course it does, share your php code and show us what you do in it.

@Tray2 I'm using json on these fields because I'm using Laravel-translatable plugin from spatie which needs the text fields to be json instead so that it saves a value for each language. Your note about the email field is valid though.

Of course it does, share your php code and show us what you do in it. well the code is the same code in the first post

Teacher::withCount('visits')->orderBy('visits_count', 'desc')->limit(10)->get(['id', 'name', 'visits_count'])

Note that I can't constrain the visitable_id or the visitable_type since they are for a polymorphic relationship

@Tray2 So I added an index to the visitable_id as well which made the query fast even in eloquent, but I'm curious why the query was fast in SQL and slow in eloquent before I added the index

@moham96 That is very unlikely that they differ, since they both are SQL when it runs the query.

@moham96 That is a really bad solution for a multi language application. Any search against these fields would be slow, since you can't really index them, and you are adding extra complexity.

• The name of the teacher can hardly be a subject of a translation, that is bonkers.
• The degree would serve you much better as a foreign key towards a degree table, with different languages connected.
• The same goes with position
• The body should also have it's own table with a translation for the languages available.

@EmilMoe Yes and no, you can use json for basically any type of text column, should you do that, hell no. All columns should have the datatype they need, that way the database will be properly optimized.

Searching json columns, while possible slows down the query, it is like searching for a name and you don't have an index for it, it makes you need to look at all names, in the table, this is known as a full table scan. You can't really index a json column to make the search faster, you can however cheat and make a generated column with a value from the json clob, and then index that. However then you can just do it right from the start.