SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 28.02.2009 02:23:31

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

тормознутый запрос

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

$arr = mysql_query("SELECT t.id,
                           t.name,
                           t.descr,
                           t.banner,
                           t.month_vote,
                           t.month_votenum,
                           t.month_hits,
                           COUNT(ratings.id) AS vote,
                           SUM(ratings.rating_num) AS votenum
                           FROM
                           tracker AS t
                           LEFT JOIN ratings ON rating_id = t.id
                            WHERE DATE_FORMAT(ratings.added, '%Y-%m') = '$year-$select'
                            GROUP BY t.id ORDER BY vote DESC") or die(mysql_error());

запрос идет циклом, индексы поставил для tracker(id,name,descr,banner,mothn_vote,month_votenum,month_hit) и для ratings(id,rating_num,added)
что я сделал не прально и что тут можно оптимизировать?

заранее спасибо!

Неактивен

 

#2 28.02.2009 04:52:06

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

Индексы таблицы `ratings`:
1. `added`
2. `rating_id`

Запрос:

$arr = mysql_query(
"
SELECT
  t.id,t.name,t.descr,t.banner,t.month_vote,t.month_votenum,t.month_hits,
  COUNT(ratings.id) AS vote,
  SUM(ratings.rating_num) AS votenum
FROM `tracker` AS t
LEFT JOIN `ratings`
  ON rating_id = t.id
WHERE ratings.added BETWEEN '$year-$select-01' AND LAST_DAY('$year-$select-01')+INTERVAL 1 DAY
GROUP BY t.id
ORDER BY vote DESC
"
) or die(mysql_error());



Можно создать ещё одну табличку, где будут храниться уже отобранные и сгруппированные по rating_id/месяцам данные.

Неактивен

 

#3 28.02.2009 05:24:22

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

спасибо за пример!
придется делать отдельную табличку так как записей в ratings очень много, на данный момент 115267 и с каждым днем растет, отуда и тормознутость запроса.

Неактивен

 

#4 28.02.2009 11:41:51

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6756

Re: тормознутый запрос

Что-то я никак не могу понять смысл этого запроса smile
Если нужно достать «те сиды, которые имеют рейтинги за этот месяц», то лучше найти
рейтинги, а потом уже сиды (у Вас получается наоборот, из-за left join):

SELECT t.id, ..., r.id AS vote
FROM tracker AS t
JOIN (SELECT rating_id, id FROM ratings WHERE added BETWEEN ... GROUP BY 1) AS r ON r.rating_id = t.id
ORDER BY vote DESC


Ключики на ratings(added) и tracker(id).

Неактивен

 

#5 01.03.2009 01:57:33

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

спасибо огромное помогло!!!

еще вопрос в правильности использования индексов, к примеру есть такой просто запрос

$arr2 = mysql_query("SELECT id, name, descr FROM tracker ORDER BY votenum DESC LIMIT 10") or die(mysql_error());

который идет циклом, как я прально понимаю я делаю индекс на (id, name , descr) а вот на сортировку нужно указывать votenum???

Неактивен

 

#6 01.03.2009 02:05:23

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

В этом запросе нужен только индекс на поле `votenum`. А зачем он идёт циклом?

Неактивен

 

#7 01.03.2009 02:08:25

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

как зачем вывод топ10, так оказывается на на выборку индекс не нужен?

Неактивен

 

#8 01.03.2009 02:12:24

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

Нужен, если есть ограничения выборки и не во всех случаях.

XJIOP написал:

как зачем вывод топ10

Ну так это 1 запрос.

Отредактированно coin (01.03.2009 02:13:07)

Неактивен

 

#9 01.03.2009 02:16:57

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

не я не прально выразился не запрос идет циклом а выборка точнее while ($row = mysql_fetch_assoc($res)) {}

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

Отредактированно XJIOP (01.03.2009 02:26:12)

Неактивен

 

#10 01.03.2009 02:28:07

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

Этот цикл на скорость выполнения запроса не влияет, в это время сам запрос уже выполнен.

http://dev.mysql.com/doc/refman/5.1/en/ … cture.html

XJIOP написал:

кстати тут ограничение LIMIT в 10

Я имел ввиду ограничения секции WHERE.

Отредактированно coin (01.03.2009 02:29:20)

Неактивен

 

#11 01.03.2009 02:30:53

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

спасибо выручил! начинаю изучать ))

Неактивен

 

#12 01.03.2009 03:41:58

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

а еще вопрос чтобы не создавать отдельную тему.

нужно сделать такой запрос чтобы узнать на какой позиции данный юзер из всех юзеров, с учетом того что сортировать будет по кол. голосов ORDER BY month_vote DESC
тоесть к примеру зайду в профиль юзеру и увижу что он на 5 месте из 230 юзеров по такой сортировке.
долго голову ломал и сумел сделать такой запрос

