|
Задавайте вопросы, мы ответим
Вы не зашли.
Вопрос 1:
Что есть:
TABLE users(
id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rank0 INT,
rank1 INT,
rank2 INT,
rank3 INT,
);
VIEW summ_rank AS
SELECT id, (rank0+rank1+rank2+rank3) as t_rank FROM users;
Что делаю:
SELECT * FROM summ_rank ORDER BY t_rank DESC;
Для большого количества записей, в таблице users, выборка из "вида" выполняется не очень быстро.
Можно ли как-то хранить сумму rank1+rank2... в users? Но так чтобы она сама считалась, а то каждый раз мучать "вид" мне кажется не разумно)
Вопрос 2:
Как получить порядковый номер строки из упорядоченной выборки?
Например есть:
TABLE players (id PK, points int)
Что нужно:
Получить номер строки(какой по порядку начиная с первой строки)
Как делаю:
SELECT count(*) FROM (SELECT * FROM players ORDER BY points DESC) pp WHERE pp.points>250
Каждый раз упорядочивать по points думаю не хорошо) Индексацию этого поля не предлагать, так как в настоящей таблице нужно выполнять подобный поиск практически по каждому из полей(типа есть еще points1 points 2 points3...).
Вопрос 3:
Есть выборка:
SELECT
id,
(SELECT names.u_name FROM names WHERE names.id=ids.uid0),
(SELECT names.u_name FROM names WHERE names.id=ids.uid1),
(SELECT names.u_name FROM names WHERE names.id=ids.uid2)
FROM ids;
Т.е. в ids хранятся номера, а в names хранятся названия соответсвующие этим именам.
Можно ли как-то оптимизировать это?)) Просто надо чтобы в таблице показывался не номер, а имя соотвествующие этому номеру) Номера меняются в ids, а в names все статично.
Получаемые имена нужны по порядку.
Ахах)) Дошло до меня спустя время) Я загружу все из names в массив (id => Имя) и буду подставлять, а не для каждой строки выполнять по 3 лишних запроса)) Спасибо)
Отредактированно ik (26.01.2010 17:03:49)
Неактивен
1. Я обычно называю это представлением. Хорошее русское слово, которое отражает
смысл и при этом не заставляет заключать себя в кавычки 
Можете воспользоваться
триггером, который будет поддерживать актуальным доп.поле суммы. Заодно и индекс
добавьте на него.
2. Если не указывать явно ORDER, то порядок будет, вообще говоря, произвольный.
И может легко меняться. Поэтому, если порядок критичен, ORDER нужно писать всегда.
Порядок строки можно получить, например, используя переменную:
SET @a := 0;
SELECT @a := @a + 1, fieldname FROM tablename;
3. Попробуйте JOIN, он должен работать быстрее подзапросов.
Неактивен
paulus,
Большое спасибо за такие скорые ответы!
1.До изучения триггеров еще не дошел. Почитаю!
2.Про переменные... буду пробовать) не совсем пока понимаю как это сработает).
3.Это получается в моем случае надо делать три LEFT JOIN? А можно ли ту таблицу со статичными данным хранить в памяти(100 значений по CHAR(20)), вместо того чтобы каждый раз подгружать всю для получения нескольких полей? Есть такая возможность? Меня устроют краткие ответы!
Еще вопрос созрел:
SELECT * FROM table WHERE type=0 ORDER BY points;
type - может быть от 0 до 10
points - FLOAT(9,6)
Чтобы такой запрос выполнялся быстро, нужно индексировать оба поля?
Поможет ли индексация поля points для ускорения?
Отредактированно ik (26.01.2010 19:54:59)
Неактивен
Если значений немного и меняться они не будут, то можете использовать
ENUM вместо числа и дополнительной таблицы: будете иметь сразу значения
в виде текстовых данных, но при этом хранить всё как числа.
Для Вашего запроса нужен составной ключ по (type, points).
Неактивен
FAQ в первую очередь прочитал)
Но меня смутило то что я использую не два условия в WHERE усл1 AND усл2, а исползую сортировку. Получается и при сортировке составной индекс помогает. Спасибо!
От триггера откажусь, так как если делать триггер на обновление, в нем самом не должно быть заданий на обновления этойже таблицы 
А хранить одно значение в другой таблице - это лишнее.. Значит придется "на стороне" считать сумму и обновлять.
Еще раз Спасибо!
Неактивен
Вы неверно поняли концепцию триггера. Если в нем будет написано что-то типа
SET NEW.sumfield = NEW.f1 + NEW.f2, то ничего дополнительно обновлять не
надо, обновление пройдет одно, и с правильным значением.
Неактивен
что-то в manual'e примеры только с update, insert... ))) Логично было предположить что есть способ обновлять туже строку что и изменилась. Спасибо! (Работает!!)
ENUM мне походит. Значение можно получить как числовое так и текстовое. Здорово!
Только одно "но". Есть ли что-то не рациональное в том что тип ENUM из 100 значений будет присвоен нескольким полям?)
Отредактированно ik (26.01.2010 21:25:49)
Неактивен
Серверу будет совсем всё равно, сколько там полей
Единственное, что действительно будет не удобно — это добавлять новые варианты,
если они когда-либо появятся. Таблице прийдется менять метаданные (ALTER), а это
всегда тяжелая операция.
Неактивен
Спасибо Вам Большое! (C ENUM выборка в разы быстрее стала)
Отредактированно ik (26.01.2010 22:35:33)
Неактивен
Позволю себе продолжить тему)
Есть 2 таблицы:
tb1 id (PK AI), sortcol1(int)
tb2 uid(INDEX), sortcol2(int)
Что делаю:
SELECT id FROM
tb1 LEFT JOIN tb2 ON tb2.uid=tb1.id ORDER BY sortcol1,sortcol2
Т.е. мне нужно получить записи из первой таблицы, отсортированные сначала по своему полю, а потом по привязанному(не уверен что это так называется).
sortcol1 - значения не уникальны.
sortcol2 - значения практически уникальны(очень мал шанс что совпадут).
Без LEFT JOIN и сортировки по привязанному полю запросы выолняется за 22-25 мс.
А с вышеупомянутыми параметрами за 180-195мс.
1.Можно ли это как-то оптимизировать?
2.Разумно ли дублировать sortcol2 в первой таблице и делать индекс по(sortcol1,sortcol2)?
3.Как можно использовать тот факт, что во второй таблице хранятся значения первичного индекса первой? Во второй таблице эти значения также уникальны.
Неактивен
Ну, индекс по sortcol1 слегка улучшит результаты, хотя сортировки
в памяти все равно не избежать. Теоретически должно хватить индексов
tb1 — (sortcol1)
tb2 — (uid, sortcol2).
Практически, скорее всего, второй кусок индекса tb2 не будет
использоваться. Впрочем, это стоит проверить.
EXPLAIN от запроса в студию 
Неактивен
Не поверите) Увелечению скорости запроса ничего не помгло.
Забыл сказать что на sortcol2 итак есть индекс.
Пробовал индекс вешать sortcol1 и (uid,sortcol2).
Время выполнения запросов с индексами даже больше показывало)))
Т.е:
Просто запрос без LEFT JOIN и без сортировки ~10мс
Запрос c LEFT JOIN 40-50мс
Добавив сортировку по не индексированному полю и привязанному
ORDER BY sortcol1,sortcol2 - 60-70мс.
Позже попробую вешать триггер на tb2 чтобы он делал копию в sortcol2 в tb1. Не рационально, но место на диске особой роли не играет)
Хочу еще спросить. Есть ли другой способ, кроме LEFT JOIN, чтобы свзяывать таблицы?
Может надо как-то ключи создавать по другому?)) Возможностей не знаю)
Отредактированно ik (27.01.2010 18:50:10)
Неактивен
EXPLAIN то покажите. Ну и не злоупотребляйте внешними объединениями.
Неактивен
я теперь без LEFT JOIN, сделал триггеры которые дублируют колонку из своих таблиц в, так сказать, основную.
rank0 - ENUM (1...100)
dist0 - FLOAT
есть индекс (rank0,dist0)
mysql> explain SELECT nick,rank0,dist0 FROM jb_u ORDER BY rank0 DESC,dist0 DESC\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jb_u
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 35105
Extra: Using filesort
1 row in set (0.00 sec)
Extra: Using filesort - это плохо или хорошо?)) Читал ман, но со знанием англ. не очень...
Когда добавил LIMIT, то Filesort исчез, а появилось:
mysql> explain EXTENDED SELECT nick,rank0,dist0 FROM jb_u ORDER BY rank0 DESC, dist0 DESC LIMIT 0,50\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jb_u
type: index
possible_keys: NULL
key: rank0
key_len: 7
ref: NULL
rows: 50
filtered: 70210.00
Extra:
1 row in set, 1 warning (0.00 sec)
Если делаю FORCE INDEX, тот же, который используется, то в реале я получаю не 100мс, а 200мс(столько показывает если вобще не использовать индекс) на запрос, но EXPLAIN ничего не показывает нового:
mysql> explain EXTENDED SELECT nick,rank0,dist0 FROM jb_u FORCE INDEX(rank0)ORDER BY rank0 DESC, dist0 DESC LIMIT 0,50\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: jb_u
type: index
possible_keys: NULL
key: rank0
key_len: 7
ref: NULL
rows: 50
filtered: 70210.00
Extra:
1 row in set, 1 warning (0.00 sec)
И вот еще что, убрал составной индекс(rank0,dist0) и сделал просто rank0, то EXPLAIN стал показывать Filesort, но(!) время на запрос тратится столько же, сколько со составным индексом.
Хотя в мане написано что ORDER BY использует индекс в данном случае:
ORDER by keypart1 DESC,keypart2 DESC
Но в моем случае, разницы между составным и одиночным я не увидел.
Идем дальше. Теперь использую сортировку не по rank0,dist0 а по rank0 и присоединенному столбцу:
id - одинаковые поля в таблицах, оба PK
dist - содержит те же значения что и dist0
rank0 - имеет индекс
dist - имеет индекс
FROM jb_u LEFT JOIN(jb_conn)USING(id) ORDER BY jb_u.rank0 DESC, jb_conn.dist DESC LIMIT 0,50
Время выполнения? ~200мс т.е. такое же, как сортировка по двум столбцам без индексов в одной таблице. Значит дублирование столбца принесло мне удвоение скорости) Или как-то можно ускорить JOIN?
Отредактированно ik (28.01.2010 10:37:13)
Неактивен
Все-таки, для первого и второго запросов ключик на (rank0, dist0) нужен
На небольших нагрузках этот дополнительный filesort будет чувствоваться.
А вот EXPLAIN интересного запроса Вы и не показали.
Неактивен
rank0 - index
dist - index
id - оба PK
Отредактированно ik (28.01.2010 18:56:29)
Неактивен
Индекс не используется, т.к. Вы пытаетесь достать всю табличку. Оптимизатор
правильно полагает, что время, необходимое для сортировки данных в памяти,
меньше, чем время, чтобы достать каждую строку с помощью отдельного
перемещения головки диска. В случае же с LIMIT, перемещений будет только
10, поэтому это быстрее, чем прочитать всё и отсортировать.
Первый запрос хороший. Добавьте разумный LIMIT — и будет летать.
Неактивен
Более-менее ясно) Но к LEFT JOIN я не вернусь. Одно только добавление LEFT JOIN () USING () добавляет к 50мс еще 100(!).. + сортировка 50мс. В итоге дублированием столбца в таблицу, для избавления т LEFT JOIN я скостил как минимум четверть времени)
Но всетаки не покидает чувство что, что-то не так делаю))))
И всеравно не понимаю, с LIMIT, что есть индекс, что нет, сортировка по времени одинаковая..
(я так понял что мои таблицы в память были подгружены)
Отредактированно ik (28.01.2010 19:53:17)
Неактивен
Созрел вопрос общего характера.
Допусти есть поля id,f0,a1,a2,a3,a4,a5.
id - не уникальный индекс
f0 - значения от 1-9
записи в таблицу вносятся такого рода:
id, f0
1, 5, ....
1, 6, ....
2, 3, ....
2, 4, ....
2, 5, ....
т.е. для каждого не уникального id есть уникальный f0.
Если мне предполагается часто запрашивать данные по id и колонке f0 то лучшим вариантом будет создать составной индекс(id,f0) или же хранить данным в разных таблицах(для кадого f0 своя) с уже уникальным индексом id ?
Отредактированно ik (22.02.2010 22:01:55)
Неактивен
Лучше хранить так, как подразумевает логика Вашего приложения.
Я бы начал с одного уникального индекса на двух полях (благо,
это следует из логики Вашего приложения), а потом, если вдруг
таблица начнет прогибаться по записи, добавил бы partitioning по
второму полю.
Неактивен
