Paul Wagorn wrote:
> We are running mySQL on a dual Xeon 450 with 1MB cache, and 1GB of RAM &
> Raid5 18GB. Linux 2.2.10.
> We are finding a few things that bother us....
> first, when we run a simple select statement on a table with 1-2 million
> rows, it takes up 50% of the processor(s).
> (& about a minute!!!!) and slows everything else down. ( below is a
> description of the file.....)
> Also, it NEVER uses more than 50% of the processor. Could it be that it has
> not been compiled for dual processor support?
> is there any way to check? Is there anything I should think about in terms
> of caching, swapfiles, etc? Obviously we're missing something important.
> indexing the table on the column that is being looked at doesn't seem to
> make ANY difference at all. (?!)
This means that the query cannot use the index.
> It seems that we are usually running with around one hundred megs of ram
> free, and even when that select statement fires up and the load average goes
> up, the free mem
> stays around 90 megs. We're not even using any swap!
> the file is made up of about 4 columns, nothing too big. the select
> statement looks like this:
> select count(*) from $filename where date like '1999-06-%'"
> .....? any ideas?
What type is your date? If it is DATE, mysql cannot use the index - it
has to do a full table scan, convert each DATE to a string and to a
comparison to see if it matches the pattern.
What Frazier suggested ( select count(*) from $filename where date >=
'1999-06-01' and date <= '1999-06-30' ) will use the index and will be a