$arr = mysql_query("SELECT COUNT(*), id FROM tracker  GROUP BY id ORDER BY month_vote DESC") or die(mysql_error());

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

Отредактированно XJIOP (01.03.2009 03:43:12)

Неактивен

 

#13 01.03.2009 05:10:00

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5829

Re: тормознутый запрос

Ваш запрос будет давать одинаковый результат для любого юзера, так как в нем нет условия на выборку для конкретного юзера.
В предположении, что юзер определяетяс по id:


set @a=0;
select pos from (select @a:=@a+1 as pos, id from tracker order by month_vote desc) t where id=значение_id;

Неактивен

 

#14 01.03.2009 05:22:46

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

круто спасибо, такой запрос уже не для моих мозгов ))) вот только не понял как вывести значение SET @a=0; в пхп

спасибо разобрался mysql_query("SET @a=0"); а индекс я так понимаю month_vote и id, вот только еще отдельный запрос нада будет делать на count(*) что бы знать сколько всего юзеров.

эх неработает =/

mysql_query("SET @a=0");
$row = mysql_query("SELECT pos FROM (SELECT @a:=@a+1 AS pos, id, month_vote FROM tracker ORDER BY month_vote DESC) t WHERE id = $id AND month_vote != '0'") or die(mysql_error());
$arr = mysql_fetch_assoc($row);

первые 2 позиции показывает верно а дальше 3 место выдает за 6 а 6 за 3 путаница получилась.

Отредактированно XJIOP (01.03.2009 05:52:56)

Неактивен

 

#15 01.03.2009 13:50:40

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

Количество записей, стоящих в рейтинге выше id=123:

SELECT COUNT(*) AS a FROM tracker WHERE month_vote>(SELECT month_vote FROM tracker WHERE id=123)

Количество записей, имеющих такой же рейтинг как и у id=123:
SELECT COUNT(*) AS b FROM tracker WHERE month_vote=(SELECT month_vote FROM tracker WHERE id=123)


Теперь если запись на первом месте, то a=0 и b=1, а если 5 записей имеют одинаково высокий рейтинг и претендуют на 1ое место, то у всех будет a=0, b=5 (т.е. все эти записи занимают места с a+1 по a+b в неопределённом порядке).

Неактивен

 

#16 01.03.2009 18:49:52

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

спасибо разобрался ))

начал изучать индексы, все вроде понятно но не понятно только одно, какие индексы указывать в связке LEFT JOIN на первую выборку
к примеру запрос такой:

$res = mysql_query("SELECT afisha.*,
                           users.class,
                           users.username,
                           IF( ISNULL(countries.flagpic) <> 0, '', CONCAT('<img src=\"".$GLOBALS['pic_base_url']."/flag/', countries.flagpic, '\" title=\"', countries.name, '\" width=\"', 20, '\">')  ) AS flag,
                           categories.name,
                           countries.id AS countid
                           FROM afisha
                           LEFT JOIN users ON users.id = afisha.userid
                           LEFT JOIN countries ON countries.id = afisha.land
                           LEFT JOIN categories ON categories.id = afisha.razdel
                           ORDER BY added DESC $limit") or die(mysql_error());

я должен ставить индекс только на added или же userid,land,razdel,added для таблицы afisha? и второе для этих LEFT JOIN тут понятно к примеру users.id там индекс примари для него уникальный а еслиб был бы не ид а другое значение для него тож индекс нужен был бы?


ps: извиняюсь за такое кол. вопросов, просто хочу понять всю систему иднксации.

Неактивен

 

#17 01.03.2009 20:06:47

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

Неактивен

 

#18 14.03.2009 00:19:51

XJIOP
Участник
Зарегистрирован: 28.02.2009
Сообщений: 22

Re: тормознутый запрос

помогите с запросом

SELECT *
FROM ratings
WHERE rating_id =1
AND DATE_FORMAT( ratings.added, '%Y-%m-%d' ) = CURDATE( )


explain выводит
id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1    SIMPLE    ratings    ALL    id_added    NULL    NULL    NULL    92474    Using where

проблема, затрагивает слишком много строк, индекс стоит на rating_id и added, запросом нужно вытащить кол. голосов за сегодня, что я делаю не так?

Неактивен

 

#19 14.03.2009 11:16:25

coin
Гуру
Зарегистрирован: 15.07.2008
Сообщений: 66

Re: тормознутый запрос

У вас в условии проверяется не значение самого индексного поля, а результат вычисления функции, который к индексу имеет косвенное отношение.

SELECT *
FROM ratings
WHERE rating_id=1
AND ratings.added BETWEEN CURDATE() AND CURDATE()+INTERVAL 1 DAY

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson