Блог

В той же базе данных у меня есть таблица messages , столбцы которой: id , title , text я хочу. Я хочу, чтобы в таблице были только те записи, в которых title нет записей lastlogon , которые затем называются эквивалентом username заголовка.

Я использовал эту команду SQL в PHP, обычно для ее запуска требовалось 2-3 секунды:

SELECT DISTINCT * FROM messages WHERE title NOT IN (SELECT username FROM lastlogon) LIMIT 1000

Все было хорошо, пока в таблице lastlogon не появилось около 80% таблицы значений messages . В сообщениях около 8000 записей, в lastlogon около 7000. Теперь для его прохождения требуется от минуты до 2 минут. MySQL разгоняется до очень высокой загрузки процессора.

Я попробовал следующее, но не повезло сократить время:

SELECT id,title,text FROM messages a LEFT OUTER JOIN lastlogon b ON (a.title = b.username) LIMIT 1000

Почему вдруг для такого небольшого количества записей требуется так много времени? Я несколько раз пытался перезапустить mysql и apache. Я использую debian linux.

Редактировать: вот структуры

 --
-- Table structure for table `lastlogon`
--

CREATE TABLE IF NOT EXISTS `lastlogon` (
  `username` varchar(25) NOT NULL,
  `lastlogon` date NOT NULL,
  `datechecked` date NOT NULL,
  PRIMARY KEY (`username`),
  KEY `username` (`username`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

-- --------------------------------------------------------

--
-- Table structure for table `messages`
--

CREATE TABLE IF NOT EXISTS `messages` (
  `id` smallint(9) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `email` varchar(50) NOT NULL,
  `text` mediumtext,
  `folder` tinyint(2) NOT NULL,
  `read` smallint(5) unsigned NOT NULL,
  `dateline` int(10) unsigned NOT NULL,
  `ip` varchar(15) NOT NULL,
  `attachment` varchar(255) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(300) NOT NULL,
  `error` varchar(500) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `title` (`title`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=9010 ;
  

Редактировать 2

Отредактированная структура с новыми индексами. После размещения индекса для обоих messages.title и lastlogon.username я получил следующие результаты:

Отображение строк 0-29 (всего 623, запрос занял 74,4938 сек)

Во-первых: замените ключ в title на составной ключ в title id

 ALTER TABLE messages DROP INDEX title;
ALTER TABLE messages ADD INDEX title (title, id);
  

Теперь измените select на:

 SELECT m.* FROM messages m
LEFT JOIN lastlogon l ON (l.username = m.title)
WHERE l.username IS NULL
-- GROUP BY m.id DESC -- faster replacement for distinct. I don't think you need this.
LIMIT 1000;
  

Или

 SELECT m.* FROM messages m
WHERE m.title NOT IN (SELECT l.username FROM lastlogon l)
-- GROUP BY m.id DESC -- faster than distinct, I don't think you need it though.
LIMIT 1000;
  

Еще одна проблема с медлительностью — это SELECT m.* часть.
Выбирая все столбцы, вы заставляете MySQL выполнять дополнительную работу.
Выберите только те столбцы, которые вам нужны:

 SELECT m.title, m.name, m.email, ......
  

Это также ускорит запрос.

Есть еще один трюк, который вы можете использовать:
Замените ограничение 1000 датой отсечения.

Шаг 1: Добавьте индекс в метку времени (или любое другое поле, которое вы хотите использовать для отсечения).

 SELECT m.* FROM messages m
LEFT JOIN lastlogon l ON (l.username = m.title)
WHERE (m.id > (SELECT MIN(M2.ID) FROM messages m2 WHERE m2.timestamp >= '2011-09-01'))
  AND l.username IS NULL
-- GROUP BY m.id DESC -- faster replacement for distinct. I don't think you need this.
  

Я предлагаю вам добавить индекс messages.title . Затем попробуйте снова запустить запрос и проверить производительность.