Форум пользователей MySQL

fuze · 25.01.2010 22:08:17

Не знаю с чего начать, поэтому спрошу так:
есть запрос:

SELECT DISTINCT con.*, DATE_FORMAT(con.pubdate, '%d-%m-%Y (%H:%i)') as fdate, u.nickname as author, u.login as author_login

                FROM zbl_content con, zbl_category cat, zbl_users u

                WHERE con.published = 1 AND con.showlatest = 1 AND con.user_id = u.id 

                      AND (con.is_end=0 OR (con.is_end=1 AND con.enddate >= NOW() AND con.pubdate <= NOW()))

                      AND (con.category_id = cat.id AND cat.NSLeft >= 4 AND cat.NSRight <= 21)

                ORDER BY con.pubdate DESC

                LIMIT 3

есть таблицы

CREATE TABLE `#zbl__content` (

`id` int(11) NOT NULL auto_increment,

`category_id` int(11) NOT NULL,

`user_id` int(11) DEFAULT '1' NOT NULL,

`pubdate` datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,

`enddate` date NOT NULL,

`is_end` int(11) NOT NULL,

`title` varchar(200) NOT NULL,

`description` text NOT NULL,

`content` longtext,

`published` int(11) DEFAULT '1' NOT NULL,

`hits` int(11) NOT NULL,

`meta_desc` text NOT NULL,

`meta_keys` text NOT NULL,

`showtitle` int(11) DEFAULT '1' NOT NULL,

`showdate` int(11) DEFAULT '1' NOT NULL,

`showlatest` int(11) DEFAULT '1' NOT NULL,

`showpath` int(11) DEFAULT '1' NOT NULL,

`ordering` int(1) NOT NULL,

`comments` int(11) DEFAULT '1' NOT NULL,

`is_arhive` int(11) NOT NULL,

`seolink` varchar(200) NOT NULL,

`canrate` int(11) DEFAULT '1' NOT NULL,

`pagetitle` varchar(255) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `seolink` (`seolink`),

  FULLTEXT `title` (`title`),

  FULLTEXT `content` (`content`)

) ENGINE=MyISAM AUTO_INCREMENT=49;

CREATE TABLE `#zbl__category` (

`id` int(11) NOT NULL auto_increment,

`parent_id` int(11),

`title` varchar(200) NOT NULL,

`description` text NOT NULL,

`published` int(11) NOT NULL,

`showdate` int(11) DEFAULT '1' NOT NULL,

`showcomm` int(11) DEFAULT '1' NOT NULL,

`orderby` varchar(30) DEFAULT 'date' NOT NULL,

`orderto` varchar(4) DEFAULT 'asc' NOT NULL,

`modgrp_id` int(11) NOT NULL,

`NSLeft` int(11) NOT NULL,

`NSRight` int(11) NOT NULL,

`NSLevel` int(11) NOT NULL,

`NSDiffer` varchar(11) NOT NULL,

`NSIgnore` int(11) NOT NULL,

`ordering` int(11) NOT NULL,

`maxcols` int(11) DEFAULT '1' NOT NULL,

`showtags` int(11) DEFAULT '1' NOT NULL,

`showrss` int(11) DEFAULT '1' NOT NULL,

`showdesc` int(11) NOT NULL,

`is_public` int(11) NOT NULL,

`photoalbum` text NOT NULL,

`seolink` varchar(200) NOT NULL,

  PRIMARY KEY  (`id`),

  UNIQUE KEY `seolink` (`seolink`)

) ENGINE=MyISAM AUTO_INCREMENT=23;

CREATE TABLE `#zbl__users` (

`id` int(11) NOT NULL auto_increment,

`group_id` int(11) DEFAULT '1' NOT NULL,

`login` varchar(100) NOT NULL,

`nickname` varchar(100) NOT NULL,

`password` varchar(100) NOT NULL,

`email` varchar(100) NOT NULL,

`icq` varchar(15) NOT NULL,

`regdate` datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,

`logdate` datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,

`birthdate` date DEFAULT '0000-00-00' NOT NULL,

`is_locked` int(11) NOT NULL,

`is_deleted` int(11) NOT NULL,

`rating` int(11) NOT NULL,

`points` int(11) NOT NULL,

`last_ip` varchar(15) NOT NULL,

`status` varchar(255) NOT NULL,

`status_date` datetime NOT NULL,

  PRIMARY KEY  (`id`)

) ENGINE=MyISAM AUTO_INCREMENT=28;

Вопрос: как правильно построить индексы при таком запросе?

p.s. просьба не пинать)))

paulus · 26.01.2010 12:32:53

О ужас Скорее всего, этому запросу уже ничто не поможет.
Попробуйте разбить его на осмысленные куски через UNION.

Если вот эти много-много условий обычно выполняются, то может
оказаться удобным выбирать сразу по дате, а потом проверять
уже соответствие остальным индексам. Т.е. что-то типа

SELECT t.*
FROM zbl_content co
JOIN ( страшный запрос ) t ON t.id = co.id
ORDER BY co.pubdate DESC
LIMIT 3

Если условия выполняются не почти всегда, сделает хуже.

Кстати, а почему таблицы называются, начиная с решетки?

fuze · 26.01.2010 14:39:37

paulus написал:
Попробуйте разбить его на осмысленные куски через UNION.

я не очень понял чт Вы имели ввиду(((

я пробовал делать общий индекс (имя - pubdate) на то, что участвует в WHERE
published
showlatest
user_id
is_end
enddate
pubdate
category_id

таблицы zbl_content, количество rows уменьшилось, но
| 1 | SIMPLE | con | ref | pubdate | pubdate | 8 | const,const | 8 | Using where; Using temporary; Using filesort |

paulus написал:
Кстати, а почему таблицы называются, начиная с решетки?

даже сам хз почему так скопипастилось - естественно решеток нет в начале названия.

paulus · 26.01.2010 15:09:14

Добавление индекса не спасет — у Вас там сложная логика с OR посередине.
Если избавиться от OR (например, разбить на два независимых запроса), то
можно выискивать уже индексы, но, скорее всего, они в этом случае все равно
останутся малоэффективными.

fuze · 26.01.2010 15:27:40

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

SELECT con.id

                          FROM zbl_uc_items con, zbl_uc_cats cat

                          WHERE con.category_id = cat.id AND (cat.NSLeft >= 13 AND cat.NSRight <= 14) AND con.published = 1

fuze · 01.02.2010 13:22:50

По поводу первого вопроса:
если из запроса убрать DISTINCT, а индексы сделать - общий индекс на published, showlatest, pubdate

ALTER TABLE `zbl_content` ADD INDEX ( `published` , `showlatest`, `pubdate` )

то файлсорт пропадает, остается Using where, что вроде как нормально.

Вопрос насколько это корректно?

Отредактированно fuze (01.02.2010 14:44:33)

paulus · 01.02.2010 14:29:28

При чтении таблицы используется максимум один индекс (есть некоторые исключения,
но их сложно использовать). Соответственно, нужно стараться делать так, чтобы запрос
максимально укладывался в индекс. В Вашем случае нужен один из индексов на con
(published, NSLeft), (published, NSRight), (NSLeft), (NSRight) — в зависимости от cardinality
published и количества выбираемых строк по NS*.

Также основным правилом при выборе индексов и построении запросов является исполь-
зование головы в каждом случае, а не тупой перебор индексов

--

Работает так, как Вам нужно, — значит, корректно.

fuze · 01.02.2010 14:39:08

paulus, спасибо за ответы.

Форум пользователей MySQL

#1 25.01.2010 22:08:17

Принцип построения индексов.

#2 26.01.2010 12:32:53

Re: Принцип построения индексов.

#3 26.01.2010 14:39:37

Re: Принцип построения индексов.

paulus написал:

paulus написал:

#4 26.01.2010 15:09:14

Re: Принцип построения индексов.

#5 26.01.2010 15:27:40

Re: Принцип построения индексов.

#6 01.02.2010 13:22:50

Re: Принцип построения индексов.

#7 01.02.2010 14:29:28

Re: Принцип построения индексов.

#8 01.02.2010 14:39:08

Re: Принцип построения индексов.

Board footer