Hi,
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
index.
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
possible_keys:
recordid,recordid_idxfield_idxvalue,Fixed_fields_o nly,Fixed_fields_and_gener
ic_fields
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,
Eric
