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

В таблице есть пять полей (IP(VARCHAR(15), SITE(VARCHAR(300), TIME(DATETIME), COMP(VARCHAR(200), WHERE(VARCHAR(100)). Есть ли какие-нибудь способы, приёмы, как это дело оптимизировать?

Как один из вариантов: Перелить данные за прошлые  квартал/год/месяц в "бекапную" таблицу ...

bobik02, Хорошая идея, только как сделать бекапную таблицу, и в чем её отличия от обычной?

PS: Но ведь популярность проектов растет и новой таблицы хватит только на полгода

А  какие запросы  у Вас там  выполняются ?

скрипт в студию для начала (может что подправить можно)

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

Ну добавления это понятно, а вот с выводом(отчётами) как  код ?

Пару дней назад писал.
У меня тоже ограничения по размеру бд. Так придумал для себя, может конечно сильно грамозко, но разбил на 3 базы:

1:
CREATE TABLE IF NOT EXISTS `stat_osn` (
              `site` varchar(80) NOT NULL,
              `kols` int(15) NOT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - основная'

2:
CREATE TABLE IF NOT EXISTS `stat_ip` (
              `ip` varchar(50) NOT NULL
            ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - уникальные ip'

3:
CREATE TABLE IF NOT EXISTS `stat_mon` (
              `site` varchar(80) NOT NULL,
              `ip` varchar(60),
              `date_in` datetime default '0000-00-00 00:00:00'
            ) ENGINE=MyISAM DEFAULT CHARSET=cp1251 COMMENT='Статистика посещений - за последний месяц подробная'

В 1 записываю для данного сайта kols+1;
Во 2-ю записываю и по ней же сравниваю иникальные ip;
В 3-ю записываю подробный отчет со всеми вхождениями, и храню только определенное время например - месяц:
 $dd1 = date("Y-m-d H:i:s", mktime(0,0,0,date("m")-1,date("d"),  date("Y")));
 $SQL2="delete from `stat_mon` where `date_in`<'$dd1'";
 $r2 = mysql_query($SQL2);

А дальше информер рисую с двумя цифрами, уникальных ip и количество посещений из 1 таблицы.

Это сообщение отредактировал(а) password - 23.9.2008, 18:31

$q_stat=mysql_query("select `stat`, `where`, `year` from `stat` WHERE `status` = '1' and `category` LIKE '%$categ%' order by `id` desc LIMIT $col2, 20") or die(mysql_error());

Индексы все нужные в таблице созданы?

Какие индексы?

http://www.mysql.ru/docs/man/MySQL_indexes.html

Для огромных таблиц - нужная вещь.. Позволяет ускорить время выполнения запросов. 

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

Int + ip2long 


желательно от like избавиться. Он занимает много времени.


солидарен. На мой взгляд лучшее решение. Как только основная таблица начинает тормозить, переносить часть/все ее данные в бэкапную. 

А вообще конечно это большой поток информации. У меня на сайте начало тормозить после того как таблица выросла далеко за 1гб. 

Это сообщение отредактировал(а) mr.fox - 24.9.2008, 00:37

teroni, там очень плохо написано, я совсем не понял, для чего нужны индексы, и как они работают. Может ты объяснишь?

Есть вопрос, как быстро превратить таблицу в бекапную средствами HeidiSQL?

И ещё вопрос: может просто не надо записывать посещения сайта ботами?



Это сообщение отредактировал(а) m1kle - 26.9.2008, 13:57

m1kle, о индексах: http://kurepin.ru/php/index/

Кое-как разобрался с индексами.
select * from `enters` WHERE `time` LIKE '%".mysql_real_escape_string($tim)."%' $bot order by `".mysql_real_escape_string($o)."` ".mysql_real_escape_string($d)."

Использую вот такой запрос. Данные за год он считал до введения индекса за 8 секунд, после введения индекса - также. Непонимаю, почему.

*Думаю стоит удалить все данные о ботах, которые заходили на сайты.
*Еще появилась идея не записывать в базу время, только даты.

Вопрос: Как сделать тип данной IP?

Это сообщение отредактировал(а) m1kle - 1.10.2008, 18:00

select * from `enters` limit 30
долго выполняеться такой запрос?

Валерия, 0,172 сек.

Так для любой базы, даже маленькой.

Что за $bot?
Вообще что тебе нужно сделать со всеми этими записями? Вывести? Тогда делай постранично.

$bot - это если пользователь выбрал не показывать роботов, которые посетили сайт, ему их не показывают.

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

Ну смотри, если ты говоришь что у тебя база 60 мб, то ты на страницу так много выводишь о_О?
По крайней мере делай не 
select *
а перечисляй только те поля что выводишь, уже меньше мускул будет в пхп инфы передавать. Только не говори что нужно все поля и выводить)))

Это сообщение отредактировал(а) Валерия - 1.10.2008, 19:25

Да, нужно все поля выводить.

Я думаю, можно съэкономить на удалении ботов(80% базы), и сделать дату без времени. Еще перевести IP в тип IP.

Мне просто интересно сколько у тебя html страничка сгенерированная грузиться с такой кучей инфы)
Сделай делит там где встречаеться в useragent слово bot (кажись все поисковики дают юзерагент типа google-bot yandex-bot, но не уверена)

Валерия, я знаю, как их распозновать и думаю не только удалить их из базы, но и не записывать новых ботов.

Разобрался, удалил всех ботов. Количество записей сократилось в два раза.

Теперь нужно не записывать новых ботов. Вопрос, можно как-то через MySQL прописать, что поле не может принимать определенные значения.

Например поле `beta` не может принимать значения, которые включают в себя "%alpha%"

Также есть вопрос, как сделать поле типа IP, с помощью MySQL?

Появился еще вопрос: как ограничит Базу данных до 50 записей?

*После предпринятых мер, время загрузки сократилось на треть.

Это сообщение отредактировал(а) m1kle - 9.10.2008, 18:25

Ап. Тема забытая, а ответы на вопросы не получены.

select и если есть, то не записывать



нет такого типа поля, как и нет типа поля районные центры Хабаровского края. сделай varchar(16), а при поиске простое сравнение

Добавлено через 56 секунд

select count(*)
если больше 50 или больше, не записывать

п.с. в базе таблицы, а уже в таблицах - записи

Вот неплохая  статья ИМХО по оптимизации БД http://stroimsayt.com/drugoe/o-gramotnom-i...i-bd-mysql.html

только триггерами. конечно, если говорить только про использование СУБД. потому как средствами того же PHP подобная логика решается на "раз".

учитывая наличие встроенных функций дя работы с IP: INET_ATON и INET_NTOA, а так же стопудов более быструю индексацию для целых чисел в сравнении с индексом по тексту, учитывающим кодировку и collation, все же советовал бы использовать целочисленное поле под хранение IP

...
ссылка таки открылась и всё стало ясно

Это сообщение отредактировал(а) bars80080 - 10.11.2008, 10:14

bars80080, нет. использовать упомянутые уже inet_ntoa и inet_aton

Почитал приведенную сдесь статью.

Сделал для себя три решения в плане оптимизации:

1) Прекратил записывать поисковые машины в протокол
2) Поставил все записи в режим ожидания
3) Решил разделить таблицу на две части. Тут возник вопрос, ускорит ли это работу, в одной таблице будет хранится айпи, дата, а в другой. Откуда пришел, куда пришел, компьютер пользователя.

