1000 колонок в базе MySQL

Ну и что вам помешало сделать это сразу? Программер боится джоинов и подзапросов ?

Если у вас 99% пустых полей, то при декомпозиции явно меньший объем будет и, как следствие, большая скорость.

Имели аналогичную проблему на таблице примерно с тем же количеством строк, но существенно меньшим количеством колонок (порядка 200).

Первое. Обнаружилось что использование tinyint зачастую оверкилл и в принципе из 200 можно сделать примерно 75 колонок.

Второе. Индексы в такой ситуации один фиг не работают если выборка сразу по всем полям, слишком много полей, а у mysql ограничение есть и по индексам и по полям. То есть все равно идет скан таблицы. Поэтому 2 запроса по 2 таблицам в принципе дадут сравнимую скорость. Или 4 по четерым. А если разносить таблицы по какому-то признаку (например первое поле значение до 64, от 64 до 128, от 128 до 194, от 194 до 256), то получится некая "эмуляция" индексов. по сути.

Третье. Как подпункт второго, можно посмотреть на MERGE тип таблиц.

В целом, если данные уже рабочие и актуальные, то имхо есть смысл сделать анализ данных. Абстрактно дать хороший совет трудно. Почти наверняка выявятся какие-то закономерности, которые позволят или разумно расставить индексы или разумно разделить на отдельные таблицы одну большую или разумно уменьшить кол-во колонок. Если большинство выборок затрагивает не больше 16 колонок (ради индексов), или колонки некоторые не содержат больше 128 разных значений (для уменьшений кол-ва колонок) или колонки имеют какую-то корреляцию относительно друг друга, это уже повод для улучшения ситуации. Пусть даже какой-то из вариантов будет не 100%-ным, но даже оптимизация в том плане, что бы 80% хитов более легко разрешалось - уже хорошо.

P.S.: Полностью согласны, что (rec_id, col_id, value) для Вашей задачи может не подходить. По сути мы тоже перешли к "однолинейной" таблице из такой структуры. Именно потому, что сложные выборки вида ( col1=5 && col2<>5 or col3>250) порождают дикое кол-во запросов при структуре вида (rec_id, col_id, value) . Всё сильно ускорилось.

В настоящий момент идёт проектирование базы и приложения. Так что свобода действий - полная. Хочется выбрать быстры вариант и при этом более удобный в разработке. Удобство в разработке приоритетно - скорость поиска по базе не критична.

MERGE не выход. Он разбивает таблицу по строкам, а не по столбцам. JOIN мог бы помочь в случае разрезки таблицы по столбцам на нескольких таблиц, но всё равно ведь полный скан получается. Ограничение на 64 JOINа вполне для меня приемлимо.

Кстати, не нашёл ограничения на число столбцов MySQL. Краем памяти помню, что 256, но встречал и утверждения про 1000 столбцов и т.д.

Слава, это ограничение (255) для столбцов типа ENUM и SET. Было в четвёртой версии, меняли ли в пятой - не знаю, не смотрел.

использовать представления (VIEW)

Почитал. Я так понимаю, что VIEW это лишь представление реальной таблицы. То есть создаёт удобство для поиска. Проблемы удобства хранения данных и скорости доступа вроде не решает.

Не таблицы, а таблиц (в текущей версии поддерживатеся 32 вложенных запроса). По сути, когда мне нужна денормализованная таблица - я строю её представление по нескольким таблицам. Если у используемых таблиц правильно прописаны индексы и таблицы связываются по нормальным условиям, то всё работает достаточно быстро. Помимо этого, в качестве поля в такой таблице может выступать функция.

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

Ну а если скорость не критична, вообще о чем вопрос. Побольше памяти и будет вам полный перебор работать быстро.

Что вы в данном случае называете запросами? Кол-во селектов или реальных проходов по таблице при обработке? У меня к сожалению, нет возможности сравнить скорость на ОЧЕНЬ большой базе, но вообще запрос такого вида

SELECT *

FROM `user_table_values`

WHERE

  IF (`col_id`='1',

      IF (`value`='синий',1,0), 0)

AND

  IF (`col_id`='2',

      IF (`value`>=40 AND `value`<=115,1,0),0)

AND

   IF (`col_id`='3',

      IF (`value`<90,1,0),0)

к нормализованной таблице отрабатывает на моем опыте довольно быстро. Во всяком случае не дольше других, более простых запросов (в таблице около 120 тысяч записей).

Другой вопрос, что потом работать с нелинеаризованной структурой уже не так удобно.

Бывало, что из 32 полей получалось сделать 32 бита и заменить одним, может есть такие...