By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,592 Members | 1,931 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,592 IT Pros & Developers. It's quick & easy.

Is SELECT speed dependent on record size?

P: n/a
My current Mysql database has a table with a Text field and a few blob
fields. The average record can range from 500kb to 5mg, the bulk
coming from the blob fields. I select from each record based on words
in the Text field, but after about 3000 records, the search takes too
long. If I setup another table that has just the text field and shares
a key with a table containing the blobs, will it be quicker to search
the Text field?

In other words, does having larger records slow down my search in the
Text field?

-Ace
Jul 19 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Ok, I found another thread that mentioned that MyISAM reads the whole
record when queried. So if I go with splitting the table into an Index
and Content table that have a common key (docID), will the query:

SELECT some_field FROM Index, Content WHERE MATCH(field_in_Index)
AGAINST (‘word') AND Index.docID = Content.docID;

still be as slow since I'm searching in Content for its docID?
Thank you,
Ace
Jul 19 '05 #2

P: n/a
Ok, I found another thread that mentioned that MyISAM reads the whole
record when queried. So if I go with splitting the table into an Index
and Content table that have a common key (docID), will the query:

SELECT some_field FROM Index, Content WHERE MATCH(field_in_Index)
AGAINST (‘word') AND Index.docID = Content.docID;

still be as slow since I'm searching in Content for its docID?
Thank you,
Ace
Jul 19 '05 #3

P: n/a
Ace Alexander wrote:
Ok, I found another thread that mentioned that MyISAM reads the whole
record when queried. So if I go with splitting the table into an Index
and Content table that have a common key (docID), will the query:

SELECT some_field FROM Index, Content WHERE MATCH(field_in_Index)
AGAINST (‘word') AND Index.docID = Content.docID;

still be as slow since I'm searching in Content for its docID?
Thank you,
Ace


I'm a newbie with MySQL (about six months) - I'm pretty sure that your
table/index will speed the query, however it has more to do with the
index than it has to do with the record size... Someone else might
correct me on this...

One suggestion though: If you're only wanting to read a single record,
use LIMIT in your SELECT otherwise the *full* database/table is read
because it will be searching for multiple records. This should speed
things up significantly.

Hope the above helps...

randell d.
Jul 19 '05 #4

P: n/a
Ace Alexander wrote:
Ok, I found another thread that mentioned that MyISAM reads the whole
record when queried. So if I go with splitting the table into an Index
and Content table that have a common key (docID), will the query:

SELECT some_field FROM Index, Content WHERE MATCH(field_in_Index)
AGAINST (‘word') AND Index.docID = Content.docID;

still be as slow since I'm searching in Content for its docID?
Thank you,
Ace


I'm a newbie with MySQL (about six months) - I'm pretty sure that your
table/index will speed the query, however it has more to do with the
index than it has to do with the record size... Someone else might
correct me on this...

One suggestion though: If you're only wanting to read a single record,
use LIMIT in your SELECT otherwise the *full* database/table is read
because it will be searching for multiple records. This should speed
things up significantly.

Hope the above helps...

randell d.
Jul 19 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.