Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187

    Unanswered: Create table question - indexes and unique

    Hi,

    I've the following create table specs. The table is created successfully. I would actually like 'title' to be TINYTEXT and have it UNIQUE. When I did that, I got an error.

    What I hope to do after the appropriate data is entered into the database is to make titles and authors searchable by keywords. Did I set up the table correctly?

    Code:
    CREATE TABLE books (
          id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(120) NOT NULL,
          author VARCHAR(120) NOT NULL,
          about TEXT NOT NULL,
          UNIQUE(title),
          INDEX(title(30)),
          INDEX(author(30)),
    ) TYPE=INNODB;
    Thanks in advance

    And btw, awesome changes to the site since I was last here. Congrats!
    Last edited by pearl2; 05-12-04 at 23:30.

  2. #2
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Could someone enlighten me?

    Thanks in advance

  3. #3
    Join Date
    Oct 2003
    Posts
    706
    Forget TINYTEXT.

    Are you really sure that Titles will be "unique?" Or a mere 30 characters long? Why limit them at all?

    For many fields such as author and title, you'll want to search for entries by "any word that appears." A full-text index, and the Text field-type, are often most suitable in these applications. (Not so much that the DBMS cares one whit one way or the other, but to support what users are likely to want to do.)
    ChimneySweep(R): fast, automatic
    table repair at a click of the
    mouse! http://www.sundialservices.com

  4. #4
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    Thanks, undialsvcs!

    So how should I change the table specs to? I'm not entirely sure how INDEX works. How about something as follows:

    Code:
    CREATE TABLE books (
          id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(120) NOT NULL,
          author VARCHAR(120) NOT NULL,
          about TEXT NOT NULL,
          UNIQUE(title),
          INDEX(title),
          INDEX(author),
    ) TYPE=INNODB;
    That is, removing the 30 character restriction on the index.

  5. #5
    Join Date
    Nov 2003
    Location
    Sinapore
    Posts
    187
    How about the following table specs?

    Code:
    CREATE TABLE books (
          id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
          title VARCHAR(120) NOT NULL,
          author VARCHAR(120) NOT NULL,
          about TEXT NOT NULL,
          UNIQUE(title),
          FULLEXT (title),
          FULLEXT (author),
    );
    It seems that words fewer that 4 characters are skipped in the searches...

    Thanks

Posting Permissions

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