Quick Start Guide
Use this link to download the latest version.
There’s a precompiled binary for Windows, just unzip it and run.
On Linux the installation is trivial as the tool doesn’t yet have any dependencies. You will need autoconf, automake and the GNU C++ compiler.
- gunzip
- ./configure
- make
- src/qot-test (please make sure tests do pass for you)
- sudo make install
To get the idea of what you can actually do with the tool try “qot –help”. Basically you can get some information about your queries and indexes and ask the tool to propose you additional indexes to improve the performance of your queries. You can also control the way output is generated - plain text, SQL, or XML. To make some real use you will need to pass the tool some schema definition, queries and specify type of action over them. You don’t need any running or even installed MySQL server or client as the tool is fully standalone.
Just for the first test copy the following DDL into a file qot_input.sql and save it in your home directory:
CREATE SCHEMA qot_test;
USE qot_test;
CREATE TABLE clients ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(255), last_name VARCHAR(255));
CREATE TABLE orders ( id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, client_id INT NOT NULL, entity VARCHAR(255), amount INT NOT NULL, total INT NOT NULL);
pretty simple and obvious: we have clients and clients can make orders. One client can make unlimited amount of orders, and an order can belong to only one client.
To show basic tool usage we will review 4 query examples. We will start with a simple one just to familiarize ourselves with the tool:
Query 1:
SELECT * FROM clients WHERE id = 1;
Shell command:
$ qot --input-file=qot_input.sql --input-query="SELECT * FROM clients WHERE id = 1"\ --info
The first argument tells the tool to read some input from the specified file (you can specify –input-file option more than once). Additionally we want the tool to read some SQL from the command line (as we don’t want to put volatile data into a file), so we specify the query (or queries) using –input-query option. Finally we specify what kind of action we want to perform on the query: –info means “print some basic info about the query”…
Output:
/* Output produced by qot 0.01 GPL */
/*
Query: SELECT * FROM clients WHERE id = 1
selectivity: zero or one row
tables used in this query: clients (zero or one row)
existing lookup indexes for this query:
clients.PRIMARY(id)
existing covering indexes for this query:
(none)
*/
The output should be pretty easy to understand. Selectivity is the cardinality of resultset, it’s estimated based on the index information and WHERE-filters. The selectivity estimate is provided in summary and by table - in braces after the table name in the used tables list. For this example we have selectivity “zero or one”, which is expected for a select-by-PK query. Under the existing lookup indexes title we see the list of indexes that the optimizer would consider for this query. The information about covering and lookup indexes is pretty much the same as provided by EXPLAIN SELECT MySQL command.
In the next query example we will consider a query with suboptimal underlying indexing and try to improve it by providing appropriate indexes.
Query 2:
SELECT entity FROM orders WHERE client_id = 1
(as you remember client_id doesn’t have any associated indexes)
Shell command:
$ qot --input-file=qot_input.sql --input-query= \ "SELECT entity FROM orders WHERE client_id = 1" \ --info --propose=index --propose=cov-index
By specifying –propose=index and –propose=cov-index options we make the tool propose new indexes that could be considered by the MySQL optimizer for lookup or index-only table access (index scan). Based on the provided query the tool will evaluate the optimal index form, and will check if there are existing indexes that satisfy this form. If there’s no, then it will propose to create new ones.
Output:
/* Output produced by qot 0.01 GPL */
/*
Query: SELECT entity FROM orders WHERE client_id = 1
selectivity: zero or more rows
tables used in this query: orders (zero or more rows)
existing lookup indexes for this query:
(none)
existing covering indexes for this query:
(none)
*/
/* additional lookup indexes that can be created to improve query performance */
CREATE INDEX index0 ON `qot_test`.`orders` (
client_id);
/* addtional covering indexes that can be created to improve query performance */
CREATE INDEX ix0 ON `qot_test`.`orders` (
client_id,
entity);
The first thing to notice is that there’s no existing indexes available to the optimizer to perform lookup. This means that it will fall-back to full table scan. Also the tool proposes to create lookup and/or covering indexes that can improve query performance. One thing to notice is that the CREATE INDEX is generated out of comment, so you can apply qot output directly to the server.
In the next query example we will see how the tool can optimize multi-table query. It should be noticed that at the moment only so called theta-join syntax is supported, which means the only join operator you can use is ‘,’ and all join conditions should be specified in the WHERE-clause. Broader join syntax support is planned for the future qot versions.
Query 3:
SELECT orders.id, orders.client_id, orders.entity FROM clients, orders WHERE first_name = 'Bean' AND client_id = clients.id
Shell command:
$ qot --input-file=qot_input.sql \ --input-query="SELECT orders.id, orders.client_id, orders.entity \ FROM clients, orders WHERE first_name = 'Bean' AND \ client_id = clients.id" --info --propose=index --propose=cov-index
Output:
/* Output produced by qot 0.01 GPL */
/*
Query: SELECT orders.id, orders.client_id, orders.entity FROM clients, orders
WHERE first_name = 'Bean' AND client_id = clients.id
selectivity: zero or more rows
tables used in this query: clients (zero or more rows), orders (zero or more rows)
existing lookup indexes for this query:
clients.PRIMARY(id)
existing covering indexes for this query:
(none)
*/
/* additional lookup indexes that can be created to improve query performance */
CREATE INDEX index1 ON `qot_test`.`orders` (
client_id);
CREATE INDEX index0 ON `qot_test`.`clients` (
first_name);
/* addtional covering indexes that can be created to improve query performance */
CREATE INDEX ix0 ON `qot_test`.`clients` (
id,
first_name);
CREATE INDEX ix1 ON `qot_test`.`orders` (
client_id,
id,
entity);
As you can see the tool proposes lookup indexes for the fields used equi-predicates in WHERE clause, as well as covering indexes for all query fields.
The last (but not the one of the least importance) example is about multiquery optimization. We will pass the tool 2 queries and it will propose us a lookup index which will be optimal for both queries.
Queries 4:
SELECT * FROM orders WHERE total = 100 AND amount = 1; SELECT * FROM orders WHERE amount > 10;
Shell command:
$ qot --input-file=qot_input.sql \ --input-query="SELECT * FROM orders \ WHERE total = 100 AND amount = 1; \ SELECT * FROM orders WHERE amount > 10" \ --info --propose=merged-index;
Output:
/* Output produced by qot 0.01 GPL */
/*
Query: SELECT * FROM orders WHERE total = 100 AND amount = 1
selectivity: zero or more rows
tables used in this query: orders (zero or more rows)
existing lookup indexes for this query:
(none)
existing covering indexes for this query:
(none)
*/
/*
Query: SELECT * FROM orders WHERE amount > 10
selectivity: zero or more rows
tables used in this query: orders (zero or more rows)
existing lookup indexes for this query:
(none)
existing covering indexes for this query:
(none)
*/
/* additional merged lookup indexes that can be created to improve performance of *all* the above queries */
CREATE INDEX index0 ON `qot_test`.`orders` (
amount,
total);
the option to use in this case was –propose=merged-index. When this option is used the tool first looks for an existing index(es) that would be fine for given query set, and if it’s not found, it creates new index(es).
Getting More Info and Staying Up-to-date
To get the full list of available qot options run “qot –help”.
If you want to get the latest qot news, subscribe to RSS or Atom feed.