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 SSTART 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