Оптимизация MySQL огромной таблицы

Я пытаюсь получить некоторые улучшения скорости в определенном запросе SELECT. Ситуация такова: Есть (на моих глазах) огромный перекрестный стол. В настоящее время у него около 20 миллионов строк, но я ожидаю, что это будет расти. На основе этой таблицы пересечения мне нужно создать другую таблицу. Для этого мне нужно выполнить следующий запрос:

SELECT hugeCrossingTable.field3, otherTable.field1, hugeCrossingTable.field2 * otherTable.field3 AS someName
FROM hugeCrossingTable 
INNER JOIN otherTable ON hugeCrossingTable.field1 = otherTable.field2

Теперь это в настоящее время приводит к примерно миллиону строк. У меня уже есть индексы как по полю1 в 2 таблицах, но по-прежнему требуется 18 минут. Я думал о разделении таблицы, но тогда мне нужно было бы найти способ разделить данные, и поскольку это просто таблица пересечений, ничего не приходит в голову о том, как это сделать.

Любые идеи о том, как это можно оптимизировать?

Спасибо.

По запросу здесь создайте оператор:

CREATE TABLE  `hugeCrossingTable` (
  `field` int(11) NOT NULL,
  `field1` int(11) NOT NULL,
  `field2` double(10,5) DEFAULT NULL,
  `field3` int(4) DEFAULT NULL,
  KEY `field1` (`field1`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE  `otherTable` (
  `field` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `field1` int(10) unsigned NOT NULL,
  `field2` int(10) unsigned NOT NULL,
  `field3` decimal(5,2) NOT NULL,
  PRIMARY KEY (`field`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;

И вывод объяснения:

id, select_type, table              , type , possible_keys, key     , key_len, ref                , rows, Extra
1 , 'SIMPLE'   , 'otherTable'       , 'ALL', ''           , ''      , ''     , ''                 , 294 , ''
1 , 'SIMPLE'   , 'hugeCrossingTable', 'ref', 'field1'     , 'field1', '4'    , 'otherTable.field2', 69  , 'Using where'

Ответы

Ответ 1

Вот несколько примеров innodb, которые работают на больших таблицах ок. От 60 до 500 миллионов строк, которые демонстрируют преимущества хорошо разработанной таблицы innodb и как лучше всего использовать кластерные индексы (доступны только с innodb).

MySQL и NoSQL: помогите выбрать правильный вариант

60 миллионов записей, выберите записи за определенный месяц. Как оптимизировать базу данных?

Переназначение mysql позволяет сократить время и записать tmp на диск

Вы также захотите прочитать следующее:

http://dev.mysql.com/doc/refman/5.0/en/innodb-index-types.html

http://www.xaprb.com/blog/2006/07/04/how-to-exploit-mysql-index-optimizations/

После того как вы разобрали свои таблицы и оптимизировали конфигурацию innodb:

http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/

http://www.mysqlperformanceblog.com/2007/11/03/choosing-innodb_buffer_pool_size/

Вы можете попробовать что-то вроде:

start transaction;

insert into target_table (x,y) select x,y from source_table order by x,y;

commit;

Надеюсь, что это поможет.