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

сортировка данных БД, определение позиции записи

jeka!

Просто Member
сортировка данных БД, определение позиции записи

Для сайта знакомств надо сделать сортировку анкет, и определение позиции анкеты в
общем, по региону, по городу.
Может видели, на многих сайтах знакомств уже это сделано, причем работает это так быстро что я немогу предположить на чем и как это реализовано.
Помогите советом, как это можно реализовать, чтобы не слишком грузить БД.
 

YRusinov

Филин Ух
Re: сортировка данных БД, определение позиции записи

Автор оригинала: jeka!
Помогите советом, как это можно реализовать, чтобы не слишком грузить БД.
Нажмите для раскрытия...
Что значит не слишком грузить БД ? Я бы создал на основе соотвествующих таблиц подходящие вьюхи и хранимые процедуры, которые работали в БД. Но не зная конкретных для данного случая ресурсов, что-то более определённое сказать трудно.
 

YRusinov

Филин Ух
Более существенно ускоряют различного рода системные настройки, оптимизация базы. А так они позволяют существенно ускорить процесс разработки.
 

jeka!

Просто Member
"подходящие вьюхи и хранимые процедуры" - первый раз слышу об этом, можно объяснить что это?
про хранимые процедуры немного почитал тут на форуме, это mysql 5, у меня же 3.23.53
Вобще сервер на котором все это дело будет работать дуал ксеон 3ггц, опера 4 гига, винты сата рейд 10, база mysql
Впринципе нехотелось бы отводить весь сервер под этот проект.
Пока запланировалась планка предела в миллион анкет, теми примитивными способами которыми я пытался решить эту задачу, нереально довести даже до 100к анкет.
Пока что ограничился расчетом позиции только для всех записей в целом, сделано это так:
отдельная таблица сортировки
PHP: 
CREATE TABLE `position` (
  `userid` int(10) unsigned NOT NULL default '0',
  `county` smallint(5) unsigned NOT NULL default '0',
  `region` smallint(5) unsigned NOT NULL default '0',
  `sity` smallint(5) unsigned NOT NULL default '0',
  `pos` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`),
  KEY `crs` (`county`,`region`,`sity`),
  KEY `pos` (`pos`)
) TYPE=MyISAM;
Последняя добавленная анкета имеет максимальный номер `pos`, самая нижняя анкета 0
Скрипт по крону сдвигает все записи до 0 и по счету, т.к какието анкеты могут быть подняты выше в процессе работы сайта. определить позицию в такой базе легко выбрал MAX и вычислил на каком месте от MAX конкретная анкета.
Но по такой базе почти нереально определить позицию относительно `county`,`region`,`sity`, да и обновлять таблицу по крону тоже достаточно долго, у меня 100к записей на домашнем компе обновлялись 20 мин.
В общем ниче умнее придумать неудалось, готовых примеров нигде невстречал.
 

alexv

Новичок
3 таблицы:
top_country (countryid,userid,pos), аналогично top_region,top_city
 

alexv

Новичок
а почему тогда ?
`userID` int(10) unsigned NOT NULL default '0',

поля надо по-человечьи называть:)
 
Mr_Max

Mr_Max

Первый класс. Зимние каникулы ^_^
Команда форума
1. Вы линкуете таблицу `position` с таблицой юзеров?
Тогда, думаю, что поля `county`,`region`,`sity` в таблице `position`Вам совсем не нужны. (они же скорее всего есть уже таблице юзеров).

2. В таблице `position`я бы хранил только "поднятие" анкет. Насколько я понимаю могут же быть анкеты без данного "поднятия". Соответственно количество записей в `position` будет меньше, обновление быстрее.

3.
Но по такой базе почти нереально определить позицию относительно `county`,`region`,`sity`
Нажмите для раскрытия...
SELECT * FROM table WHERE sity=123 ORDER BY pos DESC
вам чем не подходит (в случае с `county`,`region`,`sity`в Вашей таблце)

или
SELECT ..... FROM users where `sity`='value'

+ LEFT JOIN `position`........





-~{}~ 26.05.07 23:56:

4. Скрипт по крону сдвигает все записи до 0 и по счету, т.к какието анкеты могут быть подняты выше в процессе работы сайта. определить позицию в такой базе легко выбрал MAX и вычислил на каком месте от MAX конкретная анкета.

Это вообще непонятно зачем.... Тем-более, что этот скрипт занимает много "времени".
И вообще с почему скрипт по крону должен что-то "сдвигать"?
Зачем?
 

jeka!

Просто Member
сдвигать надо потомучто поле pos должно всегда начинаться с 0 и не иметь дыр, тогда можно считать позицию математикой, а не селектом
если анкета например с номером pos 5 подымется, то останется дыра, номера 5 уже небудет.
вобще мне конечно этот вариант крайне ненравится, т.к при норм онлайне возможно анкеты будут монять позиции очень часто, что скрипт сдвига будет неуспевать быстро обновлять записи и все это будет работать с багом, позиция будет высчитываться неверно.

попробую потестировать пример с 3 таблицами, как написал alexv
 

YRusinov

Филин Ух
Дык, а что мешает держать в БД время создания/изменения и каждый раз при выводе сортировать по вышеупомянутому времени ?
 
Фанат

Фанат

oncle terrible
Команда форума
На Главном сайте знакомств всея руси очевидно кэшируют - как место, так и сортировку (выдачу поиска).
 

jeka!

Просто Member
Я посмотрел несколько сайтов с большим колвом анкет, информация о позиции анкеты меняется почти после каждой перезагрузки страницы, по крайней мере на сайте где анкет почти 800к, мне каждая перезагрузка страницы давала новый результат, причем страница выдается за считанные доли сек.
Пока что без селекта с рекурсией большого кол-ва записей мне ниче подобного неудалось сделать.
Даже не представляю какая будет нагрузка на сервер, если пользоваться этим, когда онлайн станет хотябы 1к и тысяч 500 анкет.
 

chira

Новичок
jeka!
вычисляешь максимальный ID в таблице
SELECT MAX(ID) max_id FROM table
потом выводишь разницу
SELECT max_id-ID FROM table ORDER BY id DESC

думаю не нужно зацикливаться на наличии пропусков ...
вряд ли посещённые тобой сайты делают UPDATE для значений позиции
 
Mr_Max

Mr_Max

Первый класс. Зимние каникулы ^_^
Команда форума
jeka!
1. Груповой индекс по 3-м полям Вам точно нужен?
KEY `crs` (`county`,`region`,`sity`)
----------
В MySQL нельзя использовать частичный индекс, если столбцы не образуют крайний слева префикс этого индекса. Предположим, что имеются команды SELECT, показанные ниже:
...
http://www.mysql.ru/docs/man/MySQL_indexes.html
------------

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

jeka!

Просто Member
Автор оригинала: Mr_Max
jeka!
1. Груповой индекс по 3-м полям Вам точно нужен?
KEY `crs` (`county`,`region`,`sity`)
Нажмите для раскрытия...
Я испоравил индексы, просто незнал как точно изначально буду работать с таблицей.
Оставовился на таком варианте:
PHP: 
CREATE TABLE `position` (
  `userid` int(10) unsigned NOT NULL default '0',
  `county` smallint(5) unsigned NOT NULL default '0',
  `region` smallint(5) unsigned NOT NULL default '0',
  `sity` smallint(5) unsigned NOT NULL default '0',
  `pos` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`userid`),
  KEY `county` (`county`),
  KEY `region` (`region`),
  KEY `sity` (`sity`),
  KEY `pos` (`pos`)
) TYPE=MyISAM;
Позицию определяю для всех:
PHP: 
$maxpos = mysql_result(mysql_query("SELECT MAX(`pos`) FROM `position`"),0);
$userpos = mysql_result(mysql_query("SELECT `pos` FROM `position` WHERE `userid`=5230"),0);
$Allpos = $maxpos-$userpos+1;
Для страны, региона, города:
PHP: 
$Countypos = "SELECT count(*) FROM `position` WHERE `county`=".$UserCounty." AND `pos` > ".$userpos;
$Regionpos = "SELECT count(*) FROM `position` WHERE `region`=".$UserRegion." AND `pos` > ".$userpos;
$Sitypos = "SELECT count(*) FROM `position` WHERE `sity`=".$UserSity." AND `pos` > ".$userpos;
просто изначально расчитывал, что возможно есть вариант определения позиции какамто математическим способом, чтобы неделать лишние запросы к БД
 

Mols

Новичок
Хм... я может чего то не так понял... но...
наверняка при показе анкет используется конструкция LIMIT.... и там достаточно информации для того, что бы сказать какой у кого номер.... для заданного WHERE... поместив в это условие код города региона или страны - получим положение соответсвенно для города региона или страны....
А вопросик такой у меня... поля регион и город... они уникальны в пределах всех стран ? или уникальны в пределах одной страны ?
 
Mr_Max

Mr_Max

Первый класс. Зимние каникулы ^_^
Команда форума
на сайтах знакомств... Рейтнг видел...
А вот позиции ...
Сортировка насколько я понял идёт у них по рейтингу.
а номера позиции возможно в БД то и нет...

Вывод что-то типа этого
$p=0;
$p_num = 10;

SELECT fields... FROM position
ORDER BY user_view/$all_view_sum DESC
LIMIT $p, $p_num
А уже при выводе проставляется номер анкеты в зависимости от страницы
$i = 1;
while ($data = mysql_fetch_.....($sql)){
echo 'Позиция #'.$p*$p_num+$i;
$i++;
}




-~{}~ 29.05.07 22:17:

а выводется по принцыпу "количества" просмотров...



В смысле рейтин через кол.просмотров считается
 

jeka!

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

Mols :

Таблица с городами, регионами у меня такая:
PHP: 
CREATE TABLE `country` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `pid` int(10) unsigned NOT NULL default '0',
  `title` char(35) NOT NULL default '',
  PRIMARY KEY  (`id`),
  KEY `pid` (`pid`)
) TYPE=MyISAM;
соответственно pid это id родительской категории, в этой таблице записаны, страны, регионы, города.
делай вывод сам.

-~{}~ 31.05.07 13:04:

Сейчас мой предыдущий вариант расчитывает позиции для самой низшей анкеты за 0.2 сек. для базы из 100к анкет, на домашнем компе, думаю на сервере будет несколько быстрее и + кэширование расчетов на 15 мин.
Пока оставил так, может кто предложит лучший вариант структуры ДБ + расчет.
 
Войдите или зарегистрируйтесь для ответа.
Сверху