Оптимизация запросов к большей базе данных MySQL

Я пишу сайт, на котором будут храниться некоторые предложения (например, предложения о работе). В конце концов, он может содержать более 1 млн. Предложений. Теперь у меня проблемы с некоторыми неэффективными запросами SQL.

Сценарий:

• Каждое предложение может быть отнесено к категории (например, ИТ-работы)
• Каждая категория имеет настраиваемые поля (например, ИТ-задания могут иметь настраиваемое поле типа «цена», которое будет представлять принимающее число (цена) в текстовом поле — в нашем примере, скажем, у нас есть ввод цен ожидаемой зарплаты)
• Каждое предложение хранит метаданные со значениями пользовательских полей этой категории.
• Поля БД, которые будут использоваться для фильтрации, имеют индексы

Таблица категории (я использую вложенные наборы для хранения иерархии категорий):

CREATE TABLE `category` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`parent_id` int(11) DEFAULT NULL,
`lft` int(11) DEFAULT NULL,
`rgt` int(11) DEFAULT NULL,
`depth` int(11) DEFAULT NULL,
`order` int(11) NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `category_parent_id_index` (`parent_id`),
KEY `category_lft_index` (`lft`),
KEY `category_rgt_index` (`rgt`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Таблица category_field:

CREATE TABLE `category_field` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`category_id` int(10) unsigned NOT NULL,
`name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`optional` tinyint(1) NOT NULL DEFAULT '0',
`type` enum('price','number','date','color') COLLATE utf8_unicode_ci NOT NULL,
PRIMARY KEY (`id`),
KEY `category_field_category_id_index` (`category_id`),
CONSTRAINT `category_field_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Предложение стола:

CREATE TABLE `offer` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`text` text COLLATE utf8_unicode_ci NOT NULL,
`category_id` int(10) unsigned NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `offer_category_id_index` (`category_id`),
CONSTRAINT `offer_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `category` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Таблица offer_meta:

CREATE TABLE `offer_meta` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`offer_id` int(10) unsigned NOT NULL,
`category_field_id` int(10) unsigned NOT NULL,
`price` double NOT NULL,
`number` int(11) NOT NULL,
`date` date NOT NULL,
`color` varchar(7) COLLATE utf8_unicode_ci NOT NULL,
`created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`id`),
KEY `offer_meta_offer_id_index` (`offer_id`),
KEY `offer_meta_category_field_id_index` (`category_field_id`),
KEY `offer_meta_price_index` (`price`),
KEY `offer_meta_number_index` (`number`),
KEY `offer_meta_date_index` (`date`),
KEY `offer_meta_color_index` (`color`),
CONSTRAINT `offer_meta_category_field_id_foreign` FOREIGN KEY (`category_field_id`) REFERENCES `category_field` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `offer_meta_offer_id_foreign` FOREIGN KEY (`offer_id`) REFERENCES `offer` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=107769 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Когда я настраиваю некоторые фильтры на своей странице (например, для нашего настраиваемого поля зарплаты), я должен начинать с запроса, который возвращает минимальные и максимальные цены в доступных записях offer_meta (я хочу показать ползунок диапазона пользователю во внешнем интерфейсе, поэтому мне нужны значения MIN / MAX для этого диапазона):

select MIN(`price`) AS min, MAX(`price`) AS max from `offer_meta` where `category_field_id` = ? limit 1

Я обнаружил, что эти запросы являются наиболее неэффективными из всех запросов, которые я делаю (вышеупомянутый запрос занимает более 500 мс, когда таблица offer_meta содержит несколько тысяч записей).

Другие неэффективные запросы (offer_meta имеет 107k записей):

Получение значений MIN и MAX для ползунка для фильтрации чисел

select MIN(`number`) AS min, MAX(`number`) AS max from `offer_meta` where `category_field_id` = ? limit 1

Получение минимальных и максимальных цен на слайдер для фильтрации по ценам

select MIN(`price`) AS min, MAX(`price`) AS max from `offer_meta` where `category_field_id` = ? limit 1

Получение минимальной и максимальной дат для ограничения диапазона дат

select MIN(`date`) AS min, MAX(`date`) AS max from `offer_meta` where `category_field_id` = ? limit 1

Получение цветов со счетчиками для отображения списка цветов с числами

select `color`, count(*) as `count` from `offer_meta` where `category_field_id` = ? group by `color`

Пример полного запроса для получения количества предложений с несколькими критериями фильтра (0,5 с)

select count(*) as count from `offer` where id in (select
distinct offer_id
from offer_meta om
where offer_id in (select
distinct offer_id
from offer_meta om
where offer_id in (select
distinct offer_id
from offer_meta om
where offer_id in (select
distinct om.offer_id
from offer_meta om
join category_field cf on om.category_field_id = cf.id
where
cf.category_id in (2,3,4,41,43,5,6,7,8,37) and
om.category_field_id = 1 and
om.number >= 1 and
om.number <= 50) and
om.category_field_id = 2 and
om.price >= 2 and
om.price <= 4545) and
om.category_field_id = 3 and
om.date >= '0000-00-00' and
om.date <= '2015-04-09') and
category_field_id = 4 and
om.color in ('#0000ff'))

Тот же запрос без функции агрегирования (COUNT) выполняется в несколько раз быстрее (только для получения идентификаторов).

Вопрос:

Можно ли настроить эти запросы, или у вас есть какие-либо предложения о том, как реализовать мою логику (предложения с категориями и настраиваемыми полями, динамически добавляемыми в admin для каждой категории) с другой схемой таблицы? Я перепробовал еще несколько схем, но безуспешно.

Вопрос 2:

Как вы думаете, это моя проблема с сервером MySQL, и если я куплю VPS, все будет хорошо?

Помогите понять еще лучше:

Я был сильно вдохновлен схемой WordPress для пользовательских полей, поэтому логика схожа.

Последние заметки:

Также я работаю над фреймворком Laravel и использую Eloquent ORM.
Извините за мой английский, я надеюсь, что прояснил мою проблему 🙂

Заранее спасибо,
Patrik