Page 1 of 1

Just wanted to bounce something off your heads here (MySQL)

Posted: Mon Nov 06, 2006 11:24 am
by BDKR
In the application that I'm building presently, each item that is stored has a description. The descriptions can be rather lengthy or verbose at times of course.

Anyway, I'm using innodb for the bulk of the tables as I want the db to maintain referrential integrity for me. Now the only problem here is that my search
algorithm is going to generate a "LIKE" type query statement based on the input it gets from the user. To that end, I'd love to create a FULLTEXT index.

Oh crap! Can't use FULLTEXT indexes on Inno tables! LOL

So here are my considerations on how to do this. Just wanted to get some feedback from others on this.

Option 1:

Code: Select all

CREATE TABLE `descriptions_1` (
  `description_id` int(11) NOT NULL auto_increment,
  `gate_id` int(11) NOT NULL,
  `manifold_id` int(11) NOT NULL,
  `turbo_id` int(11) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`description_id`),
  UNIQUE KEY `gate_id` (`gate_id`,`manifold_id`,`turbo_id`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
Above, gate_id, manifold_id, and turbo_id represent tables that would store descriptions for their rows here. The only problem here is that each row is garuanteed
to leave 2 of the 3 fields empty. However, that's still going to represent space in the db and therefore the file system. Seems a bit of a waste if you ask me. At
least the parent id's are named correctly.

Option 2:

Code: Select all

CREATE TABLE `descriptions_2` (
  `description_id` int(11) NOT NULL auto_increment,
  `parent_id` int(11) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`description_id`),
  UNIQUE KEY `parent_id` (`parent_id`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
In the above, I opted to forgo having different fields and using just a parent_id. That parent_id could be a gate_id, manifold_id, or turbo_id.

Option 3:

Code: Select all

CREATE TABLE `descriptions_3` (
  `description_id` int(11) NOT NULL auto_increment,
  `parent_type` varchar(1) NOT NULL,
  `parent_id` int(11) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY  (`description_id`),
  UNIQUE KEY `parent_id` (`parent_id`),
  KEY `parent_type` (`parent_type`),
  FULLTEXT KEY `description` (`description`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
In this one, I'm still using the parent_id idea, but I now have a parent_type which refers to one of the potential parents. At least this way, there is not waste of space in each row AND I can tell what the parent is.

Of course, to a lot of us, the answer may seem obvious, but perhaps there is something I'm not considering here right? There are some sharp chaps around here so it's good to get some feedback.

And the more I think about it, the second option above will not work. The parent_id can't be unique as it can potentially reference one of three seperate tables that each has their own ID generation via auto_increment.

Shouldn't MySQL have a global ID generation mechanism?

Anyway, I'm going with #3 for now unless someone can give me some good feedback as to why I shouldn't.

Cheers

Posted: Mon Nov 06, 2006 11:43 am
by RobertGonzalez
I haven't tried, but can you use MATCH () AGAINST() syntax (as described here) in InnoDB tables?

Posted: Mon Nov 06, 2006 11:52 am
by BDKR
Everah wrote: I haven't tried, but can you use MATCH () AGAINST() syntax (as described here) in InnoDB tables?
Thanx! :D I think I'll do some manual diving and check those out.

Posted: Mon Nov 06, 2006 1:52 pm
by feyd
If that's not possible, you could potentially separate the table into a secondary table which is not InnoDB and merely reference the InnoDB table. Annoying, yes, but a workaround.

Posted: Mon Nov 06, 2006 5:02 pm
by BDKR
feyd wrote:If that's not possible, you could potentially separate the table into a secondary table which is not InnoDB and merely reference the InnoDB table. Annoying, yes, but a workaround.
Yeah, that's esentially what I did. I took the description field out and created it's own MyISAM table. Being able to FULLTEXT index that description field should provide better performance for those occasions where the search system decides that a full text type search is most appropriate.