How to setup a MySQL Table to keep track of stats?

[l3vi]

I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

[Gordon Burditt]

>I want to be able to keep records of how many times a keyword was

>searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

This depends a lot on what kind of statistics you want.

Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover") . You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.

So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .

[l3vi]

I ran into a guy in IRC that helped me get it worked out.

This is what we came up with

Table 1:
ID | Keyword

Tabl 2:
ID | Count | DateTime

If it is a new search we will store the search phrase into table 1, and
then add one hit to table 2. If its the same search on the same date
then we will add to the count in table 2, otherwise we add a new entry
for a new day and start the count over.

Then we will use replication (Im learning about that) to copy it to
another DB for front end usage.

Im going to use BigInt(20), DateTime, and VarChar(80) on the above
tables. Dont know if DateTime is best for tracking days or not, but it
has worked well in the past.

Sound good?

Gordon Burditt wrote:

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

This depends a lot on what kind of statistics you want.

Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover") . You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.

So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .

[davie]

l3vi wrote:

I ran into a guy in IRC that helped me get it worked out.

This is what we came up with

Table 1:
ID | Keyword

Tabl 2:
ID | Count | DateTime

If it is a new search we will store the search phrase into table 1, and
then add one hit to table 2. If its the same search on the same date
then we will add to the count in table 2, otherwise we add a new entry
for a new day and start the count over.

Then we will use replication (Im learning about that) to copy it to
another DB for front end usage.

explodeIm going to use BigInt(20), DateTime, and VarChar(80) on the
above

tables. Dont know if DateTime is best for tracking days or not, but it
has worked well in the past.

Sound good?

Gordon Burditt wrote:

>I want to be able to keep records of how many times a keyword was
>searched for daily, and from that I can calculate weekly and monthly.
>
>At this point I have one entry per search phrase with the number of
>hits the search phrase has gotten, and the last time it was updated.
>
>As I start to take the program out of testing and move in more into a
>used tool Im getting worried that my idea of switching to mutable
>entries for the same search phrase would be a bad idea as within the
>last 15 days I have stored more then 3 million unique search phrases,
>and a unknown number of hits.
>
>So should I make a rolling database that stores each search then rolls
>that data over to another database once a week that users can use, or
>is their a better way of doing it where I only keep one entry per
>search phrase and am still able to keep records how daily search
>amounts so that I can track trends, etc in search phases on my sites?
>
>My plan to have a 3-5 month window that I can look at data on search
>information.

This depends a lot on what kind of statistics you want.

Think about how an odometer works. The count for a given car just
keeps going up and up and up (hopefully you use a data type big
enough so you avoid "rollover") . You get the counts in a given
period by subtracting the readings at the beginning and the end of
the period.

So, if you take snapshots of the active count table each month or
week or whatever, you can get counts for any month. You could
duplicate the whole table, or have one table with count entries
for current, Jan 1, Feb 1, Mar 1, ... .

I am assuming you are using PHP
Here what i would do
Split search string using explode()
Remove noise words such as the,my,are,and, .when ,so,of,there,et c
see http://drupal.org/node/1202
You can modify word list to suit.
Store remaining word in database as suggested
You may find functions such as metaphone() and levenshtein() useful
see http://www.php.net/manual/en/ref.strings.php for PHP
ASP and other languages have similar functions

[Norman Peelman]

"l3vi" <ad***@boogybon bon.comwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...

I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

Try:

