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.

我想知道是否有人可以帮助我。我有2个查询版本,给我相同的结果。一个使用2个查询,但速度更快,另一个只使用一个查询,但速度相当慢。我只是试图找出是否有更好的方法在一个查询中执行此操作而不会减慢它的速度。

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_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:

然而,因为IN('ARRAY')是从另一个表动态生成的(我正在尝试在同一个查询中完成所有操作),所以我最终得到了这个,这慢了很多:

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

我也尝试了一些CASE / HAVING条款,但我提出的条款非常慢。也许有人有更好的主意?任何想法都会非常感激

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

1 个解决方案

#1

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.

老实说,我认为这不是问题。业界似乎有一些困惑,认为较少的查询比更多的查询更好。在一般意义上,这是正确的,因为2个与1,000,000相同的查询会更好。但在特定情况下,它可能有意义,也可能没有意义。我宁愿有10个主键查找查询(SELECT foo FROM bar WHERE id = 1)而不是1个笛卡尔联接。将查询组合在一起,使其具有逻辑意义并且有效。否则不要担心。

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.

在您的特定情况下,您可能能够以比两个查询更快的速度进行单个查询。但我不得不问,这值得吗? 85%的执行时间是执行第二个查询。因此,除非你提高效率,否则你可以获得的最佳收益是15%。虽然这很好,但仍然只有0.01秒。你愿意投入多少工作?我的论点是没关系,所以不要担心。相反,要关注可以在其他地方获得的更大收益,或者首先使第二个查询更有效率。

At least that's my $0.02...

至少那是我0.02美元......