NoSE Schema Recommendation

Model

# frozen_string_literal: true
# rubocop:disable all

NoSE::Model.new do
  # Define entities along with the size and cardinality of their fields
  # as well as an estimated number of each entity

  (Entity 'categories' do
    ID     'id'
    String 'name', 20
    Integer 'dummy', count: 1
  end) * 50

  (Entity 'regions' do
    ID      'id'
    String  'name', 25
    Integer 'dummy', count: 1
  end) * 5

  (Entity 'users' do
    ID         'id'
    String     'firstname', 6
    String     'lastname', 7
    String     'nickname', 12
    String     'password', 15
    String     'email', 23
    Integer    'rating', count: 50
    Float      'balance', count: 10_000
    Date       'creation_date'
  end) * 2_000

  (Entity 'items' do
    ID         'id'
    String     'name', 19
    String     'description', 197
    Float      'initial_price'
    Integer    'quantity', count: 100
    Float      'reserve_price'
    Float      'buy_now'
    Integer    'nb_of_bids', count: 100
    Float      'max_bid'
    Date       'start_date'
    Date       'end_date'
  end) * 20_000

  (Entity 'bids' do
    ID         'id'
    Integer    'qty', count: 5
    Float      'bid'
    Date       'date'
  end) * 200_000

  (Entity 'comments' do
    ID         'id'
    Integer    'rating', count: 10
    Date       'date'
    String     'comment', 130
  end) * 100_000

  (Entity 'buynow' do
    ID         'id'
    Integer    'qty', count: 4
    Date       'date'
  end) * 40_000

  HasOne 'region',       'users',
         'users'      => 'regions'

  HasOne 'seller',       'items_sold',
         'items'      => 'users'

  HasOne 'category',     'items',
         'items'      => 'categories'

  HasOne 'user',         'bids',
         'bids'       => 'users'

  HasOne 'item',         'bids',
         'bids'       => 'items'

  HasOne 'from_user',    'comments_sent',
         'comments'   => 'users'

  HasOne 'to_user',      'comments_received',
         'comments'   => 'users'

  HasOne 'item',         'comments',
         'comments'   => 'items'

  HasOne 'buyer',        'bought_now',
         'buynow'     => 'users'

  HasOne 'item',         'bought_now',
         'buynow'     => 'items'
end

# rubocop:enable all

Queries

SELECT users.nickname, users.password FROM users WHERE users.id = ? -- 1

SELECT categories.id, categories.name FROM categories WHERE categories.dummy = 1 -- 2

SELECT items.name FROM items WHERE items.id = ? -- 3

SELECT users.id, users.nickname, bids.id, item.id, bids.qty, bids.bid, bids.date FROM users.bids.item WHERE item.id = ? ORDER BY bids.date -- 4

SELECT items.* FROM items WHERE items.id = ? -- 5

SELECT bids.* FROM items.bids WHERE items.id = ? -- 6

SELECT items.id, items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date FROM items.category WHERE category.id = ? AND items.end_date >= ? LIMIT 25 -- 7

SELECT users.* FROM users WHERE users.id = ? -- 8

SELECT comments.id, comments.rating, comments.date, comments.comment FROM comments.to_user WHERE to_user.id = ? -- 9

SELECT users.nickname FROM users WHERE users.id=? -- 12

SELECT items.* FROM items WHERE items.id=? -- 13

SELECT items.quantity, items.nb_of_bids, items.end_date FROM items WHERE items.id=? -- 14

SELECT users.nickname, users.password FROM users WHERE users.id=? -- 17

SELECT items.* FROM items WHERE items.id=? -- 18

SELECT bids.qty, bids.date FROM bids.item WHERE item.id=? ORDER BY bids.bid LIMIT 2 -- 19

SELECT items.nb_of_bids, items.max_bid FROM items WHERE items.id=? -- 21

SELECT users.nickname, users.password FROM users WHERE users.id=? -- 23

SELECT items.* FROM items WHERE items.id=? -- 24

SELECT users.* FROM users WHERE users.id=? -- 25

SELECT users.rating FROM users WHERE users.id=? -- 26

SELECT users.* FROM users WHERE users.id=? -- 29

SELECT comments_received.* FROM users.comments_received WHERE users.id = ? -- 30

SELECT from_user.nickname FROM comments.from_user WHERE comments.id = ? -- 31

SELECT bought_now.*, items.* FROM items.bought_now.buyer WHERE buyer.id = ? AND bought_now.date>=? -- 32

SELECT items.* FROM items.seller WHERE seller.id=? AND items.end_date >=? -- 33

SELECT items.* FROM items.bids.user WHERE user.id=? AND items.end_date>=? -- 34

SELECT items.id, items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date FROM items.seller WHERE seller.region.id = ? AND items.category.id = ? AND items.end_date >= ? LIMIT 25 -- 35

