Версия для печати темы
Нажмите сюда для просмотра этой темы в оригинальном формате
Форум программистов > MySQL > оптимизация запроса\таблиц


Автор: fregs 22.11.2007, 02:27
собираю несколькими запросами кол-во результатов поиска в нескольких разделах (1-10), по запросу на раздел,

имеем вот такой вот корявенький запрос
Код

SELECT count(fs_files.id) as c FROM fs_dirs,fs_files
WHERE fs_files.dirid=fs_dirs.dirid AND fs_dirs.fcount>0
AND fs_dirs.dirname LIKE '$sd%'
AND fs_files.filename LIKE '%$sSearch%'

получаем примерно 1-2 секунды на запрос


2 таблы
Код

CREATE TABLE IF NOT EXISTS `fs_dirs` (
`dirid` mediumint(8) NOT NULL auto_increment,
`dirname` varchar(255) NOT NULL default '',
`pid` mediumint(8) unsigned NOT NULL default '0',
`fcount` int(10) NOT NULL default '0',
PRIMARY KEY (`dirid`),
KEY `dirname` (`dirname`)
) ENGINE=MyISAM DEFAULT


Код

CREATE TABLE IF NOT EXISTS `fs_files` (
`id` mediumint(8) NOT NULL auto_increment,
`cat` enum('poly','pict','themes','javag','symb','video','javas','mophun') NOT NULL default 'poly',
`dirid` mediumint(8) unsigned NOT NULL default '0',
`filename` varchar(230) NOT NULL default '',
`ext` char(3) default NULL,
`md5` varchar(32) default NULL,
`fsize` int(10) unsigned NOT NULL default '0',
`mtime` int(10) unsigned NOT NULL default '0',
`rate` int(10) unsigned NOT NULL default '0',
`opt` smallint(4) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `filename` (`filename`),
KEY `dirid` (`dirid`)
) ENGINE=MyISAM


fs_dirs 200 000 
fs_files 850 000 записей

куда бы копать в плане оптимизации? 

Автор: skyboy 22.11.2007, 12:01
моделируешь файловую систему?
а жесткие ссылки планируются? или структура представляет собой "чистое" дерево?
Цитата(fregs @  22.11.2007,  01:27 Найти цитируемый пост)
SELECT count(fs_files.id)

Цитата(fregs @  22.11.2007,  01:27 Найти цитируемый пост)
`fcount` int(10) NOT NULL default '0',

ты количество файлов и запоминаешь в БД, и вычисляешь при каждом запросе?

Автор: fregs 22.11.2007, 16:56
Цитата(skyboy @  22.11.2007,  12:01 Найти цитируемый пост)
моделируешь файловую систему?
а жесткие ссылки планируются? или структура представляет собой "чистое" дерево?

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

Цитата(skyboy @  22.11.2007,  12:01 Найти цитируемый пост)
ты количество файлов и запоминаешь в БД, и вычисляешь при каждом запросе? 

запомнено только кол.-во файлов в папке

а в запросе который указан первым я вычисляю кол-во файлов в разделе удовлетворяющих условиям поиска

Автор: skyboy 22.11.2007, 17:25
сделай 
Код

EXPLAIN SELECT count(fs_files.id) as c FROM fs_dirs,fs_files
WHERE fs_files.dirid=fs_dirs.dirid AND fs_dirs.fcount>0
AND fs_dirs.dirname LIKE '$sd%'
AND fs_files.filename LIKE '%$sSearch%'

и результат - сюда.

Автор: skyboy 22.11.2007, 17:25

M
skyboy		Тема намного ближе к MySQL, чем к PHP. Переношу...

Автор: fregs 22.11.2007, 22:56
мда задача на засыпку... как бы вывести, чтобы читать можно было


id select_type table    type    
1    SIMPLE    fs_dirs    ALL    
1    SIMPLE    fs_files    ref    

possible_keys    
PRIMARY,dirname 
dirid

key    key_len
NULL    NULL    
dirid    3

ref
NULL
focs_download.fs_dirs.dirid

rows    Extra
99066    Using where
5    Using where

Автор: skyboy 22.11.2007, 23:11
Цитата(fregs @  22.11.2007,  21:56 Найти цитируемый пост)
как бы вывести, чтобы читать можно было

вставил бы в цитату([ quote] ), автоперенос бы не сработал.
че-то мне кажется, что быстрее - никак. в столбце extra при применении explain отображаются потенциально узкие места: использование временных таблиц, дополнительная сортировка и т.д. У тебя же там ничего это нет. Только указание, что идет фильтрация по where. слушай, а если сделать полнотекстовый индекс? есть и http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html для работы b конструкция http://dev.mysql.com/doc/refman/5.0/en/create-index.html для создания(правда, поддерживается только для таблиц MyISAM)

Автор: fregs 23.11.2007, 02:04
Цитата(skyboy @  22.11.2007,  23:11 Найти цитируемый пост)
вставил бы в цитату([ quote] ), автоперенос бы не сработал.

Экспериментировал... поля разной длины... смещаются сильно, неудобно

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

Автор: skyboy 23.11.2007, 10:44
не мог бы сжатый, скажем, gzip'ом дамп таблиц скинуть?

Автор: Бонифаций 23.11.2007, 13:53
Цитата(fregs @  22.11.2007,  22:56 Найти цитируемый пост)
мда задача на засыпку... как бы вывести, чтобы читать можно было


Завершите запрос не по ; а по \G

Автор: fregs 25.11.2007, 00:31
Цитата(Бонифаций @  23.11.2007,  13:53 Найти цитируемый пост)
Завершите запрос не по ; а по \G

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


Цитата(skyboy @  23.11.2007,  10:44 Найти цитируемый пост)
не мог бы сжатый, скажем, gzip'ом дамп таблиц скинуть? 

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

думаю тему можно закрыть, при следующей доработке по другому спланирую таблицы, это должно решить часть проблем

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)