I can get Substring to work, but how does it work below the surface...

[falconite]

In a perl script, I'm reading in data from a mysql server database that has some fields that could be millions of bytes long (DNA sequences). However, I am interested only in the first 1000 or so bases (characters) - and I already know how to use SUBSTRING() to select those.

My question deals more with performance: How does SUBSTRING() actually work? I don't want to read in the whole field, then take a portion of it to get my end result, because reading in the rest of the field is just wasted time/effort. And when I only want 1000 bases out of millions, that's a lot of wasted time. Since locally I don't have the string already saved, does the mysql SUBSTRING command (within a larger SELECT statement) get all of the field, or does it find the beginning and just read up to what I've specified.

Any suggestions for other ways around this problem?

I'm running an openSUSE Linux distribution, if that's relevant at all.

Thanks

[Atli]

Well, I'd imagine that MySQL just gets the part of the string you want, but there is not way I can be sure.

If you have millions of bytes in some table, you can easilly test it.
Just do a query that fetches like 1/4 of each field in a number of rows, then do the same with the whole fields and measure the difference.
The first test should only take 1/4'th the time of the second test.

[pbmods]

Heya, falconite.

I'm assuming that your fields are TEXTs, which means that you have to specify an index length. As long as you are indexing at least the first 1000 (or so) characters, your queries should execute reasonably quickly.

You may need a hard drive the size of a planet, but your queries should execute fairly quickly :)

You can alternatively use LEFT(). Not sure if it's any faster than SUBSTRING(), but I would presume that it would save a couple of instructions b/c MySQL wouldn't have to check to see if the starting index is 0.