Меню
Что нового?

GROUP BY и ORDER BY помогите с запросом и таблицей...

ilal

Новичок
GROUP BY и ORDER BY помогите с запросом и таблицей...

создаётся таблица

CREATE TABLE `messages` (
`id` INT( 16 ) UNSIGNED NOT NULL AUTO_INCREMENT ,

/*данные отправителя*/
`id_coming` INT( 16 ) UNSIGNED NOT NULL ,
`coming_public` INT( 16 ) UNSIGNED NOT NULL ,

/*обращение внимания на объявление*/
`coming_vsf` INT( 8 ) UNSIGNED DEFAULT '0' NOT NULL ,
`coming_works` INT( 8 ) UNSIGNED DEFAULT '0' NOT NULL ,
`coming_announce` INT( 16 ) UNSIGNED DEFAULT '0' NOT NULL ,

/*получатель*/
`id_enterring` INT( 16 ) UNSIGNED NOT NULL ,
`enterring_view` INT( 2 ) UNSIGNED DEFAULT '0' NOT NULL ,

`message` TEXT,

/*группа*/
`gro` VARCHAR( 255 ) NOT NULL,

PRIMARY KEY (`id`),
INDEX (`id_coming`),
INDEX(`coming_public`),
INDEX (`id_enterring`),
INDEX (`enterring_view`),
INDEX (`gro`)

таблица для периписки... пользователи пишут дру-другу ...

`id_coming` -отправитель
`id_enterring`- получатель... тобишь, значения полей меняются...

`gro`- группа-- одно значение для каждой пары общающихся...
`enterring_view`- прочитал-ли получатель сообщение
`coming_public`- дата отправки сообщения

вопрос- как мне можно вывести списком диалоги, не списки всех сообщений, а только "шапку" каждого диалога в которой показать количество сообщений
отсортировав по наличию новых сообщений (выводиться для каждого посетителя отдельно) и по дате?

моск сломал...

-~{}~ 29.04.08 08:55:

пробовал

$query = "SELECT SQL_ * FROM `messages` WHERE `id_coming`='$uu' OR `id_enterring`='$uu' GROUP BY `gro` ORDER BY `enterring_view` LIMIT $start,$limit";

где '$uu' - ид просматривающего посетителя... всё хорошо, вроде, но как определить именно для просматривающего, что у него есть непросмотренные?

-~{}~ 29.04.08 09:20:

мнда... тишина...

-~{}~ 29.04.08 09:53:

сижу- думаю... может, таблицу изменить? блин...
 

Gas

может по одной?
Или свой запрос попробуй изменить на
Код: 
SELECT *,SUM(IF(`id_enterring`='$uu' AND `enterring_view`<1, 1, 0)) AS  viewed, 
         MAX(`coming_public`) AS `date_public`  
FROM `messages` 
WHERE `id_coming`='$uu' OR `id_enterring`='$uu' 
GROUP BY `gro` 
ORDER BY (viewed=0), `date_public` DESC;
но без изменения структуры я бы попробовал сделать так (должно быть быстрее на большом объём реальных данных):

Код: 
SELECT SUM(cnt) AS cnt, MIN(`enterring_view`) AS viewed, MAX(`date_public`) FROM 
(
  (SELECT COUNT(*) as cnt, MAX(`coming_public`) AS `date_public`, 1 AS `enterring_view` 
     FROM `messages` WHERE `id_coming`='$uu' GROUP BY `gro
  )
  UNION ALL
  (SELECT COUNT(*) as cnt, MAX(`coming_public`) AS `date_public`, MIN(`enterring_view`)  AS `enterring_view`
     FROM `messages` WHERE `id_enterring`='$uu' GROUP BY `gro`
  )
) AS t
GROUP BY `gro`
ORDER BY viewed, `date_public` DESC;
тут бы имели смысл составные ключи (id_coming, gro) и (id_enterring, gro), но поле `gro` желательно уменьшить до int, уверен что это реально.

а по-хорошему, ввёл бы дополнительную таблицу, в которой хранил для каждого "разговора" необходимую информацию.
 

ilal

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

а дополнительную таблицу--- что лучше вынести в неё?

-~{}~ 29.04.08 11:16:

что-то мне не нравиться такие запросы использовать сложные... кажется мне, что проще, точнее- быстрее, будет делать простые запросы из двух таблиц чем одну так "насиловать" :)

-~{}~ 29.04.08 11:32:

как я понимаю, у вас такой опыт есть :)

-~{}~ 29.04.08 11:40:

или лучше вообще описание отдельно хранить...

1. дата последнего сообщения,
2. автор последнего
3. есть ли непрочитанные для каждого из авторов... хм... хотя это тогда проверять придется при каждой записи-открытии... блин... опять засада...
 

Gas

может по одной?
придется при каждой записи-открытии... блин... опять засада...
Нажмите для раскрытия...
кажется мне, что проще, точнее- быстрее, будет делать простые запросы из двух таблиц чем одну так "насиловать"
Нажмите для раскрытия...
так тебе шашечки или ехать? или насиловать или 2 таблицы
 

ilal

Новичок
две-две :))) а что выносить посоветуете?

-~{}~ 29.04.08 13:53:

в первом примере:

ORDER BY (viewed=0), `date_public` DESC;

(viewed=0) что делает? если честно- не понял :)
 

Gas

может по одной?
В контексте того запроса - сначала те `gro`, где есть неотвеченные сообщения (`enterring_view`<1) к заданному пользователю пользователю (`id_enterring`='$uu'), потом в порядке убывания даты последнего сообщения в этой `gro`
 

ilal

Новичок
ндааа... однако... я такого просто не видел... :)

-~{}~ 29.04.08 14:26:

а по поводу двух таблиц что скажете?

-~{}~ 30.04.08 07:27:

господа, вопрос такой, в продолжение темы: у кого есть опыт, сколько строк в базе будет быстро работать? кто-то делал-же общалки... например, 100 000 быстро будет работать? если по примеру первому сделать?

просто всё думаю о таблице отдельной под каждый разговор... минусы тоже будут у этого подхода, но хоть вывод сведений о каждом "диалоге" ускориться... хотя выборка сообщений останется на той-же скорости. :(

-~{}~ 30.04.08 07:57:

сейчас работает

SELECT *,SUM(IF(`id_enterring`='$uu' AND `enterring_view`<1, 1, 0)) AS viewed,
MAX(`coming_public`) AS `date_public`
FROM `messages`
WHERE `id_coming`='$uu' OR `id_enterring`='$uu'
GROUP BY `gro`
ORDER BY `viewed` DESC, `date_public` DESC;

тестовая таблица небольшая....
 

Gas

может по одной?
хотя выборка сообщений останется на той-же скорости.
Нажмите для раскрытия...
выборка сообщений внутри одного разговора - это очень быстро, основная проблема - получение общей информации по каждому разговору что ты сейчас и решаешь. Своё мнение я уже высказал и минусов в отдельной таблице не вижу.

сейчас работает ...
ORDER BY `viewed` DESC
Нажмите для раскрытия...
при таком запросе первыми будут темы в которых больше непрочитанных сообщений, а это имхо не правильно. Если есть 2 темы (разговора) в одном из которых 2 непрочитанных сообщения за позавчера и одна тема, в которой непрочитанное за сегодня - то это тема должна идти первой, в твоём запросе она будет второй. По-этому то я и добавил ORDER BY (viewed=0), так-как viewed - количество непрочитанных сообщений, нужно сортировать не абсолютному значению, а по флагу - есть такие сообщения или нет, что это условие и делает.

например, 100 000 быстро будет работать
Нажмите для раскрытия...
если в приведённом сапросе index merge optimization не будет использоваться (смотри explain), то это полный перебор всей таблицы и тормоза.
 

ilal

Новичок
Автор оригинала: Gas
выборка сообщений внутри одного разговора - это очень быстро, основная проблема - получение общей информации по каждому разговору что ты сейчас и решаешь. Своё мнение я уже высказал и минусов в отдельной таблице не вижу.
Нажмите для раскрытия...
хм...
$query = "SELECT SQL_ * FROM `messages` WHERE `id_coming`='$uu' OR `id_enterring`='$uu' ";
будет быстро работать? хотелось бы...


при таком запросе первыми будут темы в которых больше непрочитанных сообщений, а это имхо не правильно. Если есть 2 темы (разговора) в одном из которых 2 непрочитанных сообщения за позавчера и одна тема, в которой непрочитанное за сегодня - то это тема должна идти первой, в твоём запросе она будет второй. По-этому то я и добавил ORDER BY (viewed=0), так-как viewed - количество непрочитанных сообщений, нужно сортировать не абсолютному значению, а по флагу - есть такие сообщения или нет, что это условие и делает.
Нажмите для раскрытия...
сейчас попробовал- ORDER BY (viewed=0) просто ошибку вызвало... почему такое быть может?


по второй таблице:

1. дата последнего сообщения, -- сортировка по дате
2. автор последнего-- кто кому последним писал...
3. есть ли непрочитанные для каждого из авторов... -------- а вот тут проблема... по идее, сохранять только последнее, но сообщений новых от первого "писателя" второму может быть штук 20, все будут непрочитанные... второй может зайти, увидеть что на странице 10 новых сообщений (по 10 выводятся), показанные отметятся как прочитанные, а те, что остались за кадром (первые 10 из 20 сообщений)... вот...

такие в мысли...

-~{}~ 30.04.08 14:18:

теоретически можно искать непрочитанные в общей таблице, запросом по двум таблицам... но будет ли это достаточно быстро?

имхо- кол-во непрочитанных надо тоже во второй хранить... но как это сделать удобно без вероятности возникновения ошибок...

-~{}~ 30.04.08 14:24:

можно, конечно, просто счетчик сделать-- прочитано- минус один... новое +1... но он же щелкать будет... при переписке... хотя... ммммм...

-~{}~ 02.05.08 15:56:

господа, потестил тут...

200 000 работает за 0,5 сек... для меня это вполне...

50 000 почти не заметно вообще...

это с первым примером...

но вот вопрос!

почему ORDER BY (`viewed`=0) выдает ошибку??? что делать? Gas прав... должно работать именно так...
 

ilal

Новичок
ну... мне не нужно выбирать, мне нужно отсортировать...

имхо- разные вещи...

-~{}~ 02.05.08 17:58:

Автор оригинала: LeFF®
потому что синтаксис неправильный, если выбирать записи где `viewed`=0 нужно использовать WHERE
Нажмите для раскрытия...
да и потом... я же выборку то не по этому параметру делаю...
 

berkut

Новичок
так добавь емаё
IF(SUM(IF(`id_enterring`='$uu' AND `enterring_view`<1, 1, 0)), 0, 1) AS has_ureaded
...
ORDER BY has_ureaded

-~{}~ 02.05.08 19:11:

ну чёто типо того
 

ilal

Новичок
так... до санитаров дошли :)

попробую :) спасибо :)

я с этим делом недавно плотно связался, такие вещи ещё сложны :)

-~{}~ 02.05.08 18:12:

попробую...

типа того... :) :) :)

-~{}~ 02.05.08 18:42:

"200 000 работает за 0,5 сек... для меня это вполне..."

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

berkut

Новичок
ты сам себе не веришь что-ли или как?

-~{}~ 02.05.08 19:53:

первая выборка всегда дольше, ибо кэша нету есчо.
 

ilal

Новичок
да... себе не верю... :)

первая выборка по этому запросу? значит, каждый посетитель первый раз будет по 20 сек ждать? я правильно понял???

мама моя... роди меня обратно...

-~{}~ 02.05.08 19:29:

ведь запрос будет у всех свой... а работать с такой таблицей... хм... вообще задница будет... неужели так долго работает?
 

ilal

Новичок
Автор оригинала: berkut
творй призыв крайне точен, полон и необходим
Нажмите для раскрытия...
посмеялся :))) какой именно? роди обратно или о помощи? я понимаю, что окружающим проще, чтоб мама постаралась... но я подумал, что форум по теме, помогут...
 

berkut

Новичок
тебе гас всё уже разжевал. а по поводу "первого раза" - протестируй ёмаё, если так не допетриваешь. для разных юзеров что будет разным? $uu. вот и тестируй с разными уу, чё получится.
 

ilal

Новичок
Автор оригинала: berkut
тебе гас всё уже разжевал. а по поводу "первого раза" - протестируй ёмаё, если так не допетриваешь. для разных юзеров что будет разным? $uu. вот и тестируй с разными уу, чё получится.
Нажмите для раскрытия...
так вот в том и раскоряка... у разных юзеров все так же как и у первого- макс 0,5 сек... но первый запуск... 20 сек... совершенно не понятно... база должна так тормозить? или на что-то другое грешить?
 

berkut

Новичок
етить крутить.. вот это не совсем понятно
у разных юзеров все так же как и у первого- макс 0,5 сек... но первый запуск... 20 сек..
Нажмите для раскрытия...
но скорее всего значит всё пипец. смотрим и пробуем сюда:
http://phpclub.ru/talk/showthread.php?postid=793652#post793652
а по-хорошему, ввёл бы дополнительную таблицу, в которой хранил для каждого "разговора" необходимую информацию.
Нажмите для раскрытия...
внимай гасу, он безбожно крут и непобедим в муське
 
Войдите или зарегистрируйтесь для ответа.
Сверху