is it better to not index a high-write, low-read table?

Greetings!

The site we just launched tracks usage statistics when every browser
session expires in a simple table:

create table metrics_mysite (
stamp TIMESTAMP,
user_id INT UNSIGNED default 0 NOT NULL,
visit_duration_minutes SMALLINT UNSIGNED default 0 NOT NULL,
page_views SMALLINT UNSIGNED default 0 NOT NULL
);

I am to design the database sufficient to handle tens or even hundreds
of thousands of visits a day, so you can see this table will grow
tremendously. There are only inserts, never updates or deletes.

Extracting statistics will be a management task that may happen
several times a day or less often, with a dozen or so queries like
this:

select count(user_id),sum(visit_duration_minutes) as
duration,sum(page_views) as views from metrics_starprog where
extract(YEAR_MONTH FROM DATE_SUB(CURRENT_DATE,INTERVAL 1
MONTH))=extract(YEAR_MONTH FROM stamp) AND user_id=0;

I believe the only columns in the where clause will be user_id and
stamp.

This was a late addition to the site design and constitutes a massive
increase in database load IMHO. Am I correct that updating an indexed
table of millions of rows is vastly slower than updating one without?
Also, is a query like the one above on an un-indexed table likely to
take longer than seconds? Since we just launched I don't want to test
by loading down the server too heavily, although I may be forced to do
at least some performance profiling just to be sure. New dual xeon
server with 16GB ram, RAID 10, FreeBSD, MySQL5.0 BTW.

Thanx!

On Thu, 06 Sep 2007 08:15:50 -0700,
christopher@dailycrossword.com wrote:

>Greetings!
>
>The site we just launched tracks usage statistics when every browser
>session expires in a simple table:
>
>create table metrics_mysite (
> stamp TIMESTAMP,
> user_id INT UNSIGNED default 0 NOT NULL,
> visit_duration_minutes SMALLINT UNSIGNED default 0 NOT NULL,
> page_views SMALLINT UNSIGNED default 0 NOT NULL
> );
>
>I am to design the database sufficient to handle tens or even hundreds
>of thousands of visits a day, so you can see this table will grow
>tremendously. There are only inserts, never updates or deletes.
>
>Extracting statistics will be a management task that may happen
>several times a day or less often, with a dozen or so queries like
>this:
>
>select count(user_id),sum(visit_duration_minutes) as
>duration,sum(page_views) as views from metrics_starprog where
>extract(YEAR_MONTH FROM DATE_SUB(CURRENT_DATE,INTERVAL 1
>MONTH))=extract(YEAR_MONTH FROM stamp) AND user_id=0;
>
>I believe the only columns in the where clause will be user_id and
>stamp.
>
>This was a late addition to the site design and constitutes a massive
>increase in database load IMHO. Am I correct that updating an indexed
>table of millions of rows is vastly slower than updating one without?
>Also, is a query like the one above on an un-indexed table likely to
>take longer than seconds? Since we just launched I don't want to test
>by loading down the server too heavily, although I may be forced to do
>at least some performance profiling just to be sure. New dual xeon
>server with 16GB ram, RAID 10, FreeBSD, MySQL5.0 BTW.
>
>Thanx!

Indexes on columns of type INTEGER are much faster, both in
inserts as in queries. Express timestamps as unix epoch, only
convert to datetime string for presentation purposes.
Avoid calculations in your WHERE clause, it will make use of
indexes impossible.
Convert search boundaries to unix epoch so the query can be
restricted to integers as well.
Use EXPLAIN to check what happens.
--
( Kees
)
c[_] Invalid thought detected. Close all
mental processes and restart body. (#409)

Thanx !

On Sep 7, 3:02 pm, Kees Nuyt <k.n...@nospam.demon.nl> wrote:
> On Thu, 06 Sep 2007 08:15:50 -0700,
>
>
>
> christop...@dailycrossword.com wrote:
> >Greetings!
>
> >The site we just launched tracks usage statistics when every browser
> >session expires in a simple table:
>
> >create table metrics_mysite (
> > stamp TIMESTAMP,
> > user_id INT UNSIGNED default 0 NOT NULL,
> > visit_duration_minutes SMALLINT UNSIGNED default 0 NOT NULL,
> > page_views SMALLINT UNSIGNED default 0 NOT NULL
> > );
>
> >I am to design the database sufficient to handle tens or even hundreds
> >of thousands of visits a day, so you can see this table will grow
> >tremendously. There are only inserts, never updates or deletes.
>
> >Extracting statistics will be a management task that may happen
> >several times a day or less often, with a dozen or so queries like
> >this:
>
> >select count(user_id),sum(visit_duration_minutes) as
> >duration,sum(page_views) as views from metrics_starprog where
> >extract(YEAR_MONTH FROM DATE_SUB(CURRENT_DATE,INTERVAL 1
> >MONTH))=extract(YEAR_MONTH FROM stamp) AND user_id=0;
>
> >I believe the only columns in the where clause will be user_id and
> >stamp.
>
> >This was a late addition to the site design and constitutes a massive
> >increase in database load IMHO. Am I correct that updating an indexed
> >table of millions of rows is vastly slower than updating one without?
> >Also, is a query like the one above on an un-indexed table likely to
> >take longer than seconds? Since we just launched I don't want to test
> >by loading down the server too heavily, although I may be forced to do
> >at least some performance profiling just to be sure. New dual xeon
> >server with 16GB ram, RAID 10, FreeBSD, MySQL5.0 BTW.
>
> >Thanx!
>
> Indexes on columns of type INTEGER are much faster, both in
> inserts as in queries. Express timestamps as unix epoch, only
> convert to datetime string for presentation purposes.
> Avoid calculations in your WHERE clause, it will make use of
> indexes impossible.
> Convert search boundaries to unix epoch so the query can be
> restricted to integers as well.
> Use EXPLAIN to check what happens.
> --
> ( Kees
> )
> c[_] Invalid thought detected. Close all
> mental processes and restart body. (#409)