Блог

Рассмотрим следующую таблицу adverts (создание с использованием Laravel, но код довольно понятен):

 Schema::create('adverts', function (Blueprint $table) {
   $table->increments('id');      //PRIMARY KEY
   $table->string('name', 150);   //VARCHAR
   $table->text('description');   //TEXT

   $table->integer('city_id')->nullable();
   $table->foreign('city_id')->references('id')->on('cities');

   $table->timestamps();
});
  

В adverts таблице больше столбцов, но это те, которые необходимы прямо сейчас, imo. Я хочу, чтобы пользователи могли находить объявления с помощью функции текстового поиска, поэтому я добавил следующий FULLTEXT индекс:

 ALTER TABLE posts ADD FULLTEXT search(name, description)
  

Как вы можете видеть, каждое объявление также имеет city_id внешний ключ, который позволяет пользователю выполнять поиск adverts в city , а также в городах в пределах заданного радиуса. Для этой цели у меня есть radius таблица, которая содержит записи о парах городов и их расстоянии друг от друга:

 |  city_A  |   city_B   |distance|
----------------------------------
|    1     |     2      |  30    |
|    2     |     1      |  30    |
  

radius Таблица содержит около 7 000 000 записей, и все ее столбцы проиндексированы. Я засеял adverts таблицу примерно 100 000 поддельными записями (описания составляют около 700 символов). При поиске объявлений я выполняю следующий запрос Laravel:

 Advert::whereRaw('MATCH(name, description) AGAINST(? IN BOOLEAN MODE)', [$term])
       ->join('radius', function ($join) use ($radius, $id){
             $join->on('radius.secondary_id','=', 'posts.city_id');
        })->where('radius.primary_id', $id)
        ->whereBetween('radius.radius', [0,$radius]);
  

В SQL это переводится как:

 SLECT * FROM `adverts` 
INNER JOIN `radius` on `radius`.`city_B` = `posts`.`city_id` 
WHERE MATCH(name, description) AGAINST(? IN BOOLEAN MODE) 
AND `radius`.`city_A` = ? 
AND `radius`.`radius` between ? and ?
ORDER BY `created_at` desc 
LIMIT 20
  

Итак, как я уже сказал, в adverts таблице есть еще несколько столбцов, но они, похоже, не вызывают серьезных проблем при выполнении поискового запроса. Однако, как только я объединяю поиск по FULLTEXT терминам с поиском по близости города, выполнение запроса на моем локальном хосте занимает довольно много времени — где-то около 10-15 секунд. Похоже, что выполнение a MATCH для всей adverts таблицы работает нормально, radius поиск работает нормально, но в сочетании таким образом они приводят к довольно медленному отклику.

Могу ли я что-то сделать, чтобы улучшить таблицы или операторы запроса, или это тот случай, когда MySQL просто не справляется?

Мои спецификации для записи:

 CPU: Intel Core i5 3450 @ 3.10GHz Quad
RAM: 8,00GB Dual-Channel DDR3 @ 665MHz
HDD: Seagate ST3320613AS ATA Device (SATA) 7200RPM 16MB Cache
  

РЕДАКТИРОВАТЬ: MySQL объясняет запрос внутреннего соединения:

  ---- ------------- ------- ------ --------------- ----- --------- ----- ------ ------- 
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
 ---- ------------- ------- ------ --------------- ----- --------- -------- --- -- 
| 1  |    SIMPLE   |adverts|fulltxt|adverts_city_id_foreign,| search | 0 |  | 1 | Using where; Using filesort |
|    |             |       |       | search                 |        |   |
------------------------------------------------------------------------------------------
| 1  |   SIMPLE   | radius | ref  | city_A_index,| city_A_index| 5 | test_db.adverts.city_id | 1417 | Using where |
|    |            |        |      | city_B_index,|             |   |
|    |            |        |      | distances_ind|             |   |
 ---- ------------ -------- ------ -------------- ------------- --- --- --- ---