Re: MySQL Table Size / Techniques (example table structure etc.)

In the following, (hopefully obvious and logical) the table "master"
holds the generic album specific information like title, media type,
record label, shelf category and number, etc. The table "artist"
contains info on the artists (related to the master table by the
masterID field) and the same for the table "track" (except that it's
about the songs, not the artists.)

TABLE master (TYPE=MyISAM)
  Category char(3) NOT NULL default '',
  Number mediumint(8) unsigned NOT NULL default '0',
  Label varchar(50) default NULL,
  LabelNumber varchar(50) default NULL,
  Title varchar(100) default NULL,
  Media varchar(50) default NULL,
  TotalPlayingTime time NOT NULL default '00:00:00',
  Date date NOT NULL default '0000-00-00',
  Comments longtext,
  id int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (id),
  KEY CategoryIndex (Category),
  KEY NumberIndex (Number),
  KEY DateIndex (Date),
  FULLTEXT KEY CommentsIndex (Comments),
  FULLTEXT KEY LabelIndex (Label),
  FULLTEXT KEY LabelNumberIndex (LabelNumber)

TABLE artist (TYPE=MyISAM)
  Name varchar(50) NOT NULL default '',
  ArtistType varchar(50) default NULL,
  id int(10) unsigned NOT NULL auto_increment,
  masterID int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  FULLTEXT KEY ArtistNameIndex (Name)

TABLE track (TYPE=MyISAM)
  TrackNumber varchar(15) default NULL,
  TrackLength time NOT NULL default '00:00:00',
  Title varchar(75) NOT NULL default '',
  id int(10) unsigned NOT NULL auto_increment,
  masterID int(10) unsigned default NULL,
  PRIMARY KEY  (id),
  KEY TimeIndex (TrackLength),
  FULLTEXT KEY TitleIndex (Title)

An example of one of the original queries I *had* been sending, before
using temporary tables would be:

SELECT
master.Category,master.Number,master.title,artist.Name,track.Title
FROM master,artist,track WHERE artist.Name LIKE "%John%" AND
track.Title LIKE "%Love%" AND artist.masterID=master.id AND
track.masterID=master.id ORDER BY master.Date DESC LIMIT 0,10;

And, as I've mentioned in a few posts, I've since tried splitting it
up, to perform three queries (one for each table) and create temporary
tables, and then pull out the matching rows (by id) - which did speed
things up partially. But though I'm not sure I'm right in thinking so,
it seems to me that anything over 2 seconds starts to seem like it
might as well be half an hour. :)

A note on the indexes above... the FULLTEXT indexes were added when I
was playing with the idea of using match/against... and maybe they
should be regular indexes? I'm not sure.

I welcome any thoughts and opinions.....
                         
~ The U.P. Web Maestro (Nathan Lyle)

  E-Mail: nathan(at)upwebmaestro.com
  Online: www.upwebmaestro.com
   Phone: (906)485-4806

"There is no reason anyone would want a computer in their home." - Ken Olson, president/founder of Digital Equipment Corp., 1977.

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA