MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:John Hinton Date:January 30 2003 2:43pm
Subject:Re: Cardinality doesn't auto start
View as plain text  

"Grigor, Peter" wrote:
> 
> You need to run [analyze table] to create the index stats...do this
> reasonable often (once a day for a well-used table).

This script will be run on servers around the world by regular people,
not sys admins. I will have no control over their databases.

> Running [optimize table] on the table recreates index stats AND
> reorganizes/defrags/coalesces data pages...do this once a week or so.

The one index I have in this database is on a field which contains
TO_DAYS(NOW()). Therefore the return is automatically grouped in the
database as each day is written in order. Is there any value in
optomizing in this situation? Apparently, this index is working pretty
well, as the time to execute the queries has halted at about 12 seconds
on the test site and the time doesn't seem to change after optimizing.
The rows in this database will not be edited. Deletion should always
occur only from the beginning as deletion is be month or year and I
can't imagine anyone not deleting the oldest months first. Will this
operation cause fragmentation? I'm having a bit of trouble between the
order in which I see the data onscreen and how the actual file is
written.
 
> Look up the mysqlcheck utility--it lets you do this from scrips pretty easy.

I see where I can write a script to run these functions, however,
getting an end-user to run a script 'later' after the install, we all
know is not an easy thing. I simply find it frustating that MySQL does
not start its cardinality count automatically when an index is created
at the time the table it created. It has been counting perfectly when I
create the index after data is in the table. There also seems to be a
'isamchk -a' function which is supposed to jump start the cardinality
count. I'm just hoping for a cure to getting this to start by doing
something in the install script where there table is created as well as
many other config files.

Perhaps if cardinality were set to 0 it would begin the count on its
own? I can find very little information regarding cardinality and have
no idea about how one would set it to 0 if that did work. 
> 
> Peter


-- 
John Hinton - Goshen, VA.
http://www.ew3d.com

Those who dance are considered insane 
by those who can't hear the music....
Thread
Cardinality doesn't auto startJohn Hinton29 Jan
RE: Cardinality doesn't auto startPeter Grigor29 Jan
  • Re: Cardinality doesn't auto startJohn Hinton30 Jan