Форум PHP программистов

[ Поиск ] - [ Пользователи ] - [ Календарь ]

Полная Версия: индекс

forza

1.05.2012 - 18:36

Доброе время суток.
Ребят, помогите создать индекс к запросу. Вот уже целый день мучаюсь и никак не получается. Перепробовал кучу вариантов. Все время присутствует "filesort".

Запрос

explain SELECT * FROM `gift`  WHERE whom=123 AND action='now' ORDER BY visit, date DESC LIMIT 0, 5

Структура таблицы

CREATE TABLE `gift` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `who` int(10) unsigned NOT NULL,
 `whom` int(10) unsigned NOT NULL,
 `eventName` varchar(255) NOT NULL,
 `eventID` int(10) unsigned NOT NULL,
 `date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
 `action` enum('now','later','notsent') NOT NULL,
 `visit` tinyint(1) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `ix_list` (`whom`,`action`,`visit`,`date`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8

Дамп таблицы

Свернутый текст

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
--
-- Хост: localhost
-- Время создания: Май 01 2012 г., 14:33
-- Версия сервера: 5.5.16
-- Версия PHP: 5.3.8

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- База данных: `test`
--

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

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

CREATE TABLE IF NOT EXISTS `gift` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`who` int(10) unsigned NOT NULL,
`whom` int(10) unsigned NOT NULL,
`eventName` varchar(255) NOT NULL,
`eventID` int(10) unsigned NOT NULL,
`date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' ON UPDATE CURRENT_TIMESTAMP,
`action` enum('now','later','notsent') NOT NULL,
`visit` tinyint(1) NOT NULL,
PRIMARY KEY (`id`),
KEY `ix_list` (`whom`,`action`,`visit`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

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

INSERT INTO `gift` (`id`, `who`, `whom`, `eventName`, `eventID`, `date`, `action`, `visit`) VALUES
(1, 1234, 123, 'giftcards', 1, '2012-05-01 06:43:25', 'now', 0),
(2, 1235, 123, 'giftcards', 2, '2012-03-13 11:26:32', 'now', 0),
(3, 1236, 123, 'giftcards', 2, '2012-03-20 11:26:32', 'now', 1),
(4, 12367, 123, 'giftcards', 4, '2011-12-21 11:26:32', 'now', 1),
(5, 123678, 123, 'giftcards', 4, '2011-12-30 11:26:32', 'now', 0);

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Спустя 1 час, 9 минут, 4 секунды (1.05.2012 - 17:45) vagrand написал(а):

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

Спустя 1 час, 35 минут, 18 секунд (1.05.2012 - 19:20) forza написал(а):

Окей! Попробую

Вставил 1.5к. строк. Все равно присутствует filesort

Спустя 13 минут, 27 секунд (1.05.2012 - 19:34) vagrand написал(а):

Результат explain-а покажи

Спустя 5 минут, 49 секунд (1.05.2012 - 19:40) forza написал(а):

Запрос
explain SELECT * FROM `gift` WHERE whom=123 AND action='now' ORDER BY visit, date DESC LIMIT 5, 5

Результат

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gift ALL ix_list NULL NULL NULL 1057 Using where; Using filesort

Запрос без "DESC" после `date`
explain SELECT * FROM `gift` WHERE whom=123 AND action='now' ORDER BY visit, date LIMIT 5, 5

Результат

id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE gift index ix_list ix_list 10 NULL 10 Using where

Спустя 17 часов, 27 минут, 7 секунд (2.05.2012 - 13:07) vagrand написал(а):

Про такую особенность я не знал. Значит сортируй все по ASC а потом уже в php досортировывай.

Спустя 1 час, 34 минуты, 54 секунды (2.05.2012 - 14:42) forza написал(а):

Я уже расматривал такой вариант, но лишний функционал не облегчает работу сервера.

Спустя 1 час, 50 минут, 51 секунда (2.05.2012 - 16:32) vagrand написал(а):

тогда попробуйте создать ключ только на три поля: `whom`,`action`,`visit` и посмотрите что вам напишет explain

Спустя 15 часов, 58 минут, 46 секунд (3.05.2012 - 08:31) forza написал(а):

Да пробовал я и так и сяк -)
Вот, если интересно, что на другом форуме посоветовали и что я сделал.
http://sqlinfo.ru/forum/viewtopic.php?pid=33091

_____________
Заработок для веб-разработчиков: CodeCanyon
Мое Портфолио

Быстрый ответ:

Здесь расположена полная версия этой страницы.