MySQL Forums
Forum List  »  Quality Assurance

schema question
Posted by: michael Connors
Date: May 16, 2008 10:43PM

So this question is for search query and it has 4 tables


//// toxi schema
//// the table of tags
CREATE TABLE IF NOT EXISTS `mrgfle_tags` (
`id` int(10) unsigned NOT NULL auto_increment,
`tag` varchar(30) character set latin1 NOT NULL default '',
`raw_tag` varchar(50) character set latin1 NOT NULL default '',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=99832 ;



//// the table of tagged objects
CREATE TABLE IF NOT EXISTS `mrgfle_tagged_objects` (
`tag_id` int(10) unsigned NOT NULL default '0',
`tagger_id` int(10) unsigned NOT NULL default '0',
`object_id` int(10) unsigned NOT NULL default '0',
`tagged_on` datetime NOT NULL default '0000-00-00 00:00:00',
`file_type` tinyint(1) NOT NULL default '0',
`display_type` int(3) NOT NULL,
`tag_match` smallint(1) NOT NULL,
KEY `tagger_id_index` (`tagger_id`),
KEY `object_id_index` (`object_id`),
KEY `display_type` (`display_type`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


//// the table of objects
CREATE TABLE IF NOT EXISTS `mrgfle_categories` (
`unique_id` mediumint(8) NOT NULL auto_increment,
`parent_id` mediumint(8) NOT NULL default '0',
`left_id` mediumint(8) NOT NULL,
`right_id` mediumint(8) NOT NULL,
`parent_type` smallint(2) NOT NULL default '0',
`date_added` int(11) NOT NULL default '0',
`file_id` mediumint(8) NOT NULL default '0',
`file_type` smallint(2) NOT NULL default '0',
`display_type` mediumint(2) NOT NULL default '0',
`visible` tinyint(1) NOT NULL default '1',
`title` text character set latin1 NOT NULL,
`title_wiki` varchar(255) character set latin1 NOT NULL default '',
`downloads` mediumint(8) NOT NULL default '0',
`views` mediumint(8) NOT NULL default '1',
`author` varchar(25) character set latin1 NOT NULL default '',
`author_id` mediumint(8) NOT NULL default '0',
`model_release` int(1) NOT NULL default '0',
`property_release` int(1) NOT NULL default '0',
`caption` text character set latin1 NOT NULL,
`tags` text character set latin1 NOT NULL,
`last_view` int(11) NOT NULL default '0',
`last_zip` int(11) NOT NULL default '0',
`last_view_ip` varchar(16) character set latin1 NOT NULL default '',
`last_zip_ip` varchar(16) character set latin1 NOT NULL default '',
`comments_count` mediumint(9) NOT NULL default '0',
`comments_date` int(11) NOT NULL default '0',
`comments_author` text character set latin1 NOT NULL,
`comments_author_id` mediumint(8) NOT NULL default '0',
`rating` tinyint(4) NOT NULL default '0',
`rating_enabled` tinyint(1) NOT NULL default '0',
`license` char(3) character set latin1 NOT NULL default '0',
`locked` tinyint(1) NOT NULL default '0',
`root_permissions` tinyint(1) NOT NULL default '0',
`link_randKey` mediumint(11) NOT NULL,
`link_votes` mediumint(11) NOT NULL,
`link_karma` mediumint(11) NOT NULL,
`clr_1` varchar(7) collate utf8_bin NOT NULL,
`clr_2` varchar(7) collate utf8_bin NOT NULL,
`clr_3` varchar(7) collate utf8_bin NOT NULL,
PRIMARY KEY (`unique_id`),
KEY `parent_id` (`parent_id`),
KEY `parent_type` (`parent_type`),
KEY `file_id` (`file_id`),
KEY `left_id` (`left_id`),
KEY `right_id` (`right_id`),
KEY `date_added` (`date_added`),
KEY `file_type` (`file_type`),
KEY `display_type` (`display_type`),
KEY `visible` (`visible`),
KEY `downloads` (`downloads`),
KEY `views` (`views`),
KEY `author_id` (`author_id`),
KEY `rating` (`rating`),
KEY `link_votes` (`link_votes`),
KEY `link_karma` (`link_karma`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=212864 ;




//// a table of files joined to the category
/// this is for a file in the category table to have multiple versions

CREATE TABLE IF NOT EXISTS `mrgfle_files` (
`file_id` mediumint(8) NOT NULL auto_increment,
`mrg_file_type` tinyint(2) NOT NULL,
`display_type` tinyint(2) NOT NULL default '0',
`parent_id` mediumint(8) NOT NULL default '0',
`title` varchar(125) collate utf8_bin NOT NULL,
`parent_type` smallint(2) NOT NULL default '0',
`date_added` int(11) NOT NULL default '0',
`file_name` text character set latin1 NOT NULL,
`file_path_small` varchar(150) character set latin1 NOT NULL default '',
`file_path_med` varchar(150) character set latin1 NOT NULL default '',
`file_path_large` varchar(150) character set latin1 NOT NULL default '',
`file_path_high` varchar(150) character set latin1 NOT NULL default '',
`file_ord` smallint(3) NOT NULL default '0',
`camera_id` mediumint(5) NOT NULL default '0',
`exif_camera` varchar(100) collate utf8_bin NOT NULL,
`exif_date` int(11) NOT NULL,
`exif_aperture_stop` varchar(5) character set latin1 NOT NULL default '',
`exif_focal_length` varchar(5) character set latin1 NOT NULL default '',
`exif_shutter_speed` varchar(5) character set latin1 NOT NULL default '',
`exif_ISO` varchar(25) character set latin1 NOT NULL default '',
`exif_flash` varchar(5) character set latin1 NOT NULL default '',
`author_id` mediumint(8) NOT NULL default '0',
`file_size` mediumint(9) NOT NULL default '0',
`file_width` mediumint(9) NOT NULL default '0',
`file_height` mediumint(9) NOT NULL default '0',
PRIMARY KEY (`file_id`),
KEY `mrg_file_type` (`mrg_file_type`),
KEY `display_type` (`display_type`),
KEY `parent_id` (`parent_id`),
KEY `date_added` (`date_added`),
KEY `camera_id` (`camera_id`),
KEY `file_ord` (`file_ord`),
KEY `author_id` (`author_id`),
KEY `file_size` (`file_size`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=181447 ;




so thats the setup and I made this insane join join IN join query that looks like this...

//// search for fruit OR vegetables EXCLUDE apples


SELECT
mrgfle_categories.unique_id,
mrgfle_categories.title,
mrgfle_files.file_path_small

FROM mrgfle_categories

JOIN mrgfle_files

ON mrgfle_categories.file_id = mrgfle_files.file_id

WHERE mrgfle_categories.unique_id IN(

SELECT i2t0.object_id
FROM mrgfle_tags t0
CROSS JOIN mrgfle_tags t1

INNER JOIN mrgfle_tagged_objects i2t0
ON t0.id = i2t0.tag_id
LEFT JOIN mrgfle_tagged_objects i2t1
ON i2t0.object_id = i2t1.object_id
AND i2t1.tag_id= t1.id

WHERE t0.tag IN ('fruit','vegetable')
AND t1.tag = 'apples'
AND i2t1.object_id IS NULL

)

AND mrgfle_categories.display_type='1'
AND mrgfle_categories.visible=1
ORDER BY mrgfle_categories.title ASC
LIMIT 0,24



it definitely chokes the tiny machine I am building this on, is this terribly inneficient? Should I put the search results into an array and use the IDs in the IN () or should I build a join for the 4 tables at once or is there a much better way to do this?



Edited 1 time(s). Last edit at 05/16/2008 10:44PM by michael Connors.

Options: ReplyQuote


Subject
Views
Written By
Posted
schema question
3760
May 16, 2008 10:43PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.