а зачем разделение?

а зачем разделение? 

Чтобы таблица быстрее работала, сортировка будет идти по маленькой таблице, а остальные данные приписываться по второй.

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

Да одна запись таблице с ip одна запись с данными о компьютере, и "сайтах".

Это сообщение отредактировал(а) m1kle - 13.11.2008, 17:57

Чтобы не создавать новую тему, спрошу здесь.

Значит есть таблица forumm. Нужно заменить тип поля айди. С инт на тиниинт. Он выдает ошибку "Duplicate entry '127' for key 1". В чем может быть проблема?

Прошу прощения, сам разобрался. Там могут быть значения от -127 до 127.

Это сообщение отредактировал(а) m1kle - 13.11.2008, 19:33

m1kle ( структу бд встудию лучше в новый вопос... 

перепеши все на mysqli на 40% производительней и быстрей чем простые функции mysql_ ... 

да и переписывать не так много на php.su док есть. 

Это сообщение отредактировал(а) Sanchezzz - 14.11.2008, 22:02

Про mysqli можно поподробнее?

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

Значит сделал две таблицы. Enters (Id, Ip, Time) и Entersi (comp, site, where).
Программа, которая выводит данные сортирует все по Enters, и уже припизывает дополнительные данные по Entersi.

это класс в PHP5 

вот самая нормальная дока 

пользоватся просто:



+ ко всему много фичей можно на лету менять запросы (prepare и bind_param) а также создавать шаблоны запросов из простого массива или из прошлого запроса  ну об этом читать в документации подробно еще написано в phpclab.ru. 

единственное главное чтобы на сервере был php5 и mysqli ну на нормальных серверах это есть.

А есть смысл переходить на mysqli используя тот же процедурный стиль написания?
Я переделала свой скрипт на mysqli, разницы в скорости работы не обнаружила 

Валерия, А может не там тормозило? ;-) можно попрофилироваться xdebug'ом и почистить  именно там, где надо...

