Picasso Database Query Optimizer Visualizer
©Indian Institute of Science,
(The following setup procedure is known to work with MySQL 5.1, 5.4.1 and 5.5.9 on Windows with InnoDB as storage engine.)
mysql --host=localhost -u UserName -p Password --database=DbName
< “filename”
For example,
mysql
--host=localhost -u root -p pass123
--database=TPCH <
“c:\\tpch\\dss.ddl”
Note: escape sequence “\\” required on windows while
giving file path.
load data local
INFILE '<filename>'
INTO TABLE '<tablename>'
FIELDS TERMINATED BY '<delimiter>'
For example, if the data is pipe-separated:
load data local
INFILE 'customer.tbl'
INTO TABLE CUSTOMER
FIELDS TERMINATED BY '|'
mysql --host=localhost -u UserName -p Password --port=portno
--database=DbName < “filename”
For example,
mysql --host=localhost -u
root -p pass123 --port=3308
--database=TPCH < “c:\\tpch\\dss.ri”
ANALYZE TABLE table_name;
a) optimizer_search_depth
b) optimizer_prune_level
For detailed explanation of optimization
parameters, see
http://dev.mysql.com/doc/refman/5.5/en/controlling-query-plan-evaluation.html
Notes:
1.
PSP should be on an indexed attribute.
2.
Query Template should not involve any form of sub-query. Valid query templates include
SPJ queries which may contain aggregation functions, GROUP BY, ORDER BY and
HAVING clause.
3.
For compiled plan tree diagram, cost for intermediate nodes is not available.
Only final cost is available.
4. As
the histograms are not natively supported by the database, we have used the
equi-depth and value based techniques to construct them. The type of histogram
depends on the number of distinct values. If the distinct values are fewer than
number of buckets (default 75), a value based histogram is constructed
otherwise an equi-depth histogram is constructed.
5.
The diagrams on the Picasso website have been generated with index on the
varying attribute and InnoDB as the storage engine.