Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 4

Displaying posts with tag: TPC-H (reset)

Another look at improving TPC-H-like queries - Q17
+0 Vote Up -0Vote Down

Summary: An alternate approach, offered in response to our original post, provides excellent improvements for smaller databases, but clustered indexes offer better performance as database size increases.  (This posting is by Dave.)

Jay Pipes suggested an alternate approach to improving MySQL performance of Query 17 on a TPC-H-like database.



  • Add the index (l_partkey, l_quantity) to the lineitem table.

  • Re-write the query as:
    select 
       sum(li.l_extendedprice) / 7.0 as avg_yearly 
    from lineitem li 
       inner join part p on li.l_partkey = p.p_partkey 
       inner join ( select 
                       l_partkey, 0.2 * avg(l_quantity) as quantity 
                    from lineitem 
                    group by l_partkey






  •   [Read more...]
    Improving TPC-H-like queries - Q17
    +1 Vote Up -0Vote Down

    Executive Summary: A query like TPC-H Query 17 can be sped up by large factors by using straight_joins and clustering indexes.  (This entry posted by Dave.)

    In a previous post, we wrote about queries like TPC-H query 2, and the use of straight_join to improve performance. 
    This week, we consider Query 17, described by the TPC-H documentation as

    “The Small-Quantity-Order Revenue Query considers parts of a given brand and with a given container type and determines the average lineitem quantity of such parts ordered for all orders (past and pending) in the 7-year database.  What would the average yearly gross (undiscounted) loss in revenue if orders for these parts with a quantity of this average were no longer taken?”




      [Read more...]
    MySQL and Kickfire Break Records (Again)
    +0 Vote Up -0Vote Down

    Following on from the announcement at the MySQL conference where Sun and Kickfire jointly announced data warehousing benchmark records, we have just announced new TPC-H benchmark records. Specifically, the Kickfire Database Appliance 2400 is the highest price/performance offering at 300GB, again breaking the $1 barrier for the first time coming in at 89 cents per QphH (Queries per hour on the TPC-H benchmark). The 2400 is also the highest performance (non-clustered) offering at 300GB.

    I’m not going to further dwell on the numbers in this post other than to quickly point out another aspect of this achievement that Justin noted in his blog related to the energy savings the

      [Read more...]
    Kickfire Launch
    +0 Vote Up -0Vote Down

    Today, we officially launched Kickfire. As part of our announcement we published, together with Sun Microsystems, record-breaking TPC-H benchmark numbers (http://www.mysql.com/news-and-events/press-release/release_2008_13.html" target="_blank) (data warehousing industry benchmarks) as well as a series of significant partnerships in the Open Source world.

    There has been a lot of work here over the last two years to get us to this point and I am very proud of the team for getting us to where we are today. Two years ago we just had a vision; today that vision became reality – one substantiated by independent industry benchmarks.

    For those of you unfamiliar with these benchmarks let me give you a brief overview

      [Read more...]
    Showing entries 1 to 4

    Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

    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.