CODEWITHSUNDEEP
sqlperformance
punkish
punkish

Reputation: 15238

Fastest way to find exact match of long text string in large SQL db

My db table will have a col "a" TEXT with long strings, like multiple paragraphs. Given an input string, I want to find the one matching record. If the table has millions of rows, what would be faster? A simple 

WHERE a = ?

Or should I calc and store a md5 hash of each row and then match that? Suggestions welcome.

Upvotes: 1

Views: 1306

Answers (2)

JNK
JNK

Reputation: 65157

If you want an exact match, it will be much quicker to store the hash and compare to that. It will preclude substring searches, but it's much quicker to compare say 4 characters than to check thousands.

There will be some overhead to calculate the hash on your search parameter, but it's nothing compared to a string comparison against that much data.

Upvotes: 1

Massimiliano Peluso
Massimiliano Peluso

Reputation: 26737

if you are using SQL Server you could for the Full-Text-Search feature

http://msdn.microsoft.com/en-us/library/ms142571.aspx

Upvotes: 0

Related Questions