CODEWITHSUNDEEP
mysqlsqllaravel-5
pwnz22
pwnz22

Reputation: 469

Improve SQL queries speed

I'm using MySQL and i have schema like:

|------------|-------------|------------|--------------|
|    cities  |category_city|  categories|   companies  |
|------------|-------------|------------|--------------|
|     id     |  city_id    |     id     |     id       |
|    name    | category_id |    name    |subcategory_id|
|            |             |  parent_id |    city_id   |
|            |             |            |...other cols |
|____________|_____________|____________|______________|

Relationships: City with Category has ->belongsToMany()

public function categories()
{
    return $this->belongsToMany(Category::class);
}

Categories has subcategories:

public function subcategories()
{
    return $this->hasMany(Category::class, 'parent_id', 'id');
}

And i'm getting companies from category and filtering by city, because i need the current city companies and for that i have a global scope:

 public function getCompanies($city_id)
    {
        return $this->companies()->whereHas('mainCity', function ($q) use ($city_id) {
            $q->where('city_id', $city_id);
        });
    }

mainCity method:

public function mainCity()
{
    return $this->belongsTo(City::class, 'city_id');
}

Here is my method performing the query with AJAX request:

public function getPlaces(City $city, Category $subcategory, $north, $south, $east, $west)
{
    $companies = $subcategory->companies()
        ->withCount('comments')
        ->companiesByBounds($north, $south, $east, $west)
        ->paginate(8);

    $markers = $subcategory->companies()
        ->companiesByBounds($north, $south, $east, $west)
        ->get(['lat', 'lng', 'slug', 'title']);

    return response()->json(['companies' => $companies, 'markers' => $markers], 200, [], JSON_NUMERIC_CHECK);
}

and by companiesByBounds scope method:

public function scopeCompaniesByBounds($query, $north, $south, $east, $west)
{
    return $query->whereBetween('lat', [$south, $north])
        ->whereBetween('lng', [$west, $east]);
}

In companies i have ~2m records. The main problem is that the queries taking 3.5 seconds. Help please to improve my queries.

Here is the query:

select count(*) as aggregate from `companies` where `companies`.`category_id` = '40' and `companies`.`category_id` is not null and `lat` between '53.68540097020851' and '53.749703253622705' and `lng` between '91.34262820463869' and '91.51600619536134'

Upvotes: 1

Views: 85

Answers (1)

StanislavL
StanislavL

Reputation: 57381

To improve speed you need to add indexes on the columns lat and lng.

CREATE INDEX idx_lat ON companies (lat);

The indexes are used in queries when the columns are added to conditions.

Upvotes: 1

Related Questions