CREATE TABLE `searches` (
`search_term` char(255) NOT NULL default '',
`search_date` date default NULL,
`search_count` bigint(20) unsigned NOT NULL default '1',
PRIMARY KEY (`search_term`, `search_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT is now:

$query = "INSERT INTO searches (search_term, search_date) VALUES
('$search_term' , CURRENT_DATE()) ON DUPLICATE KEY UPDATE search_count =
search_count + 1";
....this will give you nothing but unique entries for any search term on any
particular date and their counts. You can then create your backups with
something like:

CREATE TABLE table_name (PRIMARY KEY(search_term , search_date)) SELECT *
FROM searches WHERE search_date = '????-??-??'


Norm

[Norman Peelman]

--
FREE Avatar hosting at www.easyavatar.com
"l3vi" <ad***@boogybon bon.comwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...

I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

update---

----- Original Message -----
From: "l3vi" <ad***@boogybon bon.com>
Newsgroups: mailing.databas e.mysql
Sent: Tuesday, August 15, 2006 10:06 PM
Subject: How to setup a MySQL Table to keep track of stats?

I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

Try:

CREATE TABLE `searches` (
`search_term` char(255) NOT NULL default '',
`search_date` date default NULL,
`search_count` bigint(20) unsigned NOT NULL default '1',
PRIMARY KEY (`search_term`, `search_date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT is now:

$query = "INSERT INTO searches (search_term, search_date) VALUES
('$search_term' , CURRENT_DATE()) ON DUPLICATE KEY UPDATE search_count =
search_count + 1";
....this will give you nothing but unique entries for any search term on any
particular date and their counts. You can then create your backups with
something like:

$table_name = 'searches_'.dat e('Ymd'); // yearmonthday
$backup_date = date('Y-m-d'); // year-month-day

$query = "CREATE TABLE $table_name (PRIMARY KEY(search_term , search_date))
SELECT *
FROM searches WHERE search_date = '$backup_date'" ;


Norm

[Norman Peelman]

--
FREE Avatar hosting at www.easyavatar.com
"l3vi" <ad***@boogybon bon.comwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...

I have a new system Im building that stores entries of what people are
searching for on my sites.

I want to be able to keep records of how many times a keyword was
searched for daily, and from that I can calculate weekly and monthly.

At this point I have one entry per search phrase with the number of
hits the search phrase has gotten, and the last time it was updated.

As I start to take the program out of testing and move in more into a
used tool Im getting worried that my idea of switching to mutable
entries for the same search phrase would be a bad idea as within the
last 15 days I have stored more then 3 million unique search phrases,
and a unknown number of hits.

So should I make a rolling database that stores each search then rolls
that data over to another database once a week that users can use, or
is their a better way of doing it where I only keep one entry per
search phrase and am still able to keep records how daily search
amounts so that I can track trends, etc in search phases on my sites?

My plan to have a 3-5 month window that I can look at data on search
information.

Or even:

....this will give you nothing but unique entries for any search term on any
particular date and their counts. You can then create your backups with
something like:

$table_name = 'searches_'.dat e('md'); // yearmonthday
$backup_date = date('m'); // month

$query = "CREATE TABLE $table_name (PRIMARY KEY(search_term , search_date))
SELECT *
FROM searches WHERE search_date LIKE '2006-%$backup_date%' ";

For a one month backup out of 2006

-or-
....this will give you nothing but unique entries for any search term on any
particular date and their counts. You can then create your backups with
something like:

$year = ???; // four digit year;
$start_month = ???; // two digit month, ex: 06
$end_month = ???; // two digit month, ex: 08
$table_name = "searches_$year _months_$start_ month_through_$ end_month";

$query = "CREATE TABLE $table_name (PRIMARY KEY(search_term , search_date))
SELECT * FROM searches WHERE search_date >= '$year-$start_month-01' AND
search_date <= '$year-$end_month-31'";

For a three month backup, you get the idea. :)
Norm

[l3vi]

This is what I went with:

CREATE TABLE `keyword_count` (
`Num` bigint(20) NOT NULL auto_increment,
`ID` bigint(20) NOT NULL default '0',
`Count` int(20) NOT NULL default '0',
`Date` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`Num`),
KEY `ID` (`ID`),
KEY `Date` (`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `keyword_lexico n` (
`ID` bigint(20) NOT NULL auto_increment,
`Keyword` varchar(80) NOT NULL default '',
PRIMARY KEY (`ID`),
KEY `Keyword` (`Keyword`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

>From this I am able to search by any days I want, without doing backups

and I am able to remove entries that have expire over x amount of time.
Ill prob being doing a DB dump and import to another db once a day so
that the backend processes are not conflicting with users processes.

Right now Im working on what chars are blocked, and what chars are
replaced.. < That looks like it will be a ongoing event. LOL

[l3vi]

I should note that the keyword field is set to unique, but it not
listed in the above sql.

I do have one problem that i can not figure out.

Before I insert or update anything I run:

SELECT ID FROM keyword_lexicon WHERE Keyword LIKE ? LIMIT 1

I have been getting back that it did not find a match, but when it goes
to insert I get Duplicate entry 'how to find some keywords' for key 2.

I check the DB and it is dupe, but what I dont get is why the first sql
does not catch it even with = or LIKE.

[Norman Peelman]

"l3vi" <ad***@boogybon bon.comwrote in message
news:11******** **************@ m79g2000cwm.goo glegroups.com.. .

I should note that the keyword field is set to unique, but it not
listed in the above sql.

I do have one problem that i can not figure out.

Before I insert or update anything I run:

SELECT ID FROM keyword_lexicon WHERE Keyword LIKE ? LIMIT 1

I have been getting back that it did not find a match, but when it goes
to insert I get Duplicate entry 'how to find some keywords' for key 2.

I check the DB and it is dupe, but what I dont get is why the first sql
does not catch it even with = or LIKE.

LIKE '%match text%'

But this is what i'm telling you about in the other post... you are doing
something manually that mysql will do for you. Load up my CREATE statement
and test some INSERTS... there are no ID's to look up...

Norm