SELECT regions.id, regions.name FROM regions WHERE regions.dummy = 1 -- 36

Updates

INSERT INTO items SET id=?, name=?, description=?, initial_price=?, quantity=?, reserve_price=?, buy_now=?, nb_of_bids=0, max_bid=0, start_date=?, end_date=? AND CONNECT TO category(?), seller(?) -- 10

INSERT INTO users SET id=?, firstname=?, lastname=?, nickname=?, password=?, email=?, rating=0, balance=0, creation_date=? AND CONNECT TO region(?) -- 11

UPDATE items SET quantity=?, nb_of_bids=?, end_date=? WHERE items.id=? -- 15

INSERT INTO buynow SET id=?, qty=?, date=? AND CONNECT TO item(?), buyer(?) -- 16

INSERT INTO bids SET id=?, qty=?, bid=?, date=? AND CONNECT TO item(?), user(?) -- 20

UPDATE items SET nb_of_bids=?, max_bid=? WHERE items.id=? -- 22

UPDATE users SET rating=? WHERE users.id=? -- 27

INSERT INTO comments SET id=?, rating=?, date=?, comment=? AND CONNECT TO to_user(?), from_user(?), item(?) -- 28

Workload code

# frozen_string_literal: true

NoSE::Workload.new do
  Model 'rubis'

  # Define queries and their relative weights, weights taken from below
  # http://rubis.ow2.org/results/SB-BMP/Bidding/JBoss-SB-BMP-Bi-1500/perf.html#run_stat
  # http://rubis.ow2.org/results/SB-BMP/Browsing/JBoss-SB-BMP-Br-1500/perf.html#run_stat
  DefaultMix :browsing

  Group 'BrowseCategories', browsing: 4.44,
                            bidding: 7.65,
                            write_medium: 7.65,
                            write_heavy: 7.65 do
    Q 'SELECT users.nickname, users.password FROM users WHERE users.id = ? -- 1'
    # XXX Must have at least one equality predicate
    Q 'SELECT categories.id, categories.name FROM categories WHERE ' \
      'categories.dummy = 1 -- 2'
  end

  Group 'ViewBidHistory', browsing: 2.38,
                          bidding: 1.54,
                          write_medium: 1.54,
                          write_heavy: 1.54 do
    Q 'SELECT items.name FROM items WHERE items.id = ? -- 3'
    Q 'SELECT users.id, users.nickname, bids.id, item.id, bids.qty, ' \
      'bids.bid, bids.date FROM users.bids.item WHERE item.id = ? ' \
      'ORDER BY bids.date -- 4'
  end

  Group 'ViewItem', browsing: 22.95,
                    bidding: 14.17,
                    write_medium: 14.17,
                    write_heavy: 14.17 do
    Q 'SELECT items.* FROM items WHERE items.id = ? -- 5'
    Q 'SELECT bids.* FROM items.bids WHERE items.id = ? -- 6'
  end

  Group 'SearchItemsByCategory', browsing: 27.77,
                                 bidding: 15.94,
                                 write_medium: 15.94,
                                 write_heavy: 15.94 do
    Q 'SELECT items.id, items.name, items.initial_price, items.max_bid, ' \
      'items.nb_of_bids, items.end_date FROM items.category WHERE ' \
      'category.id = ? AND items.end_date >= ? LIMIT 25 -- 7'
  end

  Group 'ViewUserInfo', browsing: 4.41,
                        bidding: 2.48,
                        write_medium: 2.48,
                        write_heavy: 2.48 do
    # XXX Not including region name below
    Q 'SELECT users.* FROM users WHERE users.id = ? -- 8'
    Q 'SELECT comments.id, comments.rating, comments.date, comments.comment ' \
      'FROM comments.to_user WHERE to_user.id = ? -- 9'
  end

  Group 'RegisterItem', bidding: 0.53,
                        write_medium: 0.53 * 10,
                        write_heavy: 0.53 * 100 do
    Q 'INSERT INTO items SET id=?, name=?, description=?, initial_price=?, ' \
      'quantity=?, reserve_price=?, buy_now=?, nb_of_bids=0, max_bid=0, ' \
      'start_date=?, end_date=? AND CONNECT TO category(?), seller(?) -- 10'
  end

  Group 'RegisterUser', bidding: 1.07,
                        write_medium: 1.07 * 10,
                        write_heavy: 1.07 * 100 do
    Q 'INSERT INTO users SET id=?, firstname=?, lastname=?, nickname=?, ' \
      'password=?, email=?, rating=0, balance=0, creation_date=? ' \
      'AND CONNECT TO region(?) -- 11'
  end

  Group 'BuyNow', bidding: 1.16,
                  write_medium: 1.16,
                  write_heavy: 1.16 do
    Q 'SELECT users.nickname FROM users WHERE users.id=? -- 12'
    Q 'SELECT items.* FROM items WHERE items.id=? -- 13'
  end

  Group 'StoreBuyNow', bidding: 1.10,
                       write_medium: 1.10 * 10,
                       write_heavy: 1.10 * 100 do
    Q 'SELECT items.quantity, items.nb_of_bids, items.end_date FROM items ' \
      'WHERE items.id=? -- 14'
    Q 'UPDATE items SET quantity=?, nb_of_bids=?, end_date=? WHERE items.id=? -- 15'
    Q 'INSERT INTO buynow SET id=?, qty=?, date=? ' \
      'AND CONNECT TO item(?), buyer(?) -- 16'
  end

  Group 'PutBid', bidding: 5.40,
                  write_medium: 5.40,
                  write_heavy: 5.40 do
    Q 'SELECT users.nickname, users.password FROM users WHERE users.id=? -- 17'
    Q 'SELECT items.* FROM items WHERE items.id=? -- 18'
    Q 'SELECT bids.qty, bids.date FROM bids.item WHERE item.id=? ' \
      'ORDER BY bids.bid LIMIT 2 -- 19'
  end

  Group 'StoreBid', bidding: 3.74,
                    write_medium: 3.74 * 10,
                    write_heavy: 3.74 * 100 do
    Q 'INSERT INTO bids SET id=?, qty=?, bid=?, date=? ' \
      'AND CONNECT TO item(?), user(?) -- 20'
    Q 'SELECT items.nb_of_bids, items.max_bid FROM items WHERE items.id=? -- 21'
    Q 'UPDATE items SET nb_of_bids=?, max_bid=? WHERE items.id=? -- 22'
  end

  Group 'PutComment', bidding: 0.46,
                      write_medium: 0.46,
                      write_heavy: 0.46 do
    Q 'SELECT users.nickname, users.password FROM users WHERE users.id=? -- 23'
    Q 'SELECT items.* FROM items WHERE items.id=? -- 24'
    Q 'SELECT users.* FROM users WHERE users.id=? -- 25'
  end

  Group 'StoreComment', bidding: 0.45,
                        write_medium: 0.45 * 10,
                        write_heavy: 0.45 * 100 do
    Q 'SELECT users.rating FROM users WHERE users.id=? -- 26'
    Q 'UPDATE users SET rating=? WHERE users.id=? -- 27'
    Q 'INSERT INTO comments SET id=?, rating=?, date=?, comment=? ' \
      'AND CONNECT TO to_user(?), from_user(?), item(?) -- 28'
  end

  Group 'AboutMe', bidding: 1.71,
                   write_medium: 1.71,
                   write_heavy: 1.71 do
    Q 'SELECT users.* FROM users WHERE users.id=? -- 29'
    Q 'SELECT comments_received.* FROM users.comments_received ' \
      'WHERE users.id = ? -- 30'
    Q 'SELECT from_user.nickname FROM comments.from_user WHERE comments.id = ? -- 31'
    Q 'SELECT bought_now.*, items.* FROM items.bought_now.buyer ' \
      'WHERE buyer.id = ? AND bought_now.date>=? -- 32'
    Q 'SELECT items.* FROM items.seller WHERE seller.id=? AND ' \
      'items.end_date >=? -- 33'
    Q 'SELECT items.* FROM items.bids.user WHERE user.id=? AND ' \
      'items.end_date>=? -- 34'
  end

  Group 'SearchItemsByRegion', browsing: 8.26,
                               bidding: 6.34,
                               write_medium: 6.34,
                               write_heavy: 6.34 do
    Q 'SELECT items.id, items.name, items.initial_price, items.max_bid, ' \
      'items.nb_of_bids, items.end_date FROM ' \
      'items.seller WHERE seller.region.id = ? AND items.category.id = ? ' \
      'AND items.end_date >= ? LIMIT 25 -- 35'
  end

  Group 'BrowseRegions', browsing: 3.21,
                         bidding: 5.39,
                         write_medium: 5.39,
                         write_heavy: 5.39 do
    # XXX Must have at least one equality predicate
    Q 'SELECT regions.id, regions.name FROM regions ' \
      'WHERE regions.dummy = 1 -- 36'
  end
