Thanks for the quick reply, Dan.
The EXPLAIN SELECT for that SELECT outputs:
mysql> EXPLAIN SELECT count(id) FROM ind WHERE cust=1 AND email<>'';
+-------+------+---------------+------+---------+------+--------+-----------
-+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+------+---------------+------+---------+------+--------+-----------
-+
| ind | ALL | NULL | NULL | NULL | NULL | 181831 | where used
|
+-------+------+---------------+------+---------+------+--------+-----------
-+
I first tried using a 2 field index with slower results and then tried using
separate single field indexes, but that was also slower than not using any
index.
The InnoDB table type sounds promising, so I'll start looking into that.
If anyone else has other suggestions, they really are most appreciated!!
Thanks again,
Joe
> -----Original Message-----
> From: Dan Nelson [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, September 12, 2001 9:17 AM
> To: Joe Kaiping
> Cc: [EMAIL PROTECTED]
> Subject: Re: Can't get MySQL to use available memory (performance very
> slow)
>
>
> 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
> > /u01/opt/MySQL/lib
> >
> > 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
> above query?
>
> > 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.
>
> --
> Dan Nelson
> [EMAIL PROTECTED]
>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
