Does Greg Rahn's "join order benchmark" reflect real world OLAP join scenarios?
0
votes
1
answer
282
views
Greg Rahn's "join order benchmark" has dozens of inner join queries on the IMDB database.
Does this benchmark and the database itself reflect common real world OLAP join scenarios? How does a join optimizer's performance on these benchmark correlate to general join performance for the real world?
For example:
SELECT MIN(cn.name) AS movie_company,
MIN(mi_idx.info) AS rating,
MIN(t.title) AS complete_euro_dark_movie
FROM complete_cast AS cc,
comp_cast_type AS cct1,
comp_cast_type AS cct2,
company_name AS cn,
company_type AS ct,
info_type AS it1,
info_type AS it2,
keyword AS k,
kind_type AS kt,
movie_companies AS mc,
movie_info AS mi,
movie_info_idx AS mi_idx,
movie_keyword AS mk,
title AS t
WHERE cct1.kind = 'crew'
AND cct2.kind != 'complete+verified'
AND cn.country_code != '[us]'
AND it1.info = 'countries'
AND it2.info = 'rating'
AND k.keyword IN ('murder',
'murder-in-title',
'blood',
'violence')
AND kt.kind IN ('movie',
'episode')
AND mc.note NOT LIKE '%(USA)%'
AND mc.note LIKE '%(200%)%'
AND mi.info IN ('Sweden',
'Norway',
'Germany',
'Denmark',
'Swedish',
'Danish',
'Norwegian',
'German',
'USA',
'American')
AND mi_idx.info 2000
AND kt.id = t.kind_id
AND t.id = mi.movie_id
AND t.id = mk.movie_id
AND t.id = mi_idx.movie_id
AND t.id = mc.movie_id
AND t.id = cc.movie_id
AND mk.movie_id = mi.movie_id
AND mk.movie_id = mi_idx.movie_id
AND mk.movie_id = mc.movie_id
AND mk.movie_id = cc.movie_id
AND mi.movie_id = mi_idx.movie_id
AND mi.movie_id = mc.movie_id
AND mi.movie_id = cc.movie_id
AND mc.movie_id = mi_idx.movie_id
AND mc.movie_id = cc.movie_id
AND mi_idx.movie_id = cc.movie_id
AND k.id = mk.keyword_id
AND it1.id = mi.info_type_id
AND it2.id = mi_idx.info_type_id
AND ct.id = mc.company_type_id
AND cn.id = mc.company_id
AND cct1.id = cc.subject_id
AND cct2.id = cc.status_id;
Asked by larryliu
(1 rep)
Apr 3, 2022, 12:59 PM
Last activity: May 22, 2025, 01:03 PM
Last activity: May 22, 2025, 01:03 PM