Тачка:
PIV 3ггц, 2гб рам, 18 гб сказёвый винт,

Софт:
Freebsd 4.8, mysql 4.0.13


На машине ничего кроме сиквела не крутиться
Есть таблица в которой есть чуть более 3 милионов записей. Очень тормозно отрабатывают селекты. Упор оптимизации - для самых популярных запросов свой ключ.

Погите советом. Готов сменить базу данных (думаю о постгрес). Возможны более конструктивные изменения(MSSQL), но не хотелось бы.


Конфиг:
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 512M
max_allowed_packet = 16M
table_cache = 1024
sort_buffer_size = 16M
read_buffer_size = 16M
myisam_sort_buffer_size = 512M
thread_cache = 8
query_cache_size= 512M
max_heap_table_size=512M
max_tmp_tables=1024
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

Полный конфиг здесь: http://www.qqq.ru/my.cnf



Структура таблицы, неоптимизированны типы данных, их можно было бы сделать поменьше:
CREATE TABLE t_offers_big_83 (
id int(11) NOT NULL auto_increment,
visa tinyint(4) DEFAULT '0' NOT NULL,
allocation mediumint(5) DEFAULT '0' NOT NULL,
country mediumint(9) DEFAULT '0' NOT NULL,
resort mediumint(9) DEFAULT '0' NOT NULL,
duration mediumint(5) DEFAULT '0' NOT NULL,
meal mediumint(5) DEFAULT '0' NOT NULL,
roomtype mediumint(5) DEFAULT '0' NOT NULL,
price smallint(6) DEFAULT '0' NOT NULL,
operator smallint(5) DEFAULT '0' NOT NULL,
departure date DEFAULT '0000-00-00' NOT NULL,
arrival date DEFAULT '0000-00-00' NOT NULL,
roomview smallint(3) DEFAULT '0' NOT NULL,
roomsize smallint(3) DEFAULT '0' NOT NULL,
ch1from smallint(2) DEFAULT '0' NOT NULL,
ch1to smallint(2) DEFAULT '0' NOT NULL,
ch2from smallint(2) DEFAULT '0' NOT NULL,
ch2to smallint(2) DEFAULT '0' NOT NULL,
spo mediumint(7) DEFAULT '0' NOT NULL,
roomcat smallint(3) DEFAULT '0' NOT NULL,
ticket tinyint(1) DEFAULT '0' NOT NULL,
transfer tinyint(1) DEFAULT '0' NOT NULL,
insurance tinyint(1) DEFAULT '0' NOT NULL,
comment mediumint(7) DEFAULT '0' NOT NULL,
inf mediumint(7) DEFAULT '0' NOT NULL,
allspo mediumint(9) DEFAULT '0' NOT NULL,
active tinyint(1) DEFAULT '0' NOT NULL,
PRIMARY KEY (id),
KEY i_price (price),
KEY i_allocation (allocation),
KEY i_roomsize (roomsize),
KEY i_departure (departure),
KEY i_duration (duration),
KEY i_operator (operator),
KEY i_roomview (roomview),
KEY i_roomtype (roomtype),
KEY i_meal (meal),
KEY i_alrodedu (allocation, roomsize, departure, duration),
KEY i_alrodedupr (allocation, roomsize, departure, duration, price),
KEY i_aloprode (allocation, operator, roomsize, departure),
KEY i_all (allocation, duration, meal, roomtype, operator, departure, roomview, roomsize, ch1from, ch1to, ch2from, ch2to, allspo),
KEY id (id),
KEY i_country (country),
KEY i_resort (resort),
KEY i_corodedupr (country, roomsize, departure, duration, price),
KEY i_rerodedupr (resort, roomsize, departure, duration, price),
KEY i_rodedu (roomsize, departure, duration),
KEY i_rodedupri (price, roomsize, departure, duration, price)
);




Распределение данных:
allocation 618
country 1
resort 11
duration 14
meal 12
roomtype 39
price 10520
operator 28
departure 164
roomview 14
roomsize 13
ch1from 11
ch1to 16
ch2from 10
ch2to 16
allspo 592




Примеры выборок с таймингом:
mysql> create table
-> t_usertmp_c51ce410
-> select
-> t_offers_big_83.*
-> from
-> t_offers_big_83
-> use key(i_rodedu)
-> where roomsize='14'
-> and
-> departure>='2003-09-30'
-> and
-> (duration>='7' and duration<='14')
-> order by price asc
-> limit 0, 40
-> ;
Query OK, 40 rows affected (0.85 sec)
Records: 40 Duplicates: 0 Warnings: 0









mysql> create table
-> t_usertmp_c51ce410
-> select
-> t_offers_big_83.*
-> from
-> t_offers_big_83
-> use key(i_rodedu)
-> where roomsize='14'
-> and
-> departure>='2003-09-30'
-> and
-> (duration>='7' and duration<='14')
-> and
-> roomtype in (7)
-> and
-> meal in (1)
-> order by price asc
-> limit 0, 40
-> ;
Query OK, 40 rows affected (0.70 sec)
Records: 40 Duplicates: 0 Warnings: 0








mysql> create table
-> t_usertmp_c51ce410
-> select
-> t_offers_big_83.*
-> from
-> t_offers_big_83
-> use key(i_rodedu)
-> where resort in (0, 17)
-> and
-> roomsize='14'
-> and
-> departure>='2003-09-30'
-> and
-> (duration>='7' and duration<='14')
-> and
-> roomview in (29)
-> order by price asc
-> limit 0, 40
-> ;
Query OK, 0 rows affected (0.53 sec)
Records: 0 Duplicates: 0 Warnings: 0

Попробую побороться с типами данных и прибить индексы.

Темповая табличка нужна для полследующего джоина к ней словариков, со значением данных. Не темповая потому как была проблема памяти, артефакт вообщем то.

Иду на форум, где мне помогут :)