Форум MySQL

База вцелом должна хранить вот такие параметры:

fames VARCHAR(100) names VARCHAR(50) otmes VARCHAR(50) heigh BOOL statu VARCHAR(200) opf_firm VARCHAR(10) name_firm VARCHAR(200) gorod VARCHAR(60) adr_firm VARCHAR(200) mini_text VARCHAR(200)

При этом будет выполняться запрос типа:

SELECT * FROM articles ORDER BY fames, names,  otmes

Какие можно предпринять меры для обеспечения наибольшего быстродействия выполнения запроса?

Может быть, есть смысл разместить все данные в одной таблице и при этом проиндексировать поля fames, names, otmes (каждое)?

Или лучше разместить в двух связанных таблицах – в одной только fames, names, otmes, а в другой все остальное?

Нет, две таблицы - будет дольше. Лучше один индекс на трех столбцах fames, names, otmes. Но чудес лучше не ждать - range в лучшем случае. Лучше увеличивайте размер временной таблицы tmp_table_size. У вас большой объем таблицы получается?

Таблица получается до 100 Мб.

Разумеется, на одну страницу выводить всю базу нет необходимости - достаточно 100 записей. Так что полностью запрос получаетя такой:

SELECT * FROM articles ORDER BY fames, names,  otmes LIMIT 100

А индексацию тогда как осуществлять?

ALTER TABLE  `articles` ADD INDEX (`fames`) ALTER TABLE  `articles` ADD INDEX (`names`) ALTER TABLE  `articles` ADD INDEX (`otmes`)

Или так:

CREATE INDEX persona ON articles (fames(100), names(50),otmes(50) );

Если индексируются все символы, то их количество можно не указывать?

"Лучше увеличивайте размер временной таблицы tmp_table_size"
Не уверен, что эта опция доступна в настройках хостинга, но попробовать можно. Какая величина tmp_table_size была бы желательна в данной ситуации?

>Или так:
Да, по второму варианту.

>Если индексируются все символы, то их количество можно не указывать?
Можно не указывать, но чем меньше вы указываете значение, тем лучше - индекс меньше, поиск по нему осуществляется быстрее. А для индекса вся строка не нужна, нужны только первые символы, которые позволят отличить одну строку от другой.

>Не уверен, что эта опция доступна в настройках хостинга, но попробовать можно. Какая величина tmp_table_size была бы
>желательна в данной ситуации?
Да, на виртуальном хостинге, она скорее всего жестко задана. Начать можно с 64 или 128Мб. Это значение на все соединения, т.е. от количество соединений с базой данных объем оперативной памяти, занимаемой MySQL, меняться не будет.

Нет, две таблицы - будет дольше

Всегда так?

А если взять такой случай - база вцелом имеет размер 1 Гб и должна хранить следующую информацию:

adr VARCHAR(100)  text TEXT

Все значания adr уникальны. При этом в ячейке TEXT каждой строки содержится порядка 10 Кб. инфы.

База должна быть оптимизирована под запрос вида

SELECT TEXT FROM articles WHERE adr = '$adr'

В этом случае тоже достаточно проиндексировать поле adr ?

А может быть, лучше иметь не одну таблицу, а две - во вспомогательной дать только ТЕХТ, а в таблице articles дать adr и номер строки во вспомогательной таблице, содержащей текст (и выполнять поиск двумя запросами)?

Как будет работать быстрее?

>Все значания adr уникальны.
А почему бы не сделать по нему уникальный ключ и вообще первичный?

>А может быть, лучше иметь не одну таблицу, а две - во вспомогательной дать только ТЕХТ, а в таблице articles дать adr и
>номер строки во вспомогательной таблице, содержащей текст (и выполнять поиск двумя запросами)?
Если adr был бы очень длинным, тогда да, гипотетически возможна ситуация, когда два запроса выполнялись бы больше, а с длиной 100 - вряд ли. Вообще же, профессионалы не рассматривают базу данных, как единожды спроектированную и которая должна быть эффективна до второго пришествия. База - живой организм, увеличивается объем данных, меняется статистика запросов и их соотношение. В идеале за состоянием базы и эффективностью выполнения запросов нужно следить постоянно, оперативно меняя схему индексирования или даже саму схему. Т.е. база данных большого проекта должна быть под присмотром и подвергаться постоянному анализу - не изменились ли условия эксплуатации, не требуются ли изменения. В мелких проектах, как правило, даже очень плохо спроектированные базы данных успешно справляются со своей задачей. Т.е. лучше всего - провести эксперимент и проверить оба предложения. Идеально - делать это регулярно по мере роста базы данных.

профессионалы не рассматривают базу данных, как единожды спроектированную и которая должна быть эффективна до второго пришествия

Даже приблизительно не предполагал такого!
СПАСИБО!

А почему бы не сделать по нему уникальный ключ и вообще первичный?

Вот так:

$query = "CREATE TABLE firm (     adr VARCHAR(100)       text TEXT      PRIMARY KEY(adr) ) ENGINE=MyISAM CHARACTER SET utf8";

Как то стремно... Без ID ?