У меня и так 0.005 сек скрипт в среднем выполняется, но думала, а вдруг быстрее будет )

почитайте тут мне нравится навязывать не буду. 

при мальнеком запросе не уведете разницу в скорости.

Это сообщение отредактировал(а) Sanchezzz - 24.11.2008, 17:45

Sanchezzz, не впечатлило.
Кроме подготовленных запросов, ничего нового не заметила... Да и толку от них тоже не поняла...
Как ними можно сэкономить траффик?

незнаю насчет трафика но безопасность и скорость для меня важно + mysqli реализует работу с БД через ООП: классы и объекты. Что потенциально повышает гибкость кода, скорость.

mysqli более мощное и полное расширения для работы с БД MySQL тоже + 

зашита от иекция +
работа шаблонов +
скорость +
итд.

Ладно хватит сыр бор разводить а то тему в кашу превратили.

Это сообщение отредактировал(а) Sanchezzz - 24.11.2008, 18:30

Тему неплохо бы если бы разделили 

Значит я посмотрел mysqli(), по моим подсчетом если брать в расчет 1000 запросов экономия во времени 0,0004 секунды.

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

Вопрос стандартный: Как еще можно оптимизировать работу Базы данных.

Запрос LIKE '%... никогда не будет использовать индекс. Это всегда fullscan т.к. вы фактически говорите, что с начала может быть любой символ.



1. Что хоанится в поле `category`?
2. Вы перевели IP на int?
3. Приведите, наконец, полную структуру таблиц. Описанная вами структура не совпадает с вашими эе запросами на выюорку.
4. http://habrahabr.ru/blogs/mysql/44608/

Селект я изменил 

Таблицы на данный момент таковы:

Eners(id(int(8)), ip(char(16)), time(date)
Entersi(comp(char(100)), site(char(70)), where(char(70))

m1kle почему вы работаете с полем типа date как с текстовым? Какие значения прихрдят в $tim что нужно использовать like?

может чем-то поможет
http://www.mysql.ru/docs/man/EXPLAIN.html

выкинь лайк используй лучше DATE_ADD() или DATE_FORMAT() 



DATE_ADD() действует точно также только без форматов...
MINUTE DAY MONTH итд 

$tim у меня формата Y-m-d. использовать преведнный тобой метод я готов. Только насколько он прибавит в производительности?

ну думаю прибавит всеж ишит интервал а не совпадения.

ну можно проверить инструментом на скорость запроса и как он грузит систему.
MySQL Query Analyzer

Так тип поиска не сократил время исполнения кода.

что даже 0,1 сек не убавилось. Можно еще сократить поиск если в запросе указывать вазрат только определенных колонок а не всех.

Index po polyu time est?

Добавлено через 1 минуту и 35 секунд
Giv me results of

Вот результат запроса. Индекс по полю time есть.



А там все три колонки нужны.

Я правильно понимаю, что в этом запросе часть 
 будет для каждой строчки перевычисляться DATE_FORMAT и минус 30 дней? Или mysql значительно умнее, чем я о нем думаю и вычислит все это один раз? по идее - ума ему взять неоткуда, функция now() как ни крути - функция...

Начнем с того, что у меня дата без времени указана 

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

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

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