» Оптимизация запроса MySQL

Попробовал про этот ОЛАП почитать.... Что то сложно... И вообще ни чего не понятно.... И в первую очередь - какое он имеет отношение к моему вопросу?

может это вас внутренний софт какой-нибудь

Да... проблема... Ну что же, отрицательный результат тоже результат. Перестану пока об этом думать. Время покажет.

Цитата:

Что то сложно... И вообще ни чего не понятно....

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

Добрый день.
Подскажите пожалуйста с запросом. А то кучей запросов я такое могу сделать, а вот одним это можно сделать? Мне нужно что бы получилось:

НОВОСТИ
Добавить
Редактировать
ПРАЙС
Добавить
Редактировать
НАСТРОЙКИ
Курс Евро
Разделы

... и т.д.

Код: --
-- Структура таблицы `Menu`
--

CREATE TABLE `Menu` (
`id` int(11) NOT NULL auto_increment,
`parent_id` int(11) NOT NULL,
`name` tinytext NOT NULL,
`href` tinytext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=14 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=14 ;

--
-- Дамп данных таблицы `Menu`
--

INSERT INTO `Menu` VALUES (1, 0, 'Новости', '');
INSERT INTO `Menu` VALUES (2, 0, 'Прайс', '');
INSERT INTO `Menu` VALUES (3, 0, 'Настройки', '');
INSERT INTO `Menu` VALUES (4, 0, 'Выход', '');
INSERT INTO `Menu` VALUES (5, 1, 'Добавить', '?p=news_add');
INSERT INTO `Menu` VALUES (6, 1, 'Редактировать', '?p=news_edit');
INSERT INTO `Menu` VALUES (7, 2, 'Добавить', '?p=price_add');
INSERT INTO `Menu` VALUES (8, 2, 'Редактировать', '?p=price_edit');
INSERT INTO `Menu` VALUES (9, 3, 'Курс Евро', '?p=exchange');
INSERT INTO `Menu` VALUES (10, 3, 'Разделы', '?p=razdel');
INSERT INTO `Menu` VALUES (11, 3, 'Подразделы', '?p=podrazdel');
INSERT INTO `Menu` VALUES (12, 3, 'Цвета', '?p=color');
INSERT INTO `Menu` VALUES (13, 3, 'Сменить пароль', '?p=password');

получаете весь список одни запросом и с помощью php форматируете как хотите, это не задача mysql

Добрый день еще раз.
Узнал про внешние ключи. Классная штука, ну вот что то я наверное не до понимаю. Есть три таблицы Раздел, Подраздел и Прайс.
Хочу чтобы если я удалю подраздел, то все продукты касающиеся этого подраздела тоже удалялись. И если я удаляю Раздел, то что бы все разделы и продукты, которые принадлежат этому разделу - удалялись.

Делал по разному, но поведение совсем не могу понять, то удаляется не то что надо, то не добавляется.. что то не понимаю я.

Как связать таблицы?


Код: --
-- Структура таблицы `Razdel`
--

CREATE TABLE `Razdel` (
`id` int(10) unsigned NOT NULL auto_increment,
`name` tinytext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=4 ;

--
-- Структура таблицы `Podrazdel`
--

CREATE TABLE `Podrazdel` (
`id` int(11) NOT NULL auto_increment,
`name` tinytext NOT NULL,
`razdel_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=cp1251 AUTO_INCREMENT=5 ;

--
-- Структура таблицы `Price`
--

CREATE TABLE `Price` (
`id` int(11) NOT NULL auto_increment,
`razdel_id` int(11) default NULL,
`podrazdel_id` int(11) default NULL,
`img` tinytext NOT NULL,
`articul` tinytext NOT NULL,
`name` tinytext NOT NULL,
`color_id` int(4) default NULL,
`long` tinyint(4) NOT NULL,
`col` tinyint(4) NOT NULL,
`price` float default NULL,
`new` enum('0','1') NOT NULL,
PRIMARY KEY (`id`),
KEY `podrazdel_id` (`podrazdel_id`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251 AUTO_INCREMENT=1 ;

ON DELETE какой выбирал?

http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html

тебе нужно ON DELETE CASCADE

Ну да.. я его и делал.. просто к какой таблице применять его?

Не могу разобраться со связями

Цитата:

Ну да.. я его и делал.. просто к какой таблице применять его?

если не можешь с большими таблицами - потренируйся для начала на маленьких.
ON DELETE нужен для связанной таблицы. тогда при удалении из основной - всё будет снесено и из связанной.

вопро к профи в оптимизации..
как лучше сделать и правильнее.. ?


Код:

table_1
table_1_id
table_1_pole_1
table_1_pole_2
table_1_pole_3

table_2
table_2_id
table_2_pole_1
table_2_pole_2
table_2_pole_3
table_1_id

kolyas, быстрее должно быть с "односторонним" (не full) join-ом и перечислением всех нужных столбцов

substrackto
односторонний это left, right join ?

substrackto
c чего ты взял, что у него там FULL? в первом варианте запроса используется семантический эквивалент конструкции INNER JOIN.

Добавлено:
kolyas
разница между скорострельностью обоих запросов, которые, насколько можно заметить, отличаются лишь более короткими именами и явным перечислением - будет порядка ошибки измерений (разве что ты там не хранишь блобы с десятками мегабайт). так что ты паришься не о том.

zerkms
блобы не храню в основном varchar(255) и int(10)

а о чем надо париться ..?

kolyas
париться нужно о существующих проблемах. у тебя уже есть конкретные проблемы с производительностью субд?

zerkms
нет..
по двум причинам ..
1 все еще стадии запуска.
2 база - каталог для очень небольшого сайта..

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

kolyas
оптимизация бд до реальных нагрузок - это очень сложная наука. нужно представлять как работает субд, как хранит данные, как читает, как строит запросы и использует индексы, как использует буфера и кэш, итд итп.
нельзя научиться этому по советам, хотя бы потому - что панацеи и общих советов не существует.

по поводу всесобирающего запроса я понял...
а вот как поступать когда из 5-6 таблиц нужно выбрать данные с большим количеством условий

как правильно поступать - выбрать все данные и раскидать с помощью php, или все таки делать все условия в самом мускуле ?

Цитата:

как правильно поступать - выбрать все данные и раскидать с помощью php, или все таки делать все условия в самом мускуле ?

как правило, лучше доверить такую работу серверу БД: меньше траффика, меньше загрузка app-сервера. однако, БД не всегда знает, какими объёмами придётся оперировать: например в результате какого-то подзапроса должен получиться rowset из 2-3 строк, а БД посчитает, что будет несколько тысяч, и запрос будет выполняться по очень неоптимальному сценарию. именно в этом случае имеет смысл разделить задачу на несколько запросов и разрулить всё на app-сервере.

Цитата:

например в результате какого-то подзапроса должен получиться rowset из 2-3 строк, а БД посчитает, что будет несколько тыся

БД не ошибается просто так. если запрос выполняется медленно - то или запрос кривой, или схема кривая, или (довольно редко) и вправду программист умнее оптимизатора субд.

вопрос

объединение с доп условием как лучше использовать

Код:
Select
*
FROM
kat_tv LEFT JOIN kat_model ON kat_tv.model_id= kat_model.id AND kat_model.visible = 1

Цитата:

объединение с доп условием как лучше использовать

логичнее первый вариант: он поясняет разработчику логику запроса. серверу БД абсолютно пофиг: он построит один и тот же план.

Цитата:

и еще .. что "тяжелее" для mysql подзапрос или JOIN ?

как правило, подзапрос тяжелее. особенно, если подзапрос с условием, зависящим от значения поля текущей записи. это вообще ж.па для БД. в таких случаях лучше сделать один группирующий подзапрос. если же подзапрос для каждой записи должен дать один и тот же rowset, такой подзапрос можно смело использовать. А вообще, всё зависит от количества записей в связанных таблицах.

три разных запроса, дающих один и тот же результат:

Код: SELECT u.id, count(p.id) cnt FROM users u
INNER JOIN posts p ON p.user_id=u.id
GROUP BY u.id

Цитата:

логичнее первый вариант: он поясняет разработчику логику запроса. серверу БД абсолютно пофиг: он построит один и тот же план.

неправда. планы будут разные (ибо LEFT JOIN)
во втором запросе, благодаря зависимости от kat, LEFT легко можно заменить на INNER и ничего не изменится.
в первом же запросе, как и ожидается, правая половина будет добита NULL'ами, если условие объединения не выполнится.

APTEM
zerkms

INNER легче LEFT ?

у меня в базе, данные могут отсуствовать и подменяться NULL'ями только в одном месте, может есть тогда смысл убрать везде LEFT ?

kolyas
INNER легче, чем LEFT
но они делают разную работу - сам понимаешь. если нужно обычное пересечение множеств - тогда INNER. иначе - LEFT.

а если вместо INNER сравнить по id

WHERE t1.id = t2.id

?

zerkms

вернусь к запросам из поста выше
если их переделать вот так.. как я понял, они станут полностью одинаковыми, так как серер БД все равно все условия сам выкидывает в WHERE ?


Код: Select
*
FROM
kat_tv INNER JOIN kat_model ON kat_tv.model_id= kat_model.id AND kat_model.visible = 1

kolyas
нет, запросы !=

по выборке они одинаковые, но оптимизироваться будут по-разному (если, конечно оптимизатор не настолько гениальный, чтобы самому упростить, в чём я очень сомневаюсь)
первый вариант должен быть быстрее второго. вообще не вижу смысла часть условия объединения переносить во WHERE, где ему банально не место.

Добавлено:

Цитата:

так чтобы выбиралось по 4 строки из таблицы SV для каждого TV

завести переменную
SET @I:=0;

в запросе её увеличивать:
SELECT ..., @I:=@I+1

группировать по tv + остатку от деления I на 4:
GROUP BY `tv`.`id`, @I % 4

но вообще этот запрос очень медленный, и за такой запрос я бы подчинённым руки оторвал