Global Routing

Global Table Routing

Vitess has an implicit feature of routing the queries to the appropriate keyspace based on the table specified in the from list. This differs from the standard mysql, in mysql unqualified tables will fail if the correct database is not set on the connection.

This feature works only for unique table names provided in the VSchema, and only when no default keyspace is set on the connection. One exception to the uniqueness rule is Reference Tables that explicitly specify a source table.

Example:

  1. mysql> show keyspaces;
  2. +----------+
  3. | Database |
  4. +----------+
  5. | ks       |
  6. | customer |
  7. | commerce |
  8. +----------+
  9. 3 rows in set (0.00 sec)

ks and customer are sharded keyspaces and commerce is an unsharded keyspace.

Tables present in each of the keyspace.

  1. mysql> show tables from ks;
  2. +--------------+
  3. | Tables_in_ks |
  4. +--------------+
  5. | customer     |
  6. | matches      |
  7. | player       |
  8. +--------------+
  9. 3 rows in set (0.00 sec)
  11. mysql> show tables from customer;
  12. +--------------------+
  13. | Tables_in_customer |
  14. +--------------------+
  15. | corder             |
  16. | customer           |
  17. +--------------------+
  18. 2 rows in set (0.00 sec)
  20. mysql> show tables from commerce;
  21. +--------------------+
  22. | Tables_in_commerce |
  23. +--------------------+
  24. | customer_seq       |
  25. | order_seq          |
  26. | product            |
  27. +--------------------+
  28. 3 rows in set (0.00 sec)

Without a default keyspace we can route to unique tables like corder, product and player but cannot route to customer

  1. mysql> show columns from corder;
  2. +-------------+----------------+------+-----+---------+-------+
  3. | Field       | Type           | Null | Key | Default | Extra |
  4. +-------------+----------------+------+-----+---------+-------+
  5. | order_id    | bigint         | NO   | PRI | NULL    |       |
  6. | customer_id | bigint         | YES  |     | NULL    |       |
  7. | sku         | varbinary(128) | YES  |     | NULL    |       |
  8. | price       | bigint         | YES  |     | NULL    |       |
  9. +-------------+----------------+------+-----+---------+-------+
  10. 4 rows in set (0.01 sec)
  12. mysql> show columns from product;
  13. +-------------+----------------+------+-----+---------+-------+
  14. | Field       | Type           | Null | Key | Default | Extra |
  15. +-------------+----------------+------+-----+---------+-------+
  16. | sku         | varbinary(128) | NO   | PRI | NULL    |       |
  17. | description | varbinary(128) | YES  |     | NULL    |       |
  18. | price       | bigint         | YES  |     | NULL    |       |
  19. +-------------+----------------+------+-----+---------+-------+
  20. 3 rows in set (0.00 sec)
  22. mysql> show columns from player;
  23. +-----------+-------------+------+-----+---------+-------+
  24. | Field     | Type        | Null | Key | Default | Extra |
  25. +-----------+-------------+------+-----+---------+-------+
  26. | player_id | bigint      | NO   | PRI | NULL    |       |
  27. | name      | varchar(50) | NO   |     | NULL    |       |
  28. +-----------+-------------+------+-----+---------+-------+
  29. 2 rows in set (0.00 sec)
  31. mysql> show columns from customer;
  32. ERROR 1105 (HY000): ambiguous table reference: customer

With the default keyspace set to customer we can only query tables in commerce i.e customer and corder.

  1. mysql> use customer
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> show columns from customer;
  7. +-------------+----------------+------+-----+---------+-------+
  8. | Field       | Type           | Null | Key | Default | Extra |
  9. +-------------+----------------+------+-----+---------+-------+
  10. | customer_id | bigint         | NO   | PRI | NULL    |       |
  11. | email       | varbinary(128) | YES  |     | NULL    |       |
  12. +-------------+----------------+------+-----+---------+-------+
  13. 2 rows in set (0.01 sec)
  15. mysql> show columns from corder;
  16. +-------------+----------------+------+-----+---------+-------+
  17. | Field       | Type           | Null | Key | Default | Extra |
  18. +-------------+----------------+------+-----+---------+-------+
  19. | order_id    | bigint         | NO   | PRI | NULL    |       |
  20. | customer_id | bigint         | YES  |     | NULL    |       |
  21. | sku         | varbinary(128) | YES  |     | NULL    |       |
  22. | price       | bigint         | YES  |     | NULL    |       |
  23. +-------------+----------------+------+-----+---------+-------+
  24. 4 rows in set (0.00 sec)
  26. mysql> show columns from product;
  27. ERROR 1105 (HY000): table product not found

With a default keyspace set, the queries can be routed to other keyspaces by specifying the table qualifier.

  1. mysql> use customer
  2. Reading table information for completion of table and column names
  3. You can turn off this feature to get a quicker startup with -A
  5. Database changed
  6. mysql> show columns from ks.player;
  7. +-----------+-------------+------+-----+---------+-------+
  8. | Field     | Type        | Null | Key | Default | Extra |
  9. +-----------+-------------+------+-----+---------+-------+
  10. | player_id | bigint      | NO   | PRI | NULL    |       |
  11. | name      | varchar(50) | NO   |     | NULL    |       |
  12. +-----------+-------------+------+-----+---------+-------+
  13. 2 rows in set (0.00 sec)
  15. mysql> show columns from commerce.product;
  16. +-------------+----------------+------+-----+---------+-------+
  17. | Field       | Type           | Null | Key | Default | Extra |
  18. +-------------+----------------+------+-----+---------+-------+
  19. | sku         | varbinary(128) | NO   | PRI | NULL    |       |
  20. | description | varbinary(128) | YES  |     | NULL    |       |
  21. | price       | bigint         | YES  |     | NULL    |       |
  22. +-------------+----------------+------+-----+---------+-------+
  23. 3 rows in set (0.00 sec)