GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

performance with varchar vs tinytext

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • #16
    The reason why it doesn't use the index for your text/varchar columns is because you have defined the index to contain only the first 100 (for tinytext) and first 7 characters (for varchar).

    This means that mysql will have to read each row of data from the table anyway to get the complete string.
    And since reading randomly is a very slow operation it chooses to go with reading all data and sort it instead.

    Comment


    • #17
      ok I gathered that it does use index if I use the whole length of column for creating the index (post #3 of this thread). So what you are saying then is that there is no way to force mysql to use the index(created using leading x chars) when doing a simple order by.

      Comment


      • #18
        myrddin wrote on Tue, 12 June 2007 17:01

        ok I gathered that it does use index if I use the whole length of column for creating the index (post #3 of this thread).


        yes
        myrddin wrote on Tue, 12 June 2007 17:01


        So what you are saying then is that there is no way to force mysql to use the index(created using leading x chars) when doing a simple order by.

        The thing is that the index does not contain enough data to perform a proper sort on since it only contains truncated strings.
        Consider this:
        Your column is VARCHAR(10) index is (4).
        Data i table:
        Quote:


        1234A
        1234Z


        How will it be able to use the index to sort that data properly if the index only contains the first 4 characters?

        I'm curious why you want it to use the index for a "select * from foo order by"?
        Because since you are selecting all rows it will impose a lot of random reads of the table and they are very expensive.
        So I don't think that you will gain anything.

        But back to your original question you should avoid text/blob columns unless you need them.
        Other cases use VARCHAR for strings.
        The big reason here is what post #2 said about temporary tables since text/blob is intended to be large objects.

        What you said about TEXT/BLOB being handled differently internally in the database is true. Blob objects are allocated in a different way which means that you will get more seeks.

        So the recommendation is that you avoid BLOB/TEXT columns unless you really need them.

        Comment


        • #19
          Quote:


          How will it be able to use the index to sort that data properly if the index only contains the first 4 characters?


          I figured if you issue a force index it will use the index to sort granted in cases like you mentioned those parts of the sort will be undefined(i.e. it will do a sort based on those 4 chars only, after 4 chars is undefined). But apparently thats not how it works.
          Quote:


          I'm curious why you want it to use the index for a "select * from foo order by"?
          Because since you are selecting all rows it will impose a lot of random reads of the table and they are very expensive.
          So I don't think that you will gain anything.


          My actual table has about 200+ cols and has about 200K rows right now and will be increasing. I will be selecting approximately 15 cols(of both numeric and text types) from that and will be doing an order by and limit(x,100). Are you saying that using the index in such a case will be slower than just sticking with filesort?
          Quote:


          So the recommendation is that you avoid BLOB/TEXT columns unless you really need them.


          I know I got sidetracked there and thanks for getting back on topic. I will be following that recommendation.

          I appreciate all the help/suggestions everyone. Thanks!

          Comment

          Working...
          X