In the last episode (Sep 12), Joe Kaiping said:
> Hi there,
> I'm having trouble configuring MySQL 3.23.36 running on a Sparc/SunOS 5.8 to
> use the available 1 gig of memory and was wondering if anyone might be able
> to help find a solution.
> /usr/ucb/ps -aux gives the stats:
> USER PID %CPU %MEM SZ RSS TT S START TIME COMMAND
> root 2120 36.6 7.044881669800 ? O Sep 11 16:46
> when a single user is running a simple query like:
> SELECT count(id) FROM ind WHERE cust=1 AND email<>'';
> This query takes 8.29 seconds to run and there are less than 200,000
> records in the ind table. Different queries don't change the
> percentage of memory being used, only the CPU usage seems to change.
Mysql does not cache table data for the MyISAM type, so you won't see
any memory change there. What does an EXPLAIN SELECT.. print for the
> I tried adding an index to the ind table for the cust and email
> fields, but the query took even longer since about 175,000 records
> have nonempty email fields.
A single 2-column index, or two separate indexes? Mysql can only use
one index per query, so a compound index would help the most here.
If your problem truly is disk I/O, you can try using the InnoDB table
type, which caches both index and table data in memory.