I'm trying to sort out a query that identifies images that are not in the story table (structures below). Peter Brawley has kindly pointed me in the right direction with the sql structure (which does work), but I'm getting benchmarks of 5+ seconds on the test data, whereas the live site has 50x the number of rows and I'm concerned about trying to go live with it.
If anyone could suggest ways to improve the query/table structure, I'd be most grateful.
Kris
SELECT i.image_id, i.image_name FROM table_image i LEFT JOIN table_story s USING (mainpic_id) WHERE s.mainpic_id IS NULL ORDER by mi.image_name
TABLE IMAGE image_id int(11) unsigned NOT NULL auto_increment, image_name char(64) NOT NULL default 'default', PRIMARY KEY (mainpic_id), KEY mainpic_id (mainpic_id) ) TYPE=MyISAM
TABLE STORY id int(11) NOT NULL auto_increment, body text, image_id int(11) unsigned NOT NULL default '0', PRIMARY KEY (id), KEY id (id) ) TYPE=MyISAM
And the results from EXPLAIN SELECT: table = image type = ALL possible keys = key = key_len = ref = rows = 460 extra =
table = story type = ALL possible keys = key = key_len = ref = rows = 610 extra =
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
