Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-24215

Indexes on text columns need sizes

    XMLWordPrintable

    Details

    • Conversion Script Required:
      Yes

      Description

      reference trunk, r90835, 2.8.x r90836.

      Begin forwarded message:

      From: Anthony Whyte <arwhyte@umich.edu>
      Date: March 28, 2011 9:14:37 PM EDT
      To: Steve Swinsburg <steve.swinsburg@gmail.com>
      Cc: Theriault Seth <slt@columbia.edu>, Sam Ottenhoff <ottenhoff@longsight.com>
      Subject: Re: MySQL: indexing a text column

      A starting point for sure (it would be good to get some data to test this assumption from Rutgers or someone else running 2.8.0 this Spring) . That said, I'll update the MySQL CREATE INDEX statement to

      create index URL_INDEX on URL_RANDOMISED_MAPPINGS_T (URL(200));

      You do the same for the the profile2 scripts. Let me know when you commit.

      Cheers,

      Anth

      On Mar 28, 2011, at 8:45 PM, Steve Swinsburg wrote:

      Interesting.

      So for the index to be of value it would need to be pretty big, I'd say at least 200 chars.

      Here is a fairly long URL:
      https://alliance.anu.edu.au/portal/site/~b7221fb9-ca8b-4e46-00d9-54c58eb81b98/page/e52457ec-53e7-44b6-808e-395e84d3c8e0?toolstate-66ffa861-014c-436b-007d-cb60115ad889=%3Fwicket%3AbookmarkablePage%3D%3Aorg.sakaiproject.profile2.tool.pages.MyFriends

      which is 247 chars long. There are even longer ones that can be generate in the direct links to messages.

      So lets work on 200 chars for the index length, does that sound reasonable?

      Re the varchar2(4000) for Oracle. That field is storing a URL and its possible that a huge URL might be generated so we need a large field to store it.

      cheers,
      Steve

      On 29/03/2011, at 11:41 AM, Anthony Whyte wrote:

      I'm conducting a final review of the conversion scripts. The following MySQL create index statement attempts to index a text column. You can't do that without defining a prefix length that limits the index to the first n bytes of a column value.

      create index URL_INDEX on URL_RANDOMISED_MAPPINGS_T (URL);

      should be something like:

      create index URL_INDEX on URL_RANDOMISED_MAPPINGS_T (URL(10));

      Prefix length is limited to 767 bytes for Innodb tables. Can you choose an appropriate prefix length and then update the profile2 scripts as well as the 2.8 conversion scripts (or send me an email with the prefix length and I will update the conversion scripts).

      Regarding Oracle, is varchar2(4000) an appropriate sizing for the URL field? Is it not over-sized?

      Anth

      http://hrivera99.blogspot.com/2008/05/why-is-varchar2-oversizing-bad.html
      http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3014576500346676264
      https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1145132537055 (long but humorous)
      http://www.orafaq.com/forum/t/57397/2/
      http://www.coderanch.com/t/307419/JDBC/java/Fit-or-not-fit-Oracle

      _____________________________________

      – ShortenedUrlService 1.0.0 db creation start
      create table URL_RANDOMISED_MAPPINGS_T (
      ID bigint not null auto_increment,
      TINY varchar(255) not null,
      URL text not null,
      primary key (ID)
      );

      create index URL_INDEX on URL_RANDOMISED_MAPPINGS_T (URL);
      create index KEY_INDEX on URL_RANDOMISED_MAPPINGS_T (TINY);

      Oracle

      create table URL_RANDOMISED_MAPPINGS_T (
      ID number(19,0) not null,
      TINY varchar2(255 CHAR) not null,
      URL varchar2(4000) not null,
      primary key (ID)
      );

      create index URL_INDEX on URL_RANDOMISED_MAPPINGS_T (URL);
      create index KEY_INDEX on URL_RANDOMISED_MAPPINGS_T (TINY);
      create sequence URL_RANDOMISED_MAPPINGS_S;

        Gliffy Diagrams

          Zeplin

            Attachments

              Issue Links

                Activity

                  People

                  Assignee:
                  steve.swinsburg Steve Swinsburg
                  Reporter:
                  steve.swinsburg Steve Swinsburg
                  Votes:
                  0 Vote for this issue
                  Watchers:
                  1 Start watching this issue

                    Dates

                    Created:
                    Updated:
                    Resolved:

                      Git Integration