end

Indexes

i1683742356

CREATE COLUMNFAMILY "i1683742356" ("users_id" uuid, "regions_id" uuid, PRIMARY KEY(("users_id"), "regions_id"));

i1683742356 [users.id] [regions.id] → [] $64000 Graph(nodes: users, regions, edges: {users=>#users.region}>, regions=>#regions.users}>})

i3264766123

CREATE COLUMNFAMILY "i3264766123" ("items_id" uuid, "categories_id" uuid, PRIMARY KEY(("items_id"), "categories_id"));

i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>})

i1888493477

CREATE COLUMNFAMILY "i1888493477" ("items_id" uuid, "items_name" text, "items_description" text, "items_initial_price" float, "items_quantity" int, "items_reserve_price" float, "items_buy_now" float, "items_nb_of_bids" int, "items_max_bid" float, "items_start_date" timestamp, "items_end_date" timestamp, PRIMARY KEY(("items_id")));

i1888493477 [items.id] [] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $5920000 Graph(nodes: items, edges: {})

i2792222986

CREATE COLUMNFAMILY "i2792222986" ("items_id" uuid, "buynow_date" timestamp, "buynow_id" uuid, "users_id" uuid, PRIMARY KEY(("items_id"), "buynow_date", "buynow_id", "users_id"));

