469,600 Members | 2,178 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,600 developers. It's quick & easy.

Need help with optimizing / speeding up large MySQL MyISAM tables


I'm somewhat new to MySql. I've been using it for a while, but pretty much
out of the box setup, and am starting to suffer heavily with my larger
tables. I have a table with 5,000,000+ rows that I have to search and do
joins on. Although I have an index set up for it, the joined select will
still take some 400+ seconds to return, which is obviously unacceptable.
This is due to enormous HD access.

Perhaps someone can help me with indexes here; I was under
the impression that the index for the tables are cached in memory, and
therefore permitted "instant" searchability, without having to retrieve data
from each of the rows of the DB. Is there a startup parameter, or
something in the mysql.ini file, that must be set to allow for this? I have
the index configured properly, and have made sure that the query uses there
parameters in the where clause in the same order that they appear in the

I've tried to do an explain on the select statement and I get the following:
table: record
type: ALL
possible_keys: PRIMARY
key: (NULL)
key_len: (NULL)
ref: (NULL)
rows: 50644
Extra: Using temporary; Using filesort

table: table0
type: ref
recordid,recordid_idxfield_idxvalue,Fixed_fields_o nly,Fixed_fields_and_gener
key: Fixed_fields_only
key_len: 20
ref: record.recordid
rows: 114
Extra: Using where; Using index
My select statement is as follows:
SELECT record.* FROM record as record, index2 as table0 WHERE
(record.recordid = table0.recordid) AND ( ( ( table0.recordid IS NULL OR
table0.recordid IS NOT NULL ) ) and ( ( table0.transid IS NULL OR
table0.transid IS NOT NULL ) ) and ( table0.formid = '4' ) and ( (
table0.clientid IS NULL OR table0.clientid IS NOT NULL ) ) and (
table0.active_recordid IS NULL ) and ( CAST( table0.issaved AS SIGNED) =
1.0 ) and ( ( table0.isclosed IS NULL OR table0.isclosed IS NOT NULL ) ) and
( CAST( table0.isdeleted AS SIGNED) = 0.0 ) ) ORDER BY table0.recordid ;
As you can see, the only reference I am making to table record is to the
field record.recordid, which is defined as my primary key for the table. So
by definition, there is already the primary key index for that table. I
don't understand why it is listing (NULL) for the key that it is using.
I've already tried running an "analyze" on both tables. All the "IS NULL or
IS NOT NULL" is to ensure that the field is being searched to match the
field ordering in the index. Is this redundant?

Additionally, is there anything else I can do to speed up my queries /
indexing and reduce the amount of disk access?

Is there a way of forcing/requesting mysql to cache entire tables in memory?
Can one specify which tables to cache in memory?

Thanks for any insight,

Jul 19 '05 #1
0 1694

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Gordon | last post: by
2 posts views Thread by Fabien Penso | last post: by
2 posts views Thread by Shashikant Kore | last post: by
1 post views Thread by coyote2002 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.