View Full Version : Selecting only a certain portion of text?

04-02-2007, 02:29 AM
When you see a site like Youtube, where the search results all return a headline, a picture, and a "blurb" about the article, are they all creating "blurb" fields in the database or are they somehow fetching only a certain portion of the text from the database?

Or...(although I can't imagine this) are they fetching ALL the text, but only displaying a portion of it?

04-02-2007, 02:14 PM
In their database, they have a LONGTEXT field. That's where the 'blurbs' are placed. Then, you can select only portions of text using the LEFT() function in mySQL.

So, for example, you can pull only the first 20 characters of any field value by using:

SELECT LEFT(testFld,20) 'testFld20' FROM testTbl;

Note that I am using an alias for the field name, or in your coding you will have to look up `left(testFld,20)` rather than `testFld20`.


04-02-2007, 03:58 PM
Pretty cool...

In my case I need a pretty decent sized "blurb" (maybe about twice as long as one you might see in Google), so I guess I'm looking at around 200 chars+.

One question, though. Are there any performance issues? Is MySQL actually calling up all the data and truncating it, or what?

I would just as soon create a "blurb" field if I'll have to take a huge performance hit...

04-02-2007, 04:51 PM
According to MySQL's manual, there are 4 TEXT types. See the manual for more information (too much to post here):

(middle of the page).

However, 200 chars is still within the midrange text-unit, VARCHAR and even just TEXT (both have a 255 char. limit). Whichever you wish to use is based on memory overhead.

The LONGTEXT fields can hold 2^32 characters. For those non-math types, that's 4294967296 characters.

As to performance hit, as long as you only pull the whole record only when you need it and use the LEFT() or MID() functions in the meantime, your server won't be hit too badly when it is queried. MySQL does not pull the whole record, only the "limit" that you have placed on the recordset.


04-02-2007, 06:50 PM
When you create your text field make an index on it of 200 characters, that way the index is searched and not the full record.

alter table yourtablename
add index 200characters (textcolumname(200))

the 200characters is an alias to the index in case you want to drop it later.
the (200) says how many characters to index.

this will speed up your searches significantly by searching the index rather than the actual rows.

Note that by adding this index, your inserts will be slowed down some. That is balanced off of course by the faster search time the index will allow.