Create table question - indexes and unique

pearl2 · #1 (**permalink**) 05-12-04, 06:08

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!

pearl2 · #2 (**permalink**) 05-12-04, 22:29

Could someone enlighten me?

Thanks in advance

sundialsvcs · #3 (**permalink**) 05-12-04, 22:46

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.)

pearl2 · #4 (**permalink**) 05-12-04, 23:50

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.

pearl2 · #5 (**permalink**) 05-13-04, 00:48

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

Internet Services

Web Development

Digital Marketing

Consumer Tech