i2792222986 [items.id] [buynow.date, buynow.id, users.id] → [] $2240000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>})

i578710568

CREATE COLUMNFAMILY "i578710568" ("items_id" uuid, "users_id" uuid, PRIMARY KEY(("items_id"), "users_id"));

i578710568 [items.id] [users.id] → [] $640000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>})

i3563903410

CREATE COLUMNFAMILY "i3563903410" ("items_id" uuid, "bids_bid" float, "bids_id" uuid, "bids_qty" int, "bids_date" timestamp, PRIMARY KEY(("items_id"), "bids_bid", "bids_id"));

i3563903410 [items.id] [bids.bid, bids.id] → [bids.qty, bids.date] $11200000 Graph(nodes: bids, items, edges: {bids=>#bids.item}>, items=>#items.bids}>})

i193173044

CREATE COLUMNFAMILY "i193173044" ("items_id" uuid, "bids_date" timestamp, "bids_id" uuid, "users_id" uuid, "users_nickname" text, "bids_qty" int, "bids_bid" float, PRIMARY KEY(("items_id"), "bids_date", "bids_id", "users_id"));

i193173044 [items.id] [bids.date, bids.id, users.id] → [users.nickname, bids.qty, bids.bid] $16800000 Graph(nodes: users, bids, items, edges: {users=>#users.bids}>, bids=>#bids.user, bids.item}>, items=>#items.bids}>})

i101364659

CREATE COLUMNFAMILY "i101364659" ("items_id" uuid, "regions_id" uuid, "users_id" uuid, PRIMARY KEY(("items_id"), "regions_id", "users_id"));

i101364659 [items.id] [regions.id, users.id] → [] $960000 Graph(nodes: items, users, regions, edges: {items=>#items.seller}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>})

i2906147889

CREATE COLUMNFAMILY "i2906147889" ("users_id" uuid, "users_firstname" text, "users_lastname" text, "users_nickname" text, "users_password" text, "users_email" text, "users_rating" int, "users_balance" float, "users_creation_date" timestamp, PRIMARY KEY(("users_id")));

i2906147889 [users.id] [] → [users.firstname, users.lastname, users.nickname, users.password, users.email, users.rating, users.balance, users.creation_date] $206000 Graph(nodes: users, edges: {})

i3722443462

CREATE COLUMNFAMILY "i3722443462" ("categories_dummy" int, "categories_id" uuid, "categories_name" text, PRIMARY KEY(("categories_dummy"), "categories_id"));

i3722443462 [categories.dummy] [categories.id] → [categories.name] $2200 Graph(nodes: categories, edges: {})

i3392968797

CREATE COLUMNFAMILY "i3392968797" ("categories_id" uuid, "items_end_date" timestamp, "items_id" uuid, "items_name" text, "items_initial_price" float, "items_max_bid" float, "items_nb_of_bids" int, PRIMARY KEY(("categories_id"), "items_end_date", "items_id"));

i3392968797 [categories.id] [items.end_date, items.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids] $1660000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>})

i3157175159

CREATE COLUMNFAMILY "i3157175159" ("users_id" uuid, "comments_id" uuid, "comments_rating" int, "comments_date" timestamp, "comments_comment" text, PRIMARY KEY(("users_id"), "comments_id"));

i3157175159 [users.id] [comments.id] → [comments.rating, comments.date, comments.comment] $17800000 Graph(nodes: comments, users, edges: {comments=>#comments.to_user}>, users=>#users.comments_received}>})

i915430138

CREATE COLUMNFAMILY "i915430138" ("comments_id" uuid, "users_id" uuid, "users_nickname" text, PRIMARY KEY(("comments_id"), "users_id"));

i915430138 [comments.id] [users.id] → [users.nickname] $4400000 Graph(nodes: comments, users, edges: {comments=>#comments.from_user}>, users=>#users.comments_sent}>})

i2653317939

CREATE COLUMNFAMILY "i2653317939" ("users_id" uuid, "buynow_date" timestamp, "buynow_id" uuid, "items_id" uuid, "buynow_qty" int, "items_name" text, "items_description" text, "items_initial_price" float, "items_quantity" int, "items_reserve_price" float, "items_buy_now" float, "items_nb_of_bids" int, "items_max_bid" float, "items_start_date" timestamp, "items_end_date" timestamp, PRIMARY KEY(("users_id"), "buynow_date", "buynow_id", "items_id"));

i2653317939 [users.id] [buynow.date, buynow.id, items.id] → [buynow.qty, items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $13760000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>})

i2337785568

CREATE COLUMNFAMILY "i2337785568" ("users_id" uuid, "items_id" uuid, "items_name" text, "items_description" text, "items_initial_price" float, "items_quantity" int, "items_reserve_price" float, "items_buy_now" float, "items_nb_of_bids" int, "items_max_bid" float, "items_start_date" timestamp, "items_end_date" timestamp, PRIMARY KEY(("users_id"), "items_id"));

i2337785568 [users.id] [items.id] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $6240000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>})

i3553045793

CREATE COLUMNFAMILY "i3553045793" ("users_id" uuid, "items_end_date" timestamp, "bids_id" uuid, "items_id" uuid, PRIMARY KEY(("users_id"), "items_end_date", "bids_id", "items_id"));

i3553045793 [users.id] [items.end_date, bids.id, items.id] → [] $11200000 Graph(nodes: items, bids, users, edges: {items=>#items.bids}>, bids=>#bids.item, bids.user}>, users=>#users.bids}>})

i1979490368

CREATE COLUMNFAMILY "i1979490368" ("categories_id" uuid, "regions_id" uuid, "items_id" uuid, "users_id" uuid, "items_name" text, "items_initial_price" float, "items_max_bid" float, "items_nb_of_bids" int, "items_end_date" timestamp, PRIMARY KEY(("categories_id"), "regions_id", "items_id", "users_id"));

i1979490368 [categories.id] [regions.id, items.id, users.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date] $2300000 Graph(nodes: items, users, regions, categories, edges: {items=>#items.seller, items.category}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>, categories=>#categories.items}>})

i590232953

CREATE COLUMNFAMILY "i590232953" ("regions_dummy" int, "regions_id" uuid, "regions_name" text, PRIMARY KEY(("regions_dummy"), "regions_id"));

i590232953 [regions.dummy] [regions.id] → [regions.name] $245 Graph(nodes: regions, edges: {})

Query plans

SELECT users.nickname, users.password FROM users WHERE users.id = ? -- 1 users

Cost: 0.009220552463838648

Index lookup i2906147889 [users.id] [] → [users.firstname, users.lastname, users.nickname, users.password, users.email, users.rating, users.balance, users.creation_date] $206000 Graph(nodes: users, edges: {}) * 1.0/1

SELECT categories.id, categories.name FROM categories WHERE categories.dummy = 1 -- 2 categories

Cost: 0.009267389919193247

Index lookup i3722443462 [categories.dummy] [categories.id] → [categories.name] $2200 Graph(nodes: categories, edges: {}) * 5.0/1

SELECT items.name FROM items WHERE items.id = ? -- 3 items

Cost: 0.009220552463838648

Index lookup i1888493477 [items.id] [] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $5920000 Graph(nodes: items, edges: {}) * 1.0/1

SELECT users.id, users.nickname, bids.id, item.id, bids.qty, bids.bid, bids.date FROM users.bids.item WHERE item.id = ? ORDER BY bids.date -- 4 users, bids, items

Cost: 0.009325936738386496

Index lookup i193173044 [items.id] [bids.date, bids.id, users.id] → [users.nickname, bids.qty, bids.bid] $16800000 Graph(nodes: users, bids, items, edges: {users=>#users.bids}>, bids=>#bids.user, bids.item}>, items=>#items.bids}>}) * 10.0/1

SELECT items.* FROM items WHERE items.id = ? -- 5 items

Cost: 0.009220552463838648

SELECT bids.* FROM items.bids WHERE items.id = ? -- 6 items, bids

Cost: 0.009325936738386496

Index lookup i3563903410 [items.id] [bids.bid, bids.id] → [bids.qty, bids.date] $11200000 Graph(nodes: bids, items, edges: {bids=>#bids.item}>, items=>#items.bids}>}) * 10.0/1

SELECT items.id, items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date FROM items.category WHERE category.id = ? AND items.end_date >= ? LIMIT 25 -- 7 items, categories

Cost: 0.009501577195966239

Index lookup i3392968797 [categories.id] [items.end_date, items.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids] $1660000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 25/1

SELECT users.* FROM users WHERE users.id = ? -- 8 users

Cost: 0.009220552463838648

SELECT comments.id, comments.rating, comments.date, comments.comment FROM comments.to_user WHERE to_user.id = ? -- 9 comments, users

Cost: 0.00979431129193248

Index lookup i3157175159 [users.id] [comments.id] → [comments.rating, comments.date, comments.comment] $17800000 Graph(nodes: comments, users, edges: {comments=>#comments.to_user}>, users=>#users.comments_received}>}) * 50.0/1

SELECT users.nickname FROM users WHERE users.id=? -- 12 users

Cost: 0.009220552463838648

SELECT items.* FROM items WHERE items.id=? -- 13 items

Cost: 0.009220552463838648

SELECT items.quantity, items.nb_of_bids, items.end_date FROM items WHERE items.id=? -- 14 items

Cost: 0.009220552463838648

SELECT users.nickname, users.password FROM users WHERE users.id=? -- 17 users

Cost: 0.009220552463838648

SELECT items.* FROM items WHERE items.id=? -- 18 items

Cost: 0.009220552463838648

SELECT bids.qty, bids.date FROM bids.item WHERE item.id=? ORDER BY bids.bid LIMIT 2 -- 19 bids, items

Cost: 0.009232261827677299

Index lookup i3563903410 [items.id] [bids.bid, bids.id] → [bids.qty, bids.date] $11200000 Graph(nodes: bids, items, edges: {bids=>#bids.item}>, items=>#items.bids}>}) * 2/1

SELECT items.nb_of_bids, items.max_bid FROM items WHERE items.id=? -- 21 items

Cost: 0.009220552463838648

SELECT users.nickname, users.password FROM users WHERE users.id=? -- 23 users

Cost: 0.009220552463838648

SELECT items.* FROM items WHERE items.id=? -- 24 items

Cost: 0.009220552463838648

SELECT users.* FROM users WHERE users.id=? -- 25 users

Cost: 0.009220552463838648

SELECT users.rating FROM users WHERE users.id=? -- 26 users

Cost: 0.009220552463838648

SELECT users.* FROM users WHERE users.id=? -- 29 users

Cost: 0.009220552463838648

SELECT comments_received.* FROM users.comments_received WHERE users.id = ? -- 30 users, comments

Cost: 0.00979431129193248

SELECT from_user.nickname FROM comments.from_user WHERE comments.id = ? -- 31 comments, users

Cost: 0.009220552463838648

Index lookup i915430138 [comments.id] [users.id] → [users.nickname] $4400000 Graph(nodes: comments, users, edges: {comments=>#comments.from_user}>, users=>#users.comments_sent}>}) * 1.0/1

SELECT bought_now.*, items.* FROM items.bought_now.buyer WHERE buyer.id = ? AND bought_now.date>=? -- 32 items, buynow, users

Cost: 0.009232261827677299

Index lookup i2653317939 [users.id] [buynow.date, buynow.id, items.id] → [buynow.qty, items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $13760000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>}) * 2.0/1

SELECT items.* FROM items.seller WHERE seller.id=? AND items.end_date >=? -- 33 items, users

Cost: 0.009325936738386496

Index lookup i2337785568 [users.id] [items.id] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $6240000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>}) * 10.0/1
Filter [] items.end_date 10.0 -> 1.0

SELECT items.* FROM items.bids.user WHERE user.id=? AND items.end_date>=? -- 34 items, bids, users

Cost: 0.03097538087677299

Index lookup i3553045793 [users.id] [items.end_date, bids.id, items.id] → [] $11200000 Graph(nodes: items, bids, users, edges: {items=>#items.bids}>, bids=>#bids.item, bids.user}>, users=>#users.bids}>}) * 10.0/1
Index lookup i1888493477 [items.id] [] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $5920000 Graph(nodes: items, edges: {}) * 10.0/10.0

SELECT items.id, items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date FROM items.seller WHERE seller.region.id = ? AND items.category.id = ? AND items.end_date >= ? LIMIT 25 -- 35 items, users

Cost: 0.010145592207091967

Index lookup i1979490368 [categories.id] [regions.id, items.id, users.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date] $2300000 Graph(nodes: items, users, regions, categories, edges: {items=>#items.seller, items.category}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>, categories=>#categories.items}>}) * 80.0/1
Filter [] items.end_date 80.0 -> 8.0

SELECT regions.id, regions.name FROM regions WHERE regions.dummy = 1 -- 36 regions

Cost: 0.009214697781919324

Index lookup i590232953 [regions.dummy] [regions.id] → [regions.name] $245 Graph(nodes: regions, edges: {}) * 0.5/1

Update plans

INSERT INTO items SET id=?, name=?, description=?, initial_price=?, quantity=?, reserve_price=?, buy_now=?, nb_of_bids=0, max_bid=0, start_date=?, end_date=? AND CONNECT TO category(?), seller(?) -- 10

Total cost: 0.1117448609276773

i1888493477

Cost: 0.013329108

Updates

Insert i1888493477 [items.id] [] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $5920000 Graph(nodes: items, edges: {}) * 1

i3264766123

Cost: 0.013329108

Updates

Insert i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1

i3392968797

Cost: 0.013329108

Updates

Insert i3392968797 [categories.id] [items.end_date, items.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids] $1660000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1

i578710568

Cost: 0.013329108

Updates

Insert i578710568 [items.id] [users.id] → [] $640000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>}) * 1

i2337785568

Cost: 0.013329108

Updates

Insert i2337785568 [users.id] [items.id] → [items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $6240000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>}) * 1

i1979490368

Cost: 0.022549660463838647

Support queries

SELECT region.id FROM users.region WHERE users.id = ? -- 1073525086588519474

Index lookup i1683742356 [users.id] [regions.id] → [] $64000 Graph(nodes: users, regions, edges: {users=>#users.region}>, regions=>#regions.users}>}) * 1.0/1

Updates

Insert i1979490368 [categories.id] [regions.id, items.id, users.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids, items.end_date] $2300000 Graph(nodes: items, users, regions, categories, edges: {items=>#items.seller, items.category}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>, categories=>#categories.items}>}) * 1.0

i101364659

Cost: 0.022549660463838647

Support queries

SELECT region.id FROM users.region WHERE users.id = ? -- 1073525085192738753

Index lookup i1683742356 [users.id] [regions.id] → [] $64000 Graph(nodes: users, regions, edges: {users=>#users.region}>, regions=>#regions.users}>}) * 1.0/1

Updates

Insert i101364659 [items.id] [regions.id, users.id] → [] $960000 Graph(nodes: items, users, regions, edges: {items=>#items.seller}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>}) * 1.0

INSERT INTO users SET id=?, firstname=?, lastname=?, nickname=?, password=?, email=?, rating=0, balance=0, creation_date=? AND CONNECT TO region(?) -- 11

Total cost: 0.026658216

i2906147889

Cost: 0.013329108

Updates

Insert i2906147889 [users.id] [] → [users.firstname, users.lastname, users.nickname, users.password, users.email, users.rating, users.balance, users.creation_date] $206000 Graph(nodes: users, edges: {}) * 1

i1683742356

Cost: 0.013329108

Updates

Insert i1683742356 [users.id] [regions.id] → [] $64000 Graph(nodes: users, regions, edges: {users=>#users.region}>, regions=>#regions.users}>}) * 1

UPDATE items SET quantity=?, nb_of_bids=?, end_date=? WHERE items.id=? -- 15

Total cost: 0.29254338218525705

i1888493477

Cost: 0.013329108

Updates

i3392968797

Cost: 0.033099003227677296

Support queries

SELECT items.name, items.initial_price, items.max_bid FROM items WHERE items.id = ? -- 3092586359840598398

SELECT category.id FROM items.category WHERE items.id = ? -- 3092586359840598398

Index lookup i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0/1

Updates

Delete i3392968797 [categories.id] [items.end_date, items.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids] $1660000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0
Insert i3392968797 [categories.id] [items.end_date, items.id] → [items.name, items.initial_price, items.max_bid, items.nb_of_bids] $1660000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0

i2653317939

Cost: 0.0358904778276773

Support queries

SELECT buyer.id, bought_now.date, bought_now.id FROM items.bought_now.buyer WHERE items.id = ? -- 3092586360580512272

Index lookup i2792222986 [items.id] [buynow.date, buynow.id, users.id] → [] $2240000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>}) * 2.0/1

Updates

Insert i2653317939 [users.id] [buynow.date, buynow.id, items.id] → [buynow.qty, items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date] $13760000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>}) * 2.0

i2337785568

Cost: 0.022549660463838647

Support queries

SELECT seller.id FROM items.seller WHERE items.id = ? -- 3092586360904562627

Index lookup i578710568 [items.id] [users.id] → [] $640000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>}) * 1.0/1

Updates

i3553045793

Cost: 0.1559049197383865

Support queries

SELECT user.id, bids.id FROM items.bids.user WHERE items.id = ? -- 3092586359689305090

Updates

Delete i3553045793 [users.id] [items.end_date, bids.id, items.id] → [] $11200000 Graph(nodes: items, bids, users, edges: {items=>#items.bids}>, bids=>#bids.item, bids.user}>, users=>#users.bids}>}) * 10.0
Insert i3553045793 [users.id] [items.end_date, bids.id, items.id] → [] $11200000 Graph(nodes: items, bids, users, edges: {items=>#items.bids}>, bids=>#bids.item, bids.user}>, users=>#users.bids}>}) * 10.0

i1979490368

Cost: 0.031770212927677295

Support queries

SELECT region.id, seller.id FROM items.seller.region WHERE items.id = ? -- 3092586356900385123

Index lookup i101364659 [items.id] [regions.id, users.id] → [] $960000 Graph(nodes: items, users, regions, edges: {items=>#items.seller}>, users=>#users.items_sold, users.region}>, regions=>#regions.users}>}) * 1.0/1

SELECT category.id FROM items.category WHERE items.id = ? -- 3092586356900385123

Index lookup i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0/1

Updates

INSERT INTO buynow SET id=?, qty=?, date=? AND CONNECT TO item(?), buyer(?) -- 16

Total cost: 0.035878768463838646

i2792222986

Cost: 0.013329108

Updates

Insert i2792222986 [items.id] [buynow.date, buynow.id, users.id] → [] $2240000 Graph(nodes: items, buynow, users, edges: {items=>#items.bought_now}>, buynow=>#buynow.item, buynow.buyer}>, users=>#users.bought_now}>}) * 1

i2653317939

Cost: 0.022549660463838647

Support queries

SELECT items.name, items.description, items.initial_price, items.quantity, items.reserve_price, items.buy_now, items.nb_of_bids, items.max_bid, items.start_date, items.end_date FROM items WHERE items.id = ? -- -112376952874333891

Updates

INSERT INTO bids SET id=?, qty=?, bid=?, date=? AND CONNECT TO item(?), user(?) -- 20

Total cost: 0.058428428927677294

i193173044

Cost: 0.022549660463838647

Support queries

SELECT users.nickname FROM users WHERE users.id = ? -- -3534268957070853058

Updates

Insert i193173044 [items.id] [bids.date, bids.id, users.id] → [users.nickname, bids.qty, bids.bid] $16800000 Graph(nodes: users, bids, items, edges: {users=>#users.bids}>, bids=>#bids.user, bids.item}>, items=>#items.bids}>}) * 1.0

i3563903410

Cost: 0.013329108

Updates

Insert i3563903410 [items.id] [bids.bid, bids.id] → [bids.qty, bids.date] $11200000 Graph(nodes: bids, items, edges: {bids=>#bids.item}>, items=>#items.bids}>}) * 1

i3553045793

Cost: 0.022549660463838647

Support queries

SELECT items.end_date FROM items WHERE items.id = ? -- -3534268959885015253

Updates

Insert i3553045793 [users.id] [items.end_date, bids.id, items.id] → [] $11200000 Graph(nodes: items, bids, users, edges: {items=>#items.bids}>, bids=>#bids.item, bids.user}>, users=>#users.bids}>}) * 1.0

UPDATE items SET nb_of_bids=?, max_bid=? WHERE items.id=? -- 22

Total cost: 0.13530967214687054

i1888493477

Cost: 0.013329108

Updates

i3392968797

Cost: 0.031770212927677295

Support queries

SELECT items.end_date FROM items WHERE items.id = ? -- 2325728223400549092

SELECT category.id FROM items.category WHERE items.id = ? -- 2325728223400549092

Index lookup i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0/1

Updates

i2653317939

Cost: 0.0358904778276773

Support queries

SELECT buyer.id, bought_now.date, bought_now.id FROM items.bought_now.buyer WHERE items.id = ? -- 2325728224140142986

Updates

i2337785568

Cost: 0.022549660463838647

Support queries

SELECT seller.id FROM items.seller WHERE items.id = ? -- 2325728224451527769

Index lookup i578710568 [items.id] [users.id] → [] $640000 Graph(nodes: items, users, edges: {items=>#items.seller}>, users=>#users.items_sold}>}) * 1.0/1

Updates

i1979490368

Cost: 0.031770212927677295

Support queries

SELECT region.id, seller.id FROM items.seller.region WHERE items.id = ? -- 2325728224746922745

SELECT category.id FROM items.category WHERE items.id = ? -- 2325728224746922745

Index lookup i3264766123 [items.id] [categories.id] → [] $640000 Graph(nodes: items, categories, edges: {items=>#items.category}>, categories=>#categories.items}>}) * 1.0/1

Updates

UPDATE users SET rating=? WHERE users.id=? -- 27

Total cost: 0.013329108

i2906147889

Cost: 0.013329108

Updates

INSERT INTO comments SET id=?, rating=?, date=?, comment=? AND CONNECT TO to_user(?), from_user(?), item(?) -- 28

Total cost: 0.035878768463838646

i3157175159

Cost: 0.013329108

Updates

Insert i3157175159 [users.id] [comments.id] → [comments.rating, comments.date, comments.comment] $17800000 Graph(nodes: comments, users, edges: {comments=>#comments.to_user}>, users=>#users.comments_received}>}) * 1

i915430138

Cost: 0.022549660463838647

Support queries

SELECT users.nickname FROM users WHERE users.id = ? -- -1600343366950554648

Updates

Insert i915430138 [comments.id] [users.id] → [users.nickname] $4400000 Graph(nodes: comments, users, edges: {comments=>#comments.from_user}>, users=>#users.comments_sent}>}) * 1.0

Summary

Total size: 96032445
Total cost: 1.1107143125642815