Что быстрее индекс по varchar(255) или индекс по int(11)

[Роман Дмитриев]

На 100 тысяч товаров делайте в одну таблицу, если у вас количество характеристик до 50 по одному id. Иначе больше времени будет уходить на соединение таблиц между собой при запросах.
<-------------- добавлено через 218 сек. -------------->
А так что покажет:

 SELECT b.id, b.name, b.url, b.meta_title, b.meta_keywords, b.meta_description, b.description, b.image FROM s_brands b, s_products p, s_products_categories s
 WHERE b.`id`= p.`brand_id`
 AND p.`visible`=1
 AND p.`id` = s.`product_id`
 AND s.`category_id` in ('7','97','128','208','6','277','314','341','39','71','137','217','231','70','83','111','135','143','283','361','82','89','382','88','96','98','238','304','379','5','20','99','358','19','30','33','46','47','65','100','121','129','145','149','154','207','221','225','232','258','266','267','275','295','318','359','29','51','64','381','172','174','243','261','280','328','331','289','305','50','118','203','216','260','308','357','380','117','171','175','291','378','170','253','296','298','252','4')

<-------------- добавлено через 157 сек. -------------->
Если надо отобрать уникальные записи из brands, то так:

 SELECT b.id, b.name, b.url, b.meta_title, b.meta_keywords, b.meta_description, b.description, b.image FROM s_brands b
 WHERE EXISTS (
 SELECT 1 FROM s_products p, s_products_categories s WHERE b.`id`= p.`brand_id`
 AND p.`visible`=1
 AND p.`id` = s.`product_id`
 AND s.`category_id` in ('7','97','128','208','6','277','314','341','39','71','137','217','231','70','83','111','135','143','283','361','82','89','382','88','96','98','238','304','379','5','20','99','358','19','30','33','46','47','65','100','121','129','145','149','154','207','221','225','232','258','266','267','275','295','318','359','29','51','64','381','172','174','243','261','280','328','331','289','305','50','118','203','216','260','308','357','380','117','171','175','291','378','170','253','296','298','252','4')

 

[p2rcoder]

если поле индексировано то разницы никакой нет varchar оно или int, скорость будет одинаковой

 

[babahalki]

Посмотреть вложение 90285

На 100 тысяч товаров делайте в одну таблицу, если у вас количество характеристик до 50 по одному id. Иначе больше времени будет уходить на соединение таблиц между собой при запросах.

То, что лучше без джоинов - это понятно. Я сейчас избавился от джоинов засунув все опции в 1 таблицу, там можно 64 индекса - макс. т.е. 1 - primary и еще 63 остаются для опций. Этого вполне достаточно.

Но если у меня будет больше, тут какой выход? Сделать 2 таблицы и джоинить их при необходимости. Или как-то можно все равно писать хитро в 1 таблицу?

<-------------- добавлено через 218 сек. -------------->
А так что покажет:

SELECT b.id, b.name, b.url, b.meta_title, b.meta_keywords, b.meta_description, b.description, b.image FROM s_brands b, s_products p, s_products_categories s
WHERE b.`id`= p.`brand_id`
AND p.`visible`=1
AND p.`id` = s.`product_id`
AND s.`category_id` in ('7','97','128','208','6','277','314','341','39','71','137','217','231','70','83','111','135','143','283','361','82','89','382','88','96','98','238','304','379','5','20','99','358','19','30','33','46','47','65','100','121','129','145','149','154','207','221','225','232','258','266','267','275','295','318','359','29','51','64','381','172','174','243','261','280','328','331','289','305','50','118','203','216','260','308','357','380','117','171','175','291','378','170','253','296','298','252','4')

Так гораздо быстрее выполняется, вот что explain показал:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE s range PRIMARY,category_id category_id 4 NULL 437281 Using where; Using index
1 SIMPLE p eq_ref PRIMARY,brand_id,visible PRIMARY 4 sevenlig_rapida.s.product_id 1 Using where
1 SIMPLE b eq_ref PRIMARY,id_name PRIMARY 4 sevenlig_rapida.p.brand_id 1 NULL

В чем хитрость?

<-------------- добавлено через 157 сек. -------------->
Если надо отобрать уникальные записи из brands, то так:

SELECT b.id, b.name, b.url, b.meta_title, b.meta_keywords, b.meta_description, b.description, b.image FROM s_brands b
WHERE EXISTS (
SELECT 1 FROM s_products p, s_products_categories s WHERE b.`id`= p.`brand_id`
AND p.`visible`=1
AND p.`id` = s.`product_id`
AND s.`category_id` in ('7','97','128','208','6','277','314','341','39','71','137','217','231','70','83','111','135','143','283','361','82','89','382','88','96','98','238','304','379','5','20','99','358','19','30','33','46','47','65','100','121','129','145','149','154','207','221','225','232','258','266','267','275','295','318','359','29','51','64','381','172','174','243','261','280','328','331','289','305','50','118','203','216','260','308','357','380','117','171','175','291','378','170','253','296','298','252','4')

Объясни, пожалуйста, в чем преимущество выборки select * from table1, table2?

сравнил на своем тестере скорость твоего запроса уникальных и оригинального. Что-то плоховато твой запрос работает. в 6 раз дольше.

если поле индексировано то разницы никакой нет varchar оно или int, скорость будет одинаковой

А если я составной индекс делать буду, сколько я таких варчар полей смогу собрать? 2 или 3 в лучшем случае.

 

[Роман Дмитриев]

если поле индексировано то разницы никакой нет varchar оно или int, скорость будет одинаковой

Не совсем так. Дело в том, что varchar в памяти занимает много больше места, чем int. На небольшом количестве записей (до 10 тысяч разница заметна не будет). А вот от 100 тысяч записей разница уже может быть заметна.
<-------------- добавлено через 702 сек. -------------->

То, что лучше без джоинов - это понятно. Я сейчас избавился от джоинов засунув все опции в 1 таблицу, там можно 64 индекса - макс. т.е. 1 - primary и еще 63 остаются для опций. Этого вполне достаточно.
Но если у меня будет больше, тут какой выход? Сделать 2 таблицы и джоинить их при необходимости. Или как-то можно все равно писать хитро в 1 таблицу?

Неужели для поиска требуется фильтр по всем 64 полям? Это что-то чересчур

Так гораздо быстрее выполняется, вот что explain показал:
В чем хитрость?
Объясни, пожалуйста, в чем преимущество выборки select * from table1, table2?

Операция IN является приоритетной, поэтому сервер вынужден выполнить эту операцию сначала, прежде чем он перейдёт к обработке запроса дальше. Преимущество запроса выше заключается в том, что при его использовании сначала объединяются все таблицы, при этом для объединения используются индексы. Нюанс в том, что этом запросе значения в выборке могут повторяться, если один и тот же товар относится к нескольким категориям.

А если я составной индекс делать буду, сколько я таких варчар полей смогу собрать? 2 или 3 в лучшем случае.

Если предполагается хранить численные данные, то не стоит использовать тип varchar для этого. Это будет сильно хуже.

 

[babahalki]

Не совсем так. Дело в том, что varchar в памяти занимает много больше места, чем int. На небольшом количестве записей (до 10 тысяч разница заметна не будет). А вот от 100 тысяч записей разница уже может быть заметна.
<-------------- добавлено через 702 сек. -------------->


Неужели для поиска требуется фильтр по всем 64 полям? Это что-то чересчур


Операция IN является приоритетной, поэтому сервер вынужден выполнить эту операцию сначала, прежде чем он перейдёт к обработке запроса дальше. Преимущество запроса выше заключается в том, что при его использовании сначала объединяются все таблицы, при этом для объединения используются индексы. Нюанс в том, что этом запросе значения в выборке могут повторяться, если один и тот же товар относится к нескольким категориям.


Если предполагается хранить численные данные, то не стоит использовать тип varchar для этого. Это будет сильно хуже.

Спасибо.
Только в реальных условиях джоин с индексами, а дальше where не получается быстрее, чем where in.


Надо как-то оптимизировать запросы, которые > 5 секунд. Не понимаю, как сделать грамотно индексы в таблице.

Вот если у меня такая конструкция:

CREATE TABLE `s_options` (
`product_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`1` mediumint(8) unsigned NOT NULL DEFAULT '0',
`2` mediumint(8) unsigned NOT NULL DEFAULT '0',
`3` mediumint(8) unsigned NOT NULL DEFAULT '0',
`4` mediumint(8) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


Ключи вида
index(`1`,`product_id`)
создаются по мере необходимости фильтрации по полям 1,2,3.....

Выборка из этой таблицы делается без order by, group by.

2 вида запросов к этой таблице:
1. Когда надо для 1 товара получить значения опций
SELECT * FROM s_options WHERE product_id = 10;

2. Когда надо получить опции для фильтров
SELECT product_id, 1,2,3,4.... #в зависимости от нужных для фильтра опций
FROM s_options WHERE 1
AND `1` in (2,3,4), AND `2` in(100,200);

Вот этот второй запрос работает сам по себе, когда нужны сами значения опций и в качестве подзапроса для других запросов, когда нужно product_id получить у товаров с определенными значениями опций.

Вот как тут лучше индексы построить?

 

[Роман Дмитриев]

На второй запрос нужен составной индекс по полям `1` и `2`. При создании составного индекса надо обратить внимание на уникальность значений в полях `1` и `2` и в зависимости от этого сделать порядок этих полей в индексе. При использовании составного индекса движок БД сначала выполняет поиск по первому полю в составном индексе, затем по второму. Что я имею в виду. Рассмотрим подробный пример на таблице `s_options`.
Например, поле `1` содержит значения (1,2,3,4,5), поле `2` содержит значения (100,200,300,400,500,600,700,800,900). Условимся, что все данные расположены в таблице равномерно (на практике вам надо будет проанализировать количество значений в таблице для планирования индекса). Стоит задача ускорить выполнение запроса

SELECT product_id, 1,2,3,4
FROM s_options WHERE 1
AND `1` in (2,3,4), AND `2` in (100,200);

Допустим, есть составной индекс и обозначен полями в порядке (1,2).
Итак, при использовании индекса, движок БД сначала просматривает в индексе первое поле по очерёдности, затем второе. Так как в поле `1` значения от 1 до 5, то 80% выборки будет попадать под это условие, а затем движок перейдёт к просмотру второго поля. Это неправильное использование индекса, так большое количество времени будет затрачено на то, чтобы отфильтровать всего лишь 20% выборки.

Теперь рассмотрим другой случай, когда составной индекс обозначен полями в порядке (2,1).
При использовании индекса, движок БД сначала просматривает в индексе первое поле по очерёдности `2`, затем поле `1`. Так как в поле `2` значения от 100 до 900, то под условие выборки попадёт 22% выборки, что существенно ограничит количество записей по фильтру, а затем движку останется отсеять малую часть из этих записей с помощью поля `1`.
Надеюсь, я понятно объяснил, как спланировать составной индекс.

Ещё вопрос. Если БД планируется с большим количеством поисковых запросов, может стоит рассмотреть движок MyISAM?