two mysql queries seem to be faster than one, anyone have any idea how to combine these without the over head?

I wonder if someone can help me with this. I have 2 query versions that give me the same result. One uses 2 queries, but is faster, the other one uses only One query but is quite a bit slower. I'm just trying to find out if there's a better way of doing this in one query without slowing it down that much.

this one is sufficiently fast....

query One:
    SELECT * FROM global_rewrite_links
    => do some php stuff (i used the IN Array numbers as example values)
    Average q1 : 0.0015 sec

query two, use php arrays in ON clause:
    SELECT SQL_NO_CACHE master.prop_id,master.property_name  FROM property_main master 
    INNER JOIN prop_normalize_options_sport_leisure_health jtbl0 ON ( master.prop_id=jtbl0.prop_id AND jtbl0.item_id IN (37))  
    INNER JOIN prop_normalize_options_property_开发者_StackOverflow社区activities jtbl1 ON ( jtbl0.prop_id=jtbl1.prop_id AND jtbl1.item_id IN (17)) 
    INNER JOIN prop_normalize_options_property_suitability jtbl2 ON ( jtbl1.prop_id=jtbl2.prop_id AND jtbl2.item_id IN (15))
    INNER JOIN prop_normalize_options_property_facilities_other jtbl3 ON ( jtbl2.prop_id=jtbl3.prop_id AND jtbl3.item_id IN (57)) 
    WHERE master.active='Y' 
    GROUP BY master.prop_id 
    ORDER BY master.sortOrder 

Average q2 : ~0.06sec

Total Average: ~0.07sec

however, as the IN ('ARRAY') is dynamically generated from the other table (and i am tryying to do it all in the same query), I ended up with this, which is a lot slower:

SELECT SQL_NO_CACHE prop.prop_id,prop.property_name FROM property_main prop 
INNER JOIN prop_normalize_options_sport_leisure_health jTbl0 ON (prop.prop_id=jTbl0.prop_id AND jTbl0.item_id IN (SELECT item_id FROM global_rewrite_links WHERE link_1 IN ('pool') AND category_name_1='recreational-facilities')) 
INNER JOIN prop_normalize_options_property_activities jTbl1 ON (prop.prop_id=jTbl1.prop_id AND jTbl1.item_id IN (SELECT item_id FROM global_rewrite_links WHERE link_1 IN ('golf') AND category_name_1='activity-holidays')) 
INNER JOIN prop_normalize_options_property_suitability jTbl2 ON (prop.prop_id=jTbl2.prop_id AND jTbl2.item_id IN (SELECT item_id FROM global_rewrite_links WHERE link_1 IN ('non-smoking') AND category_name_1='accessibility')) 
INNER JOIN prop_normalize_options_property_facilities_other jTbl3 ON (prop.prop_id=jTbl3.prop_id AND jTbl3.item_id IN (SELECT item_id FROM global_rewrite_links WHERE link_1 IN ('internet') AND category_name_1='guest-facilities')) 
WHERE prop.active='Y' 
GROUP BY prop.prop_id 
ORDER BY prop.sortOrder 

Average: ~0.45sec

I also tried some CASE/HAVING clauses, but the one I came up with was seriously slow. Maybe someone has a better idea ? Any ideas would be really appreciated

Thanks

PS: create

CREATE TABLE `global_rewrite_links` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `item_id` INT(20) UNSIGNED NULL DEFAULT NULL,
    `parent_id` SMALLINT(5) UNSIGNED NULL DEFAULT NULL,
    `sort` TINYINT(3) UNSIGNED NULL DEFAULT NULL,
    `queryField` VARCHAR(200) NULL DEFAULT NULL,
    `inFilter` ENUM('Y','N') NULL DEFAULT 'N',
    `table_name` VARCHAR(200) NULL DEFAULT NULL,
    `queryType` VARCHAR(3) NOT NULL DEFAULT 'AND',
    `queryOperator` VARCHAR(2) NOT NULL DEFAULT '=',
    `category_txt_1` VARCHAR(200) NULL DEFAULT NULL,
    `category_txt_2` VARCHAR(200) NULL DEFAULT NULL,
    `category_txt_3` VARCHAR(200) NULL DEFAULT NULL,
    `category_name_1` VARCHAR(200) NULL DEFAULT NULL,
    `category_name_2` VARCHAR(200) NULL DEFAULT NULL,
    `category_name_3` VARCHAR(200) NULL DEFAULT NULL,
    `link_txt_1` VARCHAR(200) NULL DEFAULT NULL,
    `link_txt_2` VARCHAR(200) NULL DEFAULT NULL,
    `link_txt_3` VARCHAR(200) NULL DEFAULT NULL,
    `link_1` VARCHAR(200) NULL DEFAULT NULL,
    `link_2` VARCHAR(200) NULL DEFAULT NULL,
    `link_3` VARCHAR(200) NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `queryField` (`queryField`),
    INDEX `inFilter` (`inFilter`),
    INDEX `parentID` (`parent_id`),
    INDEX `link1` (`link_1`, `category_name_1`, `item_id`),
    INDEX `link2` (`link_2`, `category_name_2`, `item_id`),
    INDEX `link3` (`link_3`, `category_name_3`, `item_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=MyISAM
ROW_FORMAT=DEFAULT
AUTO_INCREMENT=285

Honestly, I think this is a non-issue. There seems to be some obsession in the industry that less queries are better than more. In the general sense this is true since 2 queries that do the same thing as 1,000,000 will be better. But in specific cases, it may or may not make sense. I'd much rather have 10 primary key lookup queries (SELECT foo FROM bar WHERE id = 1) than 1 Cartesian join. Combine queries where it makes logical sense and is efficient to do so. Otherwise don't worry about it.

In your specific case, you may be able to make a single query that's as fast if not faster than your two queries. But I have to ask, is it worth it? 85% of your execution time is in the execution of the 2nd query. So unless you make that more efficient, the best possible gain you can get is 15%. And while that's nice, it's still only 0.01 seconds. How much work are you willing to put in for it? My argument is that it doesn't matter, so don't worry about it. Instead, focus on larger gains that you can get elsewhere, or make the second query more efficient first.

At least that's my $0.02...