[ ... ]
The point is to evaluate a string to some single value, store it in
database field and the, using simple sql compare, retrieve only those
records with fields, which have 't' letter in it.
There must be no functions, as there is 6mil records and a fetch must
be quick.
Using (or not using) functions won't make any real difference here. For
what you're doing, CPU time is virtually guaranteed to mean nothing
compared to I/O time -- if you use a dozen functions to avoid even a
little bit of I/O, it'll be a win.
Since SQL (as such) is off-topic, I'll discuss this in C++ terms, and
leave it to you to translate over to SQL (though the translation is
_mostly_ fairly trivial).
I'm going to assume that you're willing to consume some time up-front to
build an index that gives you really fast retrieval later. In that case,
let's represent your database as a vector of records:
class record {
std::string key;
void *data; // this just represents whatever other
// data goes with your key.
};
std::vector<record> database;
Now, our index is going to be a vector indexed by a character, and what
it returns as a result will be a set of records that have that character
in the key:
typedef std::set<std::size_t> record_set;
std::vector<record_set> index;
Now, we need to build our index from our data. We do that by looking at
each item in the database, and adding its record number to the
appropriate places in the index:
// for each item in the database
for (int i=0; i<database.size(); i++) {
std::string const &s = database
.key;
// for each character in the key:
for (int j=0; j<key.size(); j++) {
char ch = std::toupper(key[j]);
if (std::isupper(ch))
index[ch].insert(std::toupper(i));
}
}
Now, "index['t']" is the set of all records that have 't' somewhere in
their string. For example, to print out the record numbers of all the
records that have 't' in their key, we could do something like this:
record_set records = index['t'];
std::copy(records.begin(), records.end(),
std:
stream_iterator<std::size_t>(std::cout, "\n"));
Now, as to the conversion to SQL: the main thing is that you don't have
record numbers, so you typically include a field that acts as a unique
key. You'll want to tell your database to index on that field. IIRC, SQL
doesn't have a convenient way of representing a set like I've used
either. You can do that pretty easily with a two-column table, with the
letter in one column and a single record number in the right column.
With the left column indexed, retrieving the set of record numbers with
a particular letter is roughly equivalent to what I've written above.
I should also point out that if a given query is likely to retrieve most
of the records in the database, the index may not do much good. Its
whole point is to read only records you care about. The more records it
avoids reading, the more good it does. If you retrieve all or most of
the records anyway, the index just adds overhead.