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

[christopher@dailycrossword.com]

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)