Hmm, That's what I thought I was doing by putting the common fields of each article type in a single table. I do need to rethink the search functions. Was thinking of a fulltext index on title and article_content fields.
basic table is id | type | author | date | last_update | title | keywords | article_content Since most frequent request will be on the date field, that will be first, but I need to put learn more of fulltext indexes and probably index keywords, title, article content. Thanks much, olinux --- Toomas Vendelin <[EMAIL PROTECTED]> wrote: > Hello olinux, > > If I were in your shoes, I would put not only paths > to images into a separate > table, but also a field called "content" and > probably title, summary, > author and linked all this stuff together via > ID-like fields which are > primary keys in those new tables. Then articles > table can be searched > through much faster, and title, summary, author data > can be quickly retrieved > using primmary keys ONLY when this data is needed. > So you first search > a very compact table where most of the fields are > indexes or parts of indexes, and THEN, using primary > keys, retrieve big pieces of data > which you do not have to search through (and > nothing, I guess, > works faster in MySQL than retrieval of a single > record by it's primary > key). Run your queries with EXPLAIN, it will > probably explain it > less verbously than me :). > > Regards, > > Tom > > Monday, April 22, 2002, 6:29:52 AM, you wrote: > > o> Hello, > > o> I have a table with a large number of news > articles. > o> Articles of type 'feature' (identified in the > o> article_type field) will have the path to an > image > o> associated with the article stored. The question > is > o> whether to 1) add a field to the database that > most > o> records will not use OR 2) add a table to hold > image > o> path names and require a JOIN in order to > retrieve. > > o> I am thinking that the join query will be best. > Thanks > o> for any help. > > o> olinux > > o> Table setup is: > > o> CREATE TABLE articles ( > o> id int(12) NOT NULL auto_increment, > o> type varchar(15) NOT NULL default '', > o> region smallint(1) NOT NULL default '0', > o> date varchar(35) NOT NULL default '0', > o> author varchar(30) NOT NULL default '', > o> title varchar(90) NOT NULL default '', > o> summary varchar(255) NOT NULL default '', > o> content text NOT NULL, > o> keywords varchar(150) NOT NULL default '', > o> filename varchar(30) NOT NULL default '', > o> PRIMARY KEY (id,id) > o> ) TYPE=MyISAM; > __________________________________________________ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.com/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
