проблема с индексами

[Grapefruit Lips]

проблема с индексами

Здравствуйте,

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

стоит memcached, но нагрузка на mysql на сервере постоянно в пределах от 60 до 80-90%.
размер базы около 90MB

таблиц около 10, но в данном случае важны 2, это users и posts:

[SQL]
CREATE TABLE `posts` (
`id` int(11) NOT NULL auto_increment,
`user_id` int(11) NOT NULL default '0',
`date` datetime NOT NULL default '0000-00-00 00:00:00',
`type` varchar(10) NOT NULL,
`notes` int(11) NOT NULL default '0',
`title` text NOT NULL,
`photo` text NOT NULL,
`photourl` text NOT NULL,
`link` text NOT NULL,
`linkname` text NOT NULL,
`quote` text NOT NULL,
`quotesource` text NOT NULL,
`audio` text NOT NULL,
`video` text NOT NULL,
`post` text NOT NULL,
`ban` int(1) NOT NULL default '0',
UNIQUE KEY `id` (`id`),
KEY `uiddy` (`user_id`),
KEY `typ` (`type`(4)),
KEY `notes` (`notes`),
KEY `ban` (`ban`),
KEY `photo` (`photo`(5)),
KEY `date` (`date`),
FULLTEXT KEY `post` (`post`),
FULLTEXT KEY `quote` (`quote`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=152390 ;
[/SQL]


[SQL]
CREATE TABLE `users` (
`id` int(11) NOT NULL auto_increment,
`site` varchar(32) NOT NULL,
`email` varchar(100) NOT NULL,
`password` varchar(50) NOT NULL,
`btitle` varchar(255) NOT NULL,
`about` text NOT NULL,
`logo` int(1) NOT NULL default '0',
`avatar` varchar(50) NOT NULL,
`avatar_id` int(11) NOT NULL default '0',
`postslimit` int(11) NOT NULL default '10',
`joindate` datetime NOT NULL default '0000-00-00 00:00:00',
`lastupdate` datetime NOT NULL default '0000-00-00 00:00:00',
`ip` varchar(50) NOT NULL,
`banned` int(1) NOT NULL default '0',
UNIQUE KEY `id` (`id`),
KEY `sity` (`site`(8)),
KEY `lastupdate` (`lastupdate`),
KEY `avbn` (`avatar`(4),`banned`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3027 ;
[/SQL]


на сайте выводятся посты пользователей
(т.е. выводятся посты тех, за которыми *следит* пользователь)


# Query_time: 12 Lock_time: 0 Rows_sent: 56897 Rows_examined: 304497
[SQL]
SELECT posts.id AS postid, title, date, type, totalnotes, photo, photourl, link, linkname, quote, quotesource, audio, video, post, users.id AS follower_id, users.site AS site, users.avatar AS ava, users.avatar_id AS ava_id
FROM posts
LEFT JOIN users ON posts.user_id=users.id
WHERE posts.id<151609 AND user_id IN(834,840,843,865,866,906,908,911,915,...........)
OR user_id='14' AND posts.id<151609
GROUP BY posts.id
ORDER BY posts.date DESC;
[/SQL]
то что в IN() - это ID пользователей, за которыми человек следит (они отдельным запросом, также через memcache хранятся в переменной какое-то время)
14 - это ID самого пользователя, чтобы было видно и свои посты.

или вот такой запрос:
# Query_time: 11 Lock_time: 0 Rows_sent: 11781 Rows_examined: 60600
[SQL]
SELECT posts.id AS iddy, user_id, notes, date, users.site AS website, users.btitle AS btitle, photo FROM posts
LEFT JOIN users ON posts.user_id=users.id
WHERE photo!='' AND ban='0' AND notes>1
GROUP BY posts.id ORDER BY date DESC;
[/SQL]

помогите правильно раставить индексы, если дело в них?
или нужно ещё оптимизировать запрос?

спасибо

 

[kotpl_]

posts.id < 151609 2-а раза в первом запросе и какой explain?

-~{}~ 25.12.09 23:44:

Поменяй местами posts и users, чтобы было

FROM users
LEFT JOIN posts

 

[Grapefruit Lips]

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
1 	SIMPLE 	posts 	ALL 	id,uiddy 	NULL 	NULL 	NULL 	133987 	Using where; Using temporary; Using filesort
1 	SIMPLE 	users 	eq_ref 	id 	id 	4 	posts.user_id 	1

если поменять на
FROM users
LEFT JOIN posts

то ... Запрос занял 41.9554 сек

 

[kotpl_]

а сорри сразу не увидел, а зачем нужен left join ? Из-за этого происходит полное сканирование таблицы posts, а она я так понимаю не маленькая.

 

[Grapefruit Lips]

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

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

 

[kotpl_]

все таки полное сканирование происходит не через left join это было бы если бы было дополнительное условие or users.id is null. Здесь нет индекса на posts.user_id. Но я все равно не понял зачем нужен left join. А какой explain на from users join posts ?

 

[Grapefruit Lips]

а разве индекс на posts.user_id не - KEY `uiddy` ( `user_id` ) ?

explain на from users join posts:

id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra 
1  	SIMPLE  	users  	range  	id  	id  	4  	NULL  	234  	Using where; Using temporary; Using filesort
1 	SIMPLE 	posts 	ref 	id,uiddy 	uiddy 	4 	users.id 	67 	Using where

только почему, интересно Запрос занял 3.7240 сек

 

[kotpl_]

а разве индекс на posts.user_id не - KEY `uiddy` ( `user_id` ) ?
Да Вы правы, я уже увидел. Просто иногда СУБД решает что индексный доступ более дорогостоящий чем полное сканирование и делает полное сканирование. Попробуйте при вашем запросе сделать:
1. analyze table posts (для обновления информации о распределении значений ключей)
2. Использовать force index для posts то есть FROM posts заменить на FROM posts force index(user_id)

-~{}~ 26.12.09 01:08:

А зачем делать GROUP BY posts.id если в селекте нет агрегатных функций? Если его убрать может быть с extra уберем - Using temporary; (создание временной таблицы)?

 

[filipchuk]

не вникал в запрос, скажу только по структуре таблиц:
1) возможно, есть смысл отделить текстовые поля в таблицах "Сообщения" и "Пользователи" в отдельные таблицы
2) создано много одно-колоночных индексов, которые негативно влияют при операциях записи в таблицу (а движок MyIsam блокирует таблицу в момент записи) и не дают хорошего эффекта при чтении, а нет индексов на нескольких колонках, которые могут быть даже покрывающими и соответственно ефективными

Если высказался не в тему, пусть модеры снесут мой пост

 

[Grapefruit Lips]

kotpl_
analyze - ok
убрав GROUP BY, заметно уменьшилось время выполнения запроса (ов)
т.к. сейчас время позднее, проверить при бОльшой нагрузке можно будет завтра


filipchuk
а как найти эффективные индексы? я так понимаю это зависит от конкретных запросов?


например в запросе:
[SQL]
SELECT posts.id AS iddy, user_id, notes, date, users.site AS website, users.btitle AS btitle, photo FROM posts
JOIN users ON posts.user_id=users.id
WHERE photo!='' AND ban='0' AND notes>1
ORDER BY date DESC;
[/SQL]
будет ли эффективен индекс по трём полям: photo, ban и notes?

table `posts` indexes:

id  	 UNIQUE  	 134553
user_id	INDEX 	         2008
type 	INDEX 	         7
notes 	INDEX 	         49
ban 	INDEX 	         2
photo 	INDEX 	         67276
date 	INDEX 	         134553 
post 	FULLTEXT 	 134553
quote 	FULLTEXT 	 67276

и нужны ли вообще 2 последних индекса на fulltext полях?

 

[kotpl_]

Все зависит от запросов. Исходя из вашего запроса сделайте выборки:
1. select count(*) from posts where photo!=''

2. select count(*) from posts where ban='0'

3. select count(*) from posts where notes>1

Полученные значения разделите на select count (*) from posts и умнож. на 100%. Если результат:

а) <0,5% строк - то индексный доступ
б) 0,5-20% необходимо рассмотреть дополнительные условия
в) >20% - полное сканирование

-~{}~ 26.12.09 11:00:

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