Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2009
    Posts
    21

    Unanswered: Varchar length - performance

    Hi

    I am running some queries of the type SELECT * WHERE myvarchar LIKE '%something%'
    The database uses myIsam and the Varchar has now fixed length 8000
    The values i actually use for this field are at least 100 times shorter than 8000.

    Does the length of that field plays any role in the performance?
    If i use joins in the query does the length affects the performance?
    Last edited by sakis; 06-28-13 at 07:49.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you will almost certainly be better off using a full text search if this is soemthingn you expect to do reasonably often
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2009
    Posts
    21
    Thank you

    But does the length of that field affects the performance?
    Its better to optimize the table before changing the query

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there is no point haveing a text / string /char column that is 100 times alrger than the expected worst case values. that is going to affect the storage space required may affect backups and so on.

    I don't know the precise detaisl of how MySSQL stores char data. but I wouldnt' be surprised if it is smart enough to suys there are 7,200 zero characters and amend its matchign accordingly. however if you are doing a search on the clum,n then indexing may prove a bigger perfromance gain then resizing, especailly on such a large column in the first place
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Be aware that using a statement like SELECT * WHERE myvarchar LIKE '%something%' is never going to use an index because you are looking for something anywhere in the string. If you are searching SELECT * WHERE myvarchar LIKE 'something%' then an index may be used. healdem is correct when he says you should look at using full text searches.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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