Вообще целочисленный ID будет быстрее, но строковые ключи - нормальная практика для классического SQL: есть довольно короткая уникальная строка - это идеальный кандидат для естественного первичного ключа.

А вот так тоже можно попробовать?

$query = "CREATE TABLE firm (     id INT (11) NOT NULL AUTO_INCREMENT,     adr VARCHAR(100)        text TEXT       PRIMARY KEY(id) ) ENGINE=MyISAM CHARACTER SET utf8"; mysql_query($query); $query = "ALTER TABLE firm ADD UNIQUE (adr)"; mysql_query($query);

Тут все правильно?

И еще такая гипотеза.

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

Это верно или нет ?

(Памяти на диске потребуется больше, но это не узкое место.)

> (Памяти на диске потребуется больше, но это не узкое место.)

Вы не верно представляете на сколько увеличивается размер.... Я немного утрирую, но....

Предположим у нас есть таблица table1 с 6тью полями и 10тью строками, и таблица table2 с 3мя полями и 20тью строками. Итого в первой таблице 60 ячеек, во второй тоже 60. Вы можете их как-то условно объеденять, делать к ним запросы с преднамеренным уменьшением количества данных по условиям WHERE,

Но для того чтобы их объеденить в одну, вам придется к каждой строке первой таблицы приклеить все строки второй таблицы. Иными словами, хранить на диске результат запроса:

SELECT * FROM table1 CROSS JOIN table2

И эта таблица будет содержать 6+3=9 полей и 10*20=200 строк. Итого 200*9 = 1800 ячеек!
Чувствуете разницу? 1800 против 60+60=120. И это в мизерных таблицах. представьте теперь масштабы реальных таблиц....
А чем больше таблица, тем она медленнее.
Кроме того, на 2 таблицы можно поставить например по 3 индекса для полей, которые чаще всего участвуют в выборках. И каждый из них буде относительно небольшой.... на 10 и 20 строк соответственно. И можно настроить сервер так, что эти индексы будут храниться в оперативке и отрабатывать моментально! А на одну CROSS JOIN'утую таблицу придется сделать 9 индексов и каждый по 200 элементов. На рельной БД они уже не поместятся в ОП, и все будет мееееееееееееееедленно работать через жесткий диск.

Это и есть суть нормализации.

Конечно допускается преднамеренная денормализация. Но не полным объединением таблиц. Например в таблицу тем форума можно добавить поле, содержащее количество сообщений в этой теме..... Но не перемножение таблици тем и таблицы сообщений!

для того чтобы их объеденить в одну, вам придется к каждой строке первой таблицы приклеить все строки второй таблицы

Если приклеить все строки, то, действительно, получается нехорошо. А если из логических условий достаточно приклеить две строки в формате INT ? Тогда, вроде бы, база существенно не нарастет...

Для меня это, как бы, вопрос принципа - допустимо ли дублирование информации в разных таблицах или это жуткий моветон?

>Для меня это, как бы, вопрос принципа - допустимо ли дублирование информации в разных таблицах или это жуткий моветон?
Это зависит от проекта. Мерило - здравый смысл. Пока у вас есть время и ресурсы - денормализация в SQL это жуткий моветон, так как заставляет вас думать о двух и более местах хранения. Когда простой на десять минут или покупка/аредна дополнительного сервера оборачивается реальным убытком в реальных деньгах - правила программирования могут отступать на второй план, точнее начинают работать другие правила.

Наряду с нормализацией - никакого дублирования, существует термин денормализации - дублирование информации с целью ускорения запросов. Это обычная практика - вы место на диске или в оперативной памяти размениваете в обмен на скорость. Вместо того, чтобы каждый раз считать, у вас есть уже готовое значение под рукой. Более того, есть ряд баз данных, главным образом NoSQL, где денормализация возведена в правило и база оптимизирована под работу именно с денормализованными, а зачастую и не структурированными массивами данных. Например, та же MongoDB.

Это моветон, когда предполагает, что ответа запроса вы можете ждать пару суток, в Web же ваш приоритет - скорость обработки запроса, у вас могут висеть тысячи соединений, а главная задача как можно быстрее их обработать, иначе соединения будут висеть - потреблять память и не давать образовывать новые соединения, т.е. скорость обработки клиентов падает. Падает скорость обработки - нужно вводить новые сервера - это деньги (и хорошо, если сайт не нужно перерабатывать на работу с несколькими серверами - это тоже деньги). Понятное дело, что когда вы один разработчик или у вас один сервер, ресурсы которого по сравнению с задачей можно считать бесконечными, на первый план выходит время и оплата работы программиста - вот тут да, лучше, если все данные будут нормализованы, в коде не будет повторов и т.д. Так как основной расход - это оплата времени разработчика и лучше, если бы он работал быстрее, а на задачу тратил меньше времени.

За всеми правилами лежит здравый смысл и экономия ресурсов, в разных задачах нужно экономить разные ресурсы. У вас может быть под рукой кластер, облако, три разработчика и месяц времени, а может быть целый НИИ, три-пять лет и пару процессоров на спутнике. Подходы разработки и в том и в другом случае - кардинально различаются.