есть 3 таблицы innodb 1. items => item_id(AI).... остальные поля 2. cats => id(AI).... остальные поля 3. i2cat => cat_id(id из второй таблицы), item_id (item_id из первой) - таблица связей потом идет выборка Код (Text): SELECT items.* FROM i2cat AS i2c LEFT JOIN items AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=100 работает оно долго ((( связать индексом 1 и 3 нельзя, ибо в 1 item_id - AI, во втором item_id нет , т.к. один item_id может принадлежать нескольким id из таблицы 2 какой может быть выход из ситуации?
таблицы проиндексированы? это же одно из базовых решений связи n-n двух таблиц. насколько долго работает и для какого объема данных?
таблица 1 - индекс по item_id так как он AI (ну плюс еще пару критичных полей) таблица 2 - то же самое таблица 3 - оба поля индекс тут еще вот какая петрушка, таблиц таких (1 2 3) их много, и запрос формируется путем UNION, т.е. Код (Text): SELECT items.* FROM i2cat_1 AS i2c LEFT JOIN items_1 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=100 UNION SELECT items.* FROM i2cat_2 AS i2c LEFT JOIN items_2 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=101 UNION SELECT items.* FROM i2cat_3 AS i2c LEFT JOIN items_3 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=102 выбока 20 тысяч позиций занимает порядка 7-9 секунд на сервере 512 метров оперативы, гиг проца если все items и все cats свалить в одну таблицу....быстрее будет? у меня просто паническая боязнь таблиц в несколько лямов строк (
ну 20тыс позиций тоже разные бывают. можно кортежи по 8 байт тянуть а можно и блюрей-изошники... 1. explain нам что говорит? ок всё? 2. посмотреть статус сервера. регулярное обращение к разному набору таких триплетных таблиц (если я правильно понял что есть таблицы 1-2-3, есть 4-5-6, 7-8-9 итд просто из них юнионом тянется общий формат ответа) может на таком слабом сервере просто не попадать в кэш и постоянно приходится читать таблицы с диска. будут расти счетчики соответствующие. ну и explain тоже будет выдавать чтение с диска. в принципе если данные не очень большие, то можно и поиграться с тюнингом my.cnf. опять же если это дедик БД. отдадим все 512+свап и пусть крутится. если же нет (данные больше, это не дедик) то железо лучше поменять
Мне почему то кажется, что если 1 и 3 связать внешним ключом, то все будет шоколадно, нет? Добавлено спустя 4 минуты 1 секунду: бляха...про кеш то я и забыл ((( Код (Text): [mysqld] port = 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer_size = 16K max_allowed_packet = 64M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K ты все правильно понял беда в том, что каждый запрос тянет позиции из 1-2-3..... второй из 5-6-7...в общем они все время разные и повторятся врятли будут, поэтому кеш мне кажется тут не выход (
а какое объединение-то? таблица А допустим записи в бложике таблица Б допустим метки тогда прямая связь А-Б будет давать только 1-1. или если мы будем юзать больше одной метки то нам придется создать нужно количество меток с одинаковым именем чтоб было 1-n поэтому создается таблица В в которой и происходит связь n-n. АВ как 1н, ВБ как н1 и так мы и выбрали все метки записи поста. а если зафигачить по внешнему ключу то читай вариант АБ то есть много повторных строк в каждой из таблиц. либо же они реально 1-1 относятся и тогда зачем промежуточная таблица?
1. если связи 1-1 то не надо юзать связывающую таблицу. 2. внешний ключ не добавляет производительности. это инструмент контроля целостности данный. в некоторых случаях он может даже к замедлению привести.
1) Если таблицы вместе с индексами не в буфере, то выборка будет со скоростью чтения данных с диска - от 1-2 до 20 МБ/с в зависимости от. 2) 20 тысяч - это число записей в таблице связей? Или что? 3) UNION как правило использует временные таблицы. Если выборка большая, то вр.таблица будет создана на диске, а не в памяти. В общем, возможно 512М для движка маловато будет - нужно смотреть отчёты (в SQLyog закладка "Profiler", например).
в том то и дело, что связи 1 к N ладно, поставим вопрос по другому: увеличение мощности железки может решить вопрос? допустим что выборка 20 тыс записей - это потолок Добавлено спустя 2 минуты 47 секунд: это число записей в таблицах 1_N но выбираются они исходя из связей в таблице 3
вот за это я и боюсь ((( ладно, завтра перенесу проект на локальную машину, посмотрим что "скажет", дальше будем думать, спасибо всем да, что бы почитат на ночь про архитектуру базы, так чтоб не до 5 утра ))) Добавлено спустя 31 секунду: подскажите какие, буду очень признателен
1) Сколько записей в i2cat? 2) Каковы размеры (в Кбайтах) таблиц? (всех в запросе, вместе с индексами) 3) Сколько всего UNION в запросе?
innodb_buffer_pool_size Остальное оставьте по дефолту для начала, - это уже тонкие настройки, особенно для версии 5.6 и старше. Добавлено спустя 1 минуту 40 секунд: Ну Вы шутник Мы ведь говорим о том запросе, который выполняется 7-9 сек? Или о сферическом коне в вакууме?
а, конкретно, ща, 5 сек 1. 72480 2. ~ 39 Mб 3. 703 Добавлено спустя 31 секунду: ДА! я и говорю что что то не так в королевстве ) Добавлено спустя 1 минуту 45 секунд: я просто не уверен (но и не проверял) что такая выборка из одной таблицы в несколько лямов записей будет быстрее
703 UNION?! Офигеть! Так чего ж Вы хотите? Идёт обработка 700 таблиц каждая по 72 тыс записей, т.е. это эквивалент одной таблицы в 50 млн.записей, плюс ещё связанные таблицы. И общий размер, как я понимаю, таблиц порядка трёх гигов. И Вы хотите всё это быстро обрабатывать на 512М оперативке?! Флаг Вам в руки... В общем, хотите скоростей, то: - 2Г RAM (лучше 4) - 3Ггц проца - объединить 700 таблиц в одну Скорость выборки заметно увеличится. Добавлено спустя 3 минуты 57 секунд: Поддерживаю. Да и 7-Zip ужмакает раз в 10, если что.
не, после того как он скзаал, что у него там тыща таблиц, то уже просьба о базе не актуальна. =) а про 512 оперативки я не заметил. это просто садизм. у меня на виртуалке рабочей больше. ну так... чтоб не свопила.
Да просто интересно попытать - никогда такую хрень не делал, в 1000 однотипных таблиц. Самому генерить такую базу лень конечно, а если бы кто дал, то потестить можно было бы малость. Да, кстати, и запрос в 700 UNION тоже нужен, не писать же самому такую портянку