Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Selecting only a certain portion of text?

    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?

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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:

    Code:
    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`.

    HTH!

  • #3
    Regular Coder
    Join Date
    May 2006
    Posts
    123
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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...

  • #4
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    According to MySQL's manual, there are 4 TEXT types. See the manual for more information (too much to post here):

    http://dev.mysql.com/doc/refman/5.0/...uirements.html
    (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.

    HTH!

  • #5
    Super Moderator guelphdad's Avatar
    Join Date
    Mar 2006
    Location
    St. Catharines, Ontario Canada
    Posts
    2,633
    Thanks
    4
    Thanked 148 Times in 139 Posts
    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.

    Code:
    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.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •