1

I have two tables:

  • Business

  • Categories

Each business has a category and there are currently 10k+ businesses with different categories. I'm trying to find all of the businesses that have the category of 'Restaurant' using Laravel and MYSQL.

The code that I am using is below:

  $businesses = Business::join('category', 'category.id', '=', 'businesses.category')
              ->where('category.name', '=', $cat)->get();

Which, when $cat = 'Restaurant' brings back: 3183 results.

The issue is that at it's lowest, it's taking 8.9 seconds to 12 seconds to find any results. Can anyone suggest a way or where I'm going wrong which can improve how long this query takes to run?

Phorce
  • 4,424
  • 13
  • 57
  • 107

1 Answers1

2

Ensure that there are indexes on category.id and businesses.category to improve join speed , and category.name to speed the where.

RegularlyScheduledProgramming
  • 1,347
  • 1
  • 11
  • 27
  • Thanks! Can you offer any advice on how to update my table(s) with indexes? – Phorce Sep 05 '16 at 15:44
  • Have a look at http://stackoverflow.com/questions/3002605/how-do-i-add-indexes-to-mysql-tables. Also be aware that adding indexes to tables makes the tables both larger and slower to update. It's all a balancing act. – RegularlyScheduledProgramming Sep 05 '16 at 17:48