CONTENT
1. MYSQL SERVER OVERVIEW
1.1 Query
Processor
1.1.1 Embedded DML
Precompiler
1.1.2
DDL Compiler
1.1.3 Query Parser
1.1.4 Query Preprocessor
1.1.5 Security / Integration Manager
1.1.6 Query Optimizer
1.1.7 Execution Engine
2. SLOW QUERY LOGGING
2.1.1 Enabling Slow
Query log
2.1.2
Setting the long query time
2.1.3 Analyzing the slow query log
3. MYSQL OPTIMIZER (EXPLAIN COMMAND)
3.1.1 EXPLAIN Syntax
3.1.2 EXPLAIN Output
3.1.3 Data Access Strategy with
examples
3.1.4
Explain Plan Indexes
3.1.5
Rows
3.1.6
Extra
3.1.7
Subqueries and EXPLAIN
4. MYSQL PROFILER
4.1.1 Using the
Profiler for Tuning Queries
5. INDEXING STRATEGIES
5.1.1 Index
selectivity
1.
MySQL Server Overview
The conceptual architecture of MySQL is
illustrated in Figure 1 below.
Fig1
: MySQL Conceptual Architecture
1.1. Query Processor
The vast majority of interactions in the system
occur when a user wishes to view or manipulate the underlying data in storage.
These queries, which are specified using a data-manipulation language (i.e.
SQL), are parsed and optimized by a query processor.
1.1.1.
Embedded DML Precompiler
When a request is received from a client in the
application layer, it is the responsibility of the embedded DML (Data
Manipulation Language) precompiler to extract the relevant SQL statements
embedded in the client API commands, or to translate the client commands into
the corresponding SQL statements. This is the first step in the actual
processing of a client application written in a programming language such as
C++ or Perl, before compiling the SQL query. The client request could come from
commands executed from an application interface (API), or an application
program. This is prevalent in all general RDBMS's. MySQL has this component in
order to process the MySQL client application request into the format that
MySQL understands.
1.1.2.
DDL Compiler
Requests to access the MySQL databases received
from an administrator are processed by the DDL (Data Definition Language)
compiler. The DDL compiler compiles the commands (which are SQL statements) to
interact directly with the database. The administrator and administrative
utilities do not expose an interface, and hence execute directly to the MySQL
server. Therefore, the embedded DML precompiler does not process it, and this
explains the need for a DDL compiler.
1.1.3.
Query Parser
After the relevant SQL query statements are
obtained from deciphering the client request or the administrative request, the
next step involves parsing the MySQL query. In this stage, the objective of the
query parser is to create a parse tree structure based on the query so that it
can be easily understood by the other components later in the pipeline.
1.1.4.
Query Preprocessor
The query parse tree, as obtained from the query
parser, is then used by the query preprocessor to check the SQL syntax and
check the semantics of the MySQL query to determine if the query is valid. If
it is a valid query, then the query progresses down the pipeline. If not, then
the query does not proceed and the client is notified of the query processing
error.
1.1.5.
Security/Integration Manager
Once the MySQL query is deemed to be valid, the
MySQL server needs to check the access control list for the client. This is the
role of the security integration manager which checks to see if the client has
access to connecting to that particular MySQL database and whether he/she has
table and record privileges. In this case, this prevents malicious users from
accessing particular tables and records in the database and causing havoc in
the process.
1.1.6.
Query Optimizer
After determining that the client has the proper
permissions to access the specific table in the database, the query is then
subjected to optimization. MySQL uses the query optimizer for executing SQL
queries as fast as possible. As a result, this is the reason why the
performance of MySQL is fast compared to other RDBMS's. The task of the MySQL
query optimizer is to analyze the processed query to see if it can take
advantage of any optimizations that will allow it to process the query more
quickly. MySQL query optimizer uses indexes whenever possible and uses the most
restrictive index in order to first eliminate as many rows as possible as soon
as possible. Queries can be processed more quickly if the most restrictive test
can be done first.
1.1.7.
Execution Engine
Once the MySQL query optimizer has optimized the
MySQL query, the query can then be executed against the database. This is
performed by the query execution engine, which then proceeds to execute the SQL
statements and access the physical layer of the MySQL database from Figure 1.
As well the database administrator can execute commands on the database to
perform specific tasks such as repair, recovery, copying and backup, which it
receives from the DDL compiler.
2.
Slow Query Logging
The
slow query log is one of the less-used logs, as by default it's not activated,
but it's a useful log for identifying queries that are not optimal. Often, in
an under-performing system, especially with the default MyISAM tables (that
make use of table-level locking, not row-level locking), a single query may be
the cause of problems.
2.1.1. Enabling slow query log
To
enable or disable the setting dynamically in MySQL 5.1.36 run the following
query to enable it:
SET
slow_query_log = ON;
and
to disable it:
SET
slow_query_log = OFF;
2.1.2.
Setting the long query time
You can also specify how long a
quey needs to run for before it is logged with the "long_query_time"
setting. By default this is 10 seconds.
In the my.cnf file, to change it to e.g.
5 seconds add the following:
long_query_time = 5
This can be changed dynamically in MySQL
5.0.0+ (and possibly earlier versions) by running the following query:
set global long_query_time = 5;
This will only work for new connections;
any connections which have already been established will continue to use the
old setting. Once the user disconnects and reconnects their new connection will
use the new setting.
2.1.3.
Analyzing the slow query log
Analyzing the slow query log can be
tricky, and you need to take care to avoid some basic pitfalls. The queries are
written to the log after the query has been executed, and all locks released,
which may be a different order to that in which they were run. The first query
logged in the slow query log may be absolutely optimal, but have been delayed
by a prior query, still running, that consumed most of the available resources.
Have a look a little further in the log to see if you can identify a query that
is the guilty one. Consider the following example:
#
Time: 100922 6:19:47
#
User@Host: vanillaemail[vanillaemail] @
[65.60.24.250]
#
Query_time: 0.014752 Lock_time: 0.000062
Rows_sent: 0 Rows_examined: 6185
SET
timestamp=1285136387;
select * from job_queue where status = 'PNDG' and
scheduled_date <= CURRENT_TIMESTAMP;
#
Time: 100922 6:20:01
#
User@Host: vanillaemail[vanillaemail] @
[65.60.24.242]
#
Query_time: 0.014737 Lock_time: 0.000067
Rows_sent: 0 Rows_examined: 6185
SET
timestamp=1285136401;
select * from job_queue where status = 'PNDG' and
job_type = 'IMPORT' and data = 'www' and scheduled_date <= CURRENT_TIMESTAMP;
mysqldumpslow
A tool that comes with the MySQL server, and used for displaying the results of a slow query log is mysqldumpslow. Simply use it with the name of the slow query log as the only parameter, for example:
A tool that comes with the MySQL server, and used for displaying the results of a slow query log is mysqldumpslow. Simply use it with the name of the slow query log as the only parameter, for example:
This program parses and
summarizes a 'slow query log'.
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log.
Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s
ORDER what to sort by (al, at, ar, c,
l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last
instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and
strings to 'S'
-n NUM abstract numbers with at least n digits
within names
-g
PATTERN grep: only consider stmts that
include this string
-h
HOSTNAME hostname of db server for
*-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using
mysql.server startup script)
-l don't subtract lock time from total
time
% mysqldumpslow
/opt/mysql/data/log/mysql-slow.log
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), vanillabilling[vanillabilling]@[59.90.244.90]
SELECT COUNT(*) AS `RowCount` FROM `PROFILING`
Count: 2 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), vanillaemail[vanillaemail]@[65.60.24.242]
CALL sp_topup_process(N,'S','S','S',@com_mysql_jdbc_outparam_payAmount,@com_mysql_jdbc_outparam_payDesc,@com_mysql_jdbc_outparam_result,@com_mysql_jdbc_outparam_csstring)
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), vanillaemail[vanillaemail]@[65.60.24.242]
CALL sp_change_subscription(N,N,'S','S',@com_mysql_jdbc_outparam_payAmount,@com_mysql_jdbc_outparam_payDesc,@com_mysql_jdbc_outparam_result,@com_mysql_jdbc_outparam_csstring)
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), vanillaemail[vanillaemail]@[65.60.24.242]
CALL delete_filter(N,N,@com_mysql_jdbc_outparam_results)
3.
MySQL Optimizer (
EXPLAIN Command)
EXPLAIN
shows how your queries are being used.
By putting it before a SELECT, you can see whether indexes are being used
properly, and what kind of join is being performed.
·
How
many tables are involved
·
How
the tables are joined
·
How
the data is looked up
·
If
there are sub queries
·
If
there are unions
·
If
DISTINCT is used
·
If
a WHERE clause is used
·
If
a temporary table is used
·
Possible
indexes used
·
Actual
indexes used
·
Length
of actual indexes used
·
Approximate
number of records returned
·
If
sorting requires an extra pass through the data
The
information is known as the query execution plan, and is also referred to as
the
EXPLAIN
plan.
3.1.1. Syntax:
EXPLAIN
Syntax (Get Information About a SELECT)
EXPLAIN tbl_name
Or:
EXPLAIN SELECT select_options
3.1.2. EXPLAIN Output
Each output row from EXPLAIN provides information about one table, and each row consists of the following columns:
·
id
·
The SELECT
identifier. This is the sequential number of the SELECT
within the query.
·
select_type
·
The type of SELECT,
which can be any of the following:
o
SIMPLE
Simple SELECT (not
using UNION or
subqueries)
o
PRIMARY
Outermost SELECT
o
UNION
Second or later SELECT
statement in a UNION
o
DEPENDENT UNION
Second or later SELECT
statement in a UNION, dependent on outer subquery
o
SUBQUERY
First SELECT in
subquery
o
DEPENDENT SUBQUERY
First SELECT in
subquery, dependent on outer subquery
o
DERIVED
Derived table SELECT
(subquery in FROM clause)
·
table
The table to which the row of output
refers.
·
type
The join type. The different join types
are listed here, ordered from the best type to the worst:
o
system
The table has only one row (= system
table). This is a special case of the const join
type.
o
const
The table has at most one matching row,
which will be read at the start of the query. Because there is only one row,
values from the column in this row can be regarded as constants by the rest of
the optimizer. const tables are very fast because
they are read only once!
const is used when
you compare all parts of a PRIMARY KEY or UNIQUE
index with constant values. In the following queries, tbl_name can
be used as a const table:
SELECT * FROM tbl_name WHERE primary_key=1;
SELECT * FROM tbl_name
WHERE primary_key_part1=1 AND primary_key_part2=2;
o
eq_ref
One row will be read from this table for
each combination of rows from the previous tables. Other than the const
types, this is the best possible join type. It is used when all parts of an
index are used by the join and the index is a PRIMARY KEY or UNIQUE
index.
eq_ref can be used for
indexed columns that are compared using the =
operator. The comparison value can be a constant or an expression that uses
columns from tables that are read before this table.
In the following examples, MySQL can use
an eq_ref join
to process ref_table:
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
o
ref
All rows with matching index values will
be read from this table for each combination of rows from the previous tables. ref is
used if the join uses only a leftmost prefix of the key or if the key is not a PRIMARY
KEY
or UNIQUE
index (in other words, if the join cannot select a single row based on the key
value). If the key that is used matches only a few rows, this is a good join
type.
ref can be used for
indexed columns that are compared using the =
operator.
In the following examples, MySQL can use
a ref join
to process ref_table:
SELECT * FROM ref_table WHERE key_column=expr;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table
WHERE ref_table.key_column_part1=other_table.column
AND ref_table.key_column_part2=1;
o
ref_or_null
This join type is like ref, but
with the addition that MySQL will do an extra search for rows that contain NULL
values. This join type optimization is new for MySQL 4.1.1 and is mostly used
when resolving subqueries.
In the following examples, MySQL can use
a ref_or_null join
to process ref_table:
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;
See Section 6.2.6, "How MySQL
Optimizes IS
NULL."
o
index_merge
This join type indicates that the Index
Merge
optimization is used. In this case, the key
column contains a list of indexes used, and key_len
contains a list of the longest key parts for the indexes used. For more
information, see Section 6.2.5, "How MySQL Optimizes OR
Clauses."
o
unique_subquery
This type replaces ref for
some IN
subqueries of the following form:
value IN (SELECT primary_key FROM single_table WHERE some_expr)
unique_subquery is
just an index lookup function that replaces the subquery completely for better
efficiency.
o
index_subquery
This join type is similar to unique_subquery. It
replaces IN
subqueries, but it works for non-unique indexes in subqueries of the following
form:
value IN (SELECT key_column FROM single_table WHERE some_expr)
o
range
Only rows that are in a given range will
be retrieved, using an index to select the rows. The key
column indicates which index is used. The key_len
contains the longest key part that was used. The ref
column will be NULL for this type.
range can be used for
when a key column is compared to a constant using any of the =, <>, >, >=, <, <=, IS
NULL,
<=>, BETWEEN, or IN
operators:
SELECT * FROM tbl_name
WHERE key_column = 10;
SELECT * FROM tbl_name
WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name
WHERE key_column IN (10,20,30);
SELECT * FROM tbl_name
WHERE key_part1= 10 AND key_part2 IN (10,20,30);
o
index
This join type is the same as ALL,
except that only the index tree is scanned. This usually is faster than ALL,
because the index file usually is smaller than the data file.
MySQL can use this join type when the
query uses only columns that are part of a single index.
o
ALL
A full table scan will be done for each
combination of rows from the previous tables. This is normally not good if the
table is the first table not marked const, and
usually very bad in all other cases. Normally, you can avoid ALL by
adding indexes that allow row retrieval from the table based on constant values
or column values from earlier tables.
·
possible_keys
The possible_keys
column indicates which indexes MySQL could use to find the rows in this table.
Note that this column is totally independent of the order of the tables as
displayed in the output from EXPLAIN. That means
that some of the keys in possible_keys
might not be usable in practice with the generated table order.
If this column is NULL, there
are no relevant indexes. In this case, you may be able to improve the
performance of your query by examining the WHERE
clause to see whether it refers to some column or columns that would be
suitable for indexing. If so, create an appropriate index and check the query
with EXPLAIN
again.
To see what indexes a table has, use SHOW
INDEX FROM tbl_name.
·
key
The key
column indicates the key (index) that MySQL actually decided to use. The key is
NULL if
no index was chosen. To force MySQL to use or ignore an index listed in the possible_keys
column, use FORCE INDEX, USE
INDEX,
or IGNORE
INDEX
in your query.
For MyISAM and BDB
tables, running ANALYZE TABLE will help the
optimizer choose better indexes. For MyISAM
tables, myisamchk
--analyze
will do the same. See Section 4.6.2, "Table Maintenance and Crash
Recovery."
·
key_len
The key_len
column indicates the length of the key that MySQL decided to use. The length is
NULL if
the key
column says NULL. Note that the value of key_len
allows you to determine how many parts of a multiple-part key MySQL will
actually use.
·
ref
The ref
column shows which columns or constants are used with the key to
select rows from the table.
·
rows
The rows
column indicates the number of rows MySQL believes it must examine to execute
the query.
·
Extra
This column contains additional
information about how MySQL will resolve the query. Here is an explanation of
the different text strings that can appear in this column:
o
Distinct
MySQL will stop searching for more rows
for the current row combination after it has found the first matching row.
o
Not exists
MySQL was able to do a LEFT
JOIN
optimization on the query and will not examine more rows in this table for the
previous row combination after it finds one row that matches the LEFT
JOIN
criteria.
Here is an
example of the type of query that can be optimized this way:
SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id
WHERE t2.id IS NULL;
Assume that t2.id is
defined as NOT NULL. In this case,
MySQL will scan t1 and look up the rows in t2
using the values of t1.id. If MySQL finds
a matching row in t2, it knows that t2.id can
never be NULL, and
will not scan through the rest of the rows in t2 that
have the same id value. In other words, for each
row in t1,
MySQL needs to do only a single lookup in t2, regardless
of how many rows actually match in t2.
o
range checked for each record (index
map: #)
MySQL found no good index to use.
Instead, for each row combination in the preceding tables, it will do a check
to determine which index to use (if any), and use it to retrieve the rows from
the table. This is not very fast, but is faster than performing a join with no
index at all.
o
Using filesort
MySQL will need to do an extra pass to
find out how to retrieve the rows in sorted order. The sort is done by going
through all rows according to the join type and storing the sort key and
pointer to the row for all rows that match the WHERE
clause. The keys then are sorted and the rows are retrieved in sorted order.
o
Using index
The column information is retrieved from
the table using only information in the index tree without having to do an
additional seek to read the actual row. This strategy can be used when the
query uses only columns that are part of a single index.
o
Using temporary
To resolve the query, MySQL will need to
create a temporary table to hold the result. This typically happens if the
query contains GROUP BY and ORDER
BY
clauses that list columns differently.
o
Using where
A WHERE
clause will be used to restrict which rows to match against the next table or
send to the client. Unless you specifically intend to fetch or examine all rows
from the table, you may have something wrong in your query if the Extra
value is not Using where and the table
join type is ALL or index.
If you want to make your queries as fast
as possible, you should look out for Extra
values of Using
filesort
and Using
temporary.
3.1.2.
Data Access Strategy
Full
Table Scan
Usually, the slowest data access
method is to access each record in table. This happens when there is no
suitable index that can make data retrieval faster. In the EXPLAIN plan, rows
that use the slowest data access method have a type of ALL. When executing the
query, MySQL will perform a full table scan:
mysql> EXPLAIN SELECT
join_date from member \G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: member
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 109
Extra:
1 row in set (0.00 sec)
·
MySQL will scan each record in the
member table to retrieve the join_date.
·
A full table scan is done in this
example because there is no WHERE clause, and no index on join_date.
·
There are other reasons (besides this
example’s lack of WHERE clause and index on join_date) that cause there to be
no suitable index that can make data retrieval faster.
·
Adding an index on join_date would make
this use a full index scan instead.
Full
Index Scan
A data access method
that is usually slightly faster is when the query execution plan scans each
index record for a table. This is known as a full index scan, and is shown in
an EXPLAIN plan by a type of index:
mysql>
EXPLAIN SELECT email from member \G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: member
type: index
possible_keys:
NULL
key: email_idx
key_len: 302
ref: NULL
rows: 109
Extra: Using index
1 row in set
(0.00 sec)
·
There is an index on email, but the lack
of WHERE clause means that every index record must be read and returned.
·
In order for the data access method to
be set as a full index scan, there must be a suitable index, and the query
optimizer must decide whether using the index will make the data lookup faster.
Partial
Index Scan
In a partial index
scan, only a part of the index is scanned. A partial index scan has a type of
range, because most often it is caused by a query searching for records in a
certain range.
mysql> EXPLAIN
SELECT userid FROM member WHERE join_date BETWEEN '2010-09-20 00:00:00' and
'2010-09-22 23:59:59' \G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: member
type: range
possible_keys:
Index_8
key: Index_8
key_len: 5
ref: NULL
rows: 54
Extra: Using where; Using index
1 row in set
(0.00 sec)
Using
more than one Index
The type of
index_merge is very special — it is the only data access strategy that can
utilize more than one index. The query optimizer will decide the best plan
involves using more than one index if the WHERE clause involves conditions
that, when examined separately, have a data access strategy of either a partial
or a full index scan. This means that the WHERE clause must involve more than
one condition, that at least two of the conditions have suitable indexes, and
that the index appropriate for one condition is not appropriate for another.
An example is the best
way to show this data access strategy:
mysql>
EXPLAIN SELECT userid
->
FROM member
-> WHERE email LIKE
"drajkumar@treselle.com%" OR userid<10\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: member
type: index_merge
possible_keys:
PRIMARY,email_idx
key: email_idx,PRIMARY
key_len: 302,8
ref: NULL
rows: 10
Extra: Using
sort_union(email_idx,PRIMARY); Using where
1 row in set
(0.00 sec)
· The query that this EXPLAIN plan was generated for
has conditions on the email and userid fields.
·
The
member table has a nonunique index on email and a unique index on userid.
Either the condition on email must be met, or the condition on user must be
met.
·
There
is not one ideal index to use — the ideal indexing strategy would be to use one
index to find the records that meet the condition on email, and another index
to find the records that meet the condition on userid, and then merge the two
result sets.
·
The
email index cannot help with the userid condition, and the userid index cannot
help with the email index.
·
This
is important because, if there is one index appropriate for both conditions,
the query optimizer will choose that index for its execution plan — even if the
data access strategy is slower than index_merge.
·
An
EXPLAIN plan will also show a data access strategy of range when it determines
range is actually better.
·
The
general rule is that if there is a single index mysqld can use, it prefers that
over using more than one index and the index_merge data access strategy.
Data access
strategy for fulltext searching
The data access strategy for a fulltext search is a type of fulltext:
mysql>
EXPLAIN SELECT film_id, title
-> FROM
film_text
-> WHERE MATCH
(title,description) AGAINST (’storm’)\G
***************************
1. row ***************************
id: 1
select_type:
SIMPLE
table:
film_text
type: fulltext
possible_keys:
idx_title_description
key:
idx_title_description
key_len: 0
ref:
rows: 1
Extra: Using
where
1 row in set
(0.00 sec)
Joining and looking up nonunique index values
When searching an index over a
range of values, the range data access strategy is used. However, if you are
searching a nonunique index for an exact match, MySQL does not need to access a
range of values. The query optimizer can search the index for one value and
find all of the records that match. This data access strategy will return a
type of ref in the EXPLAIN plan:
mysql> EXPLAIN SELECT
listid
-> FROM list
-> WHERE companyid=2\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: list
type: ref
possible_keys: FK_list_1
key: FK_list_1
key_len: 2
ref: const
rows:
3
Extra: Using index
1 row in set (0.00 sec)
·
This query is
looking for instances where companyid exactly matches the constant 2.
·
The list
table includes an index for the field companyid, but the index is not unique.
·
If the index
were unique, the definition would include UNIQUE or PRIMARY.
·
The ref data
access strategy means that MySQL will go to the index and retrieve records that
·
match the
constant value given.
·
This is
faster than the range data access strategy, because only one value in the index
table needs to be looked up, instead of doing a partial scan of the index
table. However, once the value is found in the index table, all the records
that match will need to be retrieved.
·
The ref data
access strategy is also used when a table is joined using a nonunique index.
·
The same
process applies — each record that matches the index value is retrieved.
Joins and unique
index values
When a
join uses a unique index — that is, the index is specified as UNIQUE or PRIMARY
— the data access strategy is eq_ref. The data is accessed like ref, except
that there will be at most one matching record.
mysql> EXPLAIN SELECT
first_name,last_name
-> FROM rental
-> INNER JOIN customer
USING (customer_id)
-> WHERE rental_date
BETWEEN ’2006-02-14 00:00:00’
-> AND ’2006-02-14
23:59:59’\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: range
possible_keys:
rental_date,idx_fk_customer_id
key: rental_date
key_len: 8
ref: NULL
rows: 2614
Extra: Using where; Using
index
***************************
2. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref:
sakila.rental.customer_id
rows: 1
Extra:
2 rows in set (0.03 sec)
The eq_ref data access strategy appears when one table has a unique
index, and the other table in a join does not. In this case, customer_id is a
PRIMARY KEY on the customer table, and a nonunique index on the rental table.
Looking up unique
index values
An index
lookup on a unique value is very fast — MySQL only needs to go to the specified
value in the index and retrieve the one record associated with that index
value. In these cases, the query optimizer determines there will be fewer than
two records looked up. In the EXPLAIN plan, a type of const is returned,
reflecting that at most there is one record that will need to be retrieved:
mysql> EXPLAIN SELECT
join_date FROM member AS r WHERE userid =10\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: r
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
Extra:
1 row in set (0.01 sec)
The query optimizer sees that userid is an index marked as UNIQUE and
NOT NULL, and that the WHERE clause is testing for when userid equals a
constant. A row will have a type of const when the WHERE clause uses a constant
and an equality operator on a field defined as UNIQUE and NOT NULL. In other
words, the type is const when the WHERE clause looks like:
·
WHERE
unique_key=const – unique_key is a unique, not null, single-field key.
·
WHERE
unique_key_part1=const AND unique_key_part2 –
(unique_key_part1,unique_key_part2) is a unique, not null, two-field key.
Constant
propagation
The
query optimizer can use deductions to make better query execution plans. For
example, the query:
SELECT return_date,
first_name, last_name
FROM rental INNER JOIN
customer USING (customer_id)
WHERE rental_id = 13534\G
references two tables — rental and customer. In an EXPLAIN plan, the
rental row should have a type of const, because of the WHERE clause. But what
does the customer row look like?
mysql> EXPLAIN SELECT
return_date, first_name, last_name
-> FROM rental INNER
JOIN customer USING (customer_id)
-> WHERE rental_id =
13534\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: rental
type: const
possible_keys:
PRIMARY,idx_fk_customer_id
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
***************************
2. row ***************************
id: 1
select_type: SIMPLE
table: customer
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 2
ref: const
rows: 1
Extra:
2 rows in set (0.00 sec)
The customer row also has a type of const! The query optimizer deduced
that the customer table will have at most one record returned using the
following facts:
·
The
rental row has a type of const and thus will return at most one
record.
·
The
rental and customer tables are joined with an INNER JOIN using customer_id.
·
There
is at most one value for customer_id, because the rental row has a type of const.
·
The
customer_id field is defined as a key that
is unique and specified as NOT NULL.
This set of deductions is called constant propagation. The constant,
which causes the rental row to have a type of const, is propagated through the
join in the following manner:
1. The values for the SELECT fields and customer_id are retrieved from
the rental table:
mysql> SELECT
return_date, customer_id FROM rental WHERE rental_
id=13534;
+-------------+-------------+
| return_date |
customer_id |
+-------------+-------------+
| NULL | 75 |
+-------------+-------------+
1 row in set (0.00 sec)
2. The JOIN is replaced—instead of joining two tables, the constant is
propagated as a query on the customer table, using the filter WHERE
customer_id=75:
mysql> SELECT NULL,
first_name, last_name FROM customer WHERE customer_id=75;
+------+------------+-----------+
| NULL | first_name |
last_name |
+------+------------+-----------+
| NULL | TAMMY | SANDERS |
+------+------------+-----------+
1 row in set (0.01 sec)
This is why the eq_ref data access strategy shows up when only one table
in a join joins on a unique index. When both tables in the join are joining on
a unique index, constant propagation can occur.
3.1.3.
Explain Plan Indexes
·
The fields possible_keys, key,
key_len and ref in the EXPLAIN plan relate to indexes.
·
The possible_keys field shows
which indexes the query optimizer considers using to satisfy data filters —
that is, the WHERE clause and join conditions. If there are no indexes that can
be used for this purpose, the value of possible_keys is NULL.
·
The key field shows which index
the query optimizer actually uses. In the case of an index_merge strategy, the
key field is a comma-delimited list of indexes used. The key field sometimes
shows an index that was not listed in possible_keys. The list of possible_keys
only considers filters; however, if all of the fields retrieved are part of an
index, the query optimizer will decide that it is faster to do a full index
scan than a full data scan. Thus, it will use an access strategy of index with
a key that was not listed in possible_keys.
·
The key_len field shows the
length of the key used, in bytes. Queries that use indexes can be further
optimized by making the length of the index smaller.
·
The ref field shows what is
compared to the index. For a range of values or a full table scan, ref is NULL.
·
In a join, a field is compared to
the index, and the field name is shown as the ref field.
mysql>
EXPLAIN SELECT first_name,last_name FROM rental
-> INNER
JOIN customer USING (customer_id)
-> WHERE
rental_date BETWEEN ’2006-02-14 00:00:00’
-> AND
’2006-02-14 23:59:59’\G
***************************
1. row ***************************
id: 1
select_type:
SIMPLE
table: rental
type: range
possible_keys:
rental_date,idx_fk_customer_id
key:
rental_date
key_len: 8
ref: NULL
rows: 2614
Extra: Using
where; Using index
***************************
2. row ***************************
id: 1
select_type:
SIMPLE
table: customer
type: eq_ref
possible_keys:
PRIMARY
key: PRIMARY
key_len: 2
ref:
sakila.rental.customer_id
rows: 1
Extra:
2 rows in set
(0.03 sec)
If a constant is compared, the ref field is const:
mysql>
EXPLAIN SELECT return_date FROM rental WHERE rental_id =
13534\G
***************************
1. row ***************************
id: 1
select_type:
SIMPLE
table: rental
type: const
possible_keys:
PRIMARY
key: PRIMARY
key_len: 4
ref: const
rows: 1
Extra:
1 row in set
(0.09 sec)
A type of fulltext has a ref field that is blank:
mysql>
EXPLAIN SELECT film_id, title
-> FROM
film_text
-> WHERE
MATCH (title,description) AGAINST (’storm’)\G
***************************
1. row ***************************
id: 1
select_type:
SIMPLE
table:
film_text
type: fulltext
possible_keys:
idx_title_description
key:
idx_title_description
key_len: 0
ref:
rows: 1
Extra: Using
where
1 row in set
(0.00 sec)
3.1.4.
Rows
The rows field in an EXPLAIN plan is the approximate number of records
examined for this row. This number is based on metadata, and metadata may or
may not be accurate, depending on the storage engine. In addition, LIMIT is not
considered in this approximation:
mysql>
EXPLAIN SELECT first_name,last_name FROM member LIMIT 10\G
***************************
1. row ***************************
id: 1
select_type: SIMPLE
table: member
type: ALL
possible_keys:
NULL
key: NULL
key_len: NULL
ref: NULL
rows: 134
Extra:
1 row in set
(0.00 sec)
· If LIMIT were considered, rows would be 10.
·
The
more data in the database, the longer it will take to run a query. Even
optimized queries take longer to examine more data. One way to make your tables
smaller is by partitioning
·
Another
way to make the amount of data smaller is by purging data — after archiving to
a different table, different server, or backup.
·
Making
data smaller is often the key to a smoothly running database.
·
The
easiest way to make data smaller is to actually make the data types themselves
smaller.
·
Even
though VARCHAR values are variable in length, they convert to fixed-length
fields when they are stored in memory or in a memory-backed temporary table.
Therefore, it is useful to use a reasonable length for VARCHAR, instead of just
using VARCHAR(255) or VARCHAR(100) for everything.
·
Use
the PROCEDURE ANALYSE() statement to find the best value type and size for
existing data.
3.1.5.
Extra
The last field in the EXPLAIN plan is Extra. This is a catch-all field
that shows good, neutral, and bad information about a query plan. Table below
shows the most common Extra types, their meaning, and their ramifications:
3.1.6.
Subqueries and EXPLAIN
MySQL
handles subqueries very differently than it handles queries. The EXPLAIN plans show these
differences. The biggest difference is in the number of select_type values that are
used to describe subqueries. Table below shows the different select_type values used in
subqueries:
Derived
mysql> explain select userid from (select userid from list_members)a\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 97
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: list_members
type: index
possible_keys: NULL
key: FK_list_members_2
key_len: 8
ref: NULL
rows: 97
Extra: Using index
2 rows in set (0.00 sec)
mysql> explain select userid from (select userid from list_members)a\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: <derived2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 97
Extra:
*************************** 2. row ***************************
id: 2
select_type: DERIVED
table: list_members
type: index
possible_keys: NULL
key: FK_list_members_2
key_len: 8
ref: NULL
rows: 97
Extra: Using index
2 rows in set (0.00 sec)
Dependent Union
mysql> explain select * from list where listid in (select listid from list_members union s
elect listid from list_stats)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: list
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 19
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: list_members
type: ref
possible_keys: FK_list_members_1
key: FK_list_members_1
key_len: 3
ref: func
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 3
select_type: DEPENDENT UNION
table: list_stats
type: ref
possible_keys: FK_list_stats_1
key: FK_list_stats_1
key_len: 3
ref: func
rows: 1
Extra: Using index
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
4 rows in set (0.00 sec)
mysql> explain select * from list where listid in (select listid from list_members union s
elect listid from list_stats)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: list
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 19
Extra: Using where
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: list_members
type: ref
possible_keys: FK_list_members_1
key: FK_list_members_1
key_len: 3
ref: func
rows: 1
Extra: Using index
*************************** 3. row ***************************
id: 3
select_type: DEPENDENT UNION
table: list_stats
type: ref
possible_keys: FK_list_stats_1
key: FK_list_stats_1
key_len: 3
ref: func
rows: 1
Extra: Using index
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table: <union2,3>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
4 rows in set (0.00 sec)
Subquery
mysql> explain select list_name,(select listid from list_members where listid=10) listid f
rom list\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: list
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 19
Extra:
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: list_members
type: ref
possible_keys: FK_list_members_1
key: FK_list_members_1
key_len: 3
ref: const
rows: 3
Extra: Using index
2 rows in set (0.00 sec)
mysql> explain select list_name,(select listid from list_members where listid=10) listid f
rom list\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: list
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 19
Extra:
*************************** 2. row ***************************
id: 2
select_type: SUBQUERY
table: list_members
type: ref
possible_keys: FK_list_members_1
key: FK_list_members_1
key_len: 3
ref: const
rows: 3
Extra: Using index
2 rows in set (0.00 sec)
Dependent Subquery
mysql> explain select subid from subscription_new where serviceid in (select serviceid fro
m service_new where type_nr<5)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: subscription_new
type: index
possible_keys: NULL
key: FK_sid_1
key_len: 2
ref: NULL
rows: 8
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: service_new
type: unique_subquery
possible_keys: PRIMARY,FK_service_1
key: PRIMARY
key_len: 2
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
mysql> explain select subid from subscription_new where serviceid in (select serviceid fro
m service_new where type_nr<5)\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: subscription_new
type: index
possible_keys: NULL
key: FK_sid_1
key_len: 2
ref: NULL
rows: 8
Extra: Using where; Using index
*************************** 2. row ***************************
id: 2
select_type: DEPENDENT SUBQUERY
table: service_new
type: unique_subquery
possible_keys: PRIMARY,FK_service_1
key: PRIMARY
key_len: 2
ref: func
rows: 1
Extra: Using where
2 rows in set (0.00 sec)
Uncacheable Subquery
mysql> explain select adminid,first_name from admin union select adminid,(select @first_na
me from admin) from admin\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: admin
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra:
*************************** 2. row ***************************
id: 2
select_type: UNION
table: admin
type: index
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 8
Extra: Using index
*************************** 3. row ***************************
id: 3
select_type: UNCACHEABLE SUBQUERY
table: admin
type: index
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 8
Extra: Using index
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
4 rows in set (0.02 sec)
mysql> explain select adminid,first_name from admin union select adminid,(select @first_na
me from admin) from admin\G
*************************** 1. row ***************************
id: 1
select_type: PRIMARY
table: admin
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 8
Extra:
*************************** 2. row ***************************
id: 2
select_type: UNION
table: admin
type: index
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 8
Extra: Using index
*************************** 3. row ***************************
id: 3
select_type: UNCACHEABLE SUBQUERY
table: admin
type: index
possible_keys: NULL
key: PRIMARY
key_len: 2
ref: NULL
rows: 8
Extra: Using index
*************************** 4. row ***************************
id: NULL
select_type: UNION RESULT
table: <union1,2>
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: NULL
Extra:
4 rows in set (0.02 sec)
4.
MySQL Profiler
The
SQL Profiler is built into the database server and can be dynamically
enabled/disabled via the MySQL client utility. To begin profiling one or more
SQL queries, simply issue the following command:
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
Two
things happen once you issue this command. First, any query you issue from this
point on will be traced by the server with various performance diagnostics
being created and attached to each distinct query. Second, a memory table named
profiling is
created in the INFORMATION_SCHEMA database for your particular session (not
viewable by any other MySQL session) that stores all the SQL diagnostic
results. This table remains persistent until you disconnect from MySQL at which
point it is destroyed.
4.1.1.
Using the Profiler for Tuning Queries
Now, simply execute a SQL query:
mysql> select count(*) from admin;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set (0.00 sec)
Once the query completes, you can
issue the following command to view the SQL profiles that have currently been
stored for you:
mysql>
show profiles;
+----------+------------+----------------------------+
|
Query_ID | Duration | Query |
+----------+------------+----------------------------+
| 1 | 0.00078700 | select * from
admin |
| 2 | 0.00007500 | set profiling=1 |
| 3 | 0.00032900 | select count(*) from
admin |
+----------+------------+----------------------------+
3
rows in set (0.00 sec)
You
get a quick summary of all your captured SQL plus the total duration that the
query took to complete. To get the same diagnostic info, you can also query the
memory table that holds your statistical information:
mysql>
select sum(duration) from information_schema.profiling where query_id=3;
+---------------+
|
sum(duration) |
+---------------+
| 0.000329 |
+---------------+
1
row in set (0.00 sec)
The
most basic command is one that lists the steps a profiled query went through to
satisfy your SQL request, along with each step's time:
mysql>
show profile for query 3;
+--------------------------------+----------+
|
Status | Duration
|
+--------------------------------+----------+
|
starting | 0.000016 |
|
checking query cache for query | 0.000056 |
|
Opening tables | 0.000015
|
|
System lock | 0.000005
|
|
Table lock | 0.000026
|
|
init | 0.000015
|
|
optimizing | 0.000006
|
|
statistics | 0.000011
|
|
preparing | 0.000011
|
|
executing | 0.000005
|
|
Sending data | 0.000057
|
|
end | 0.000005 |
|
query end | 0.000004
|
|
freeing items | 0.000015
|
|
storing result in query cache | 0.000007
|
|
logging slow query | 0.000003
|
|
logging slow query | 0.000067 |
|
cleaning up | 0.000005
|
+--------------------------------+----------+
18
rows in set (0.00 sec)
Instead,
let's use a SQL query against the profiling table to see what our query did:
mysql>
select min(seq) seq,state,count(*) numb_ops,
-> round(sum(duration),5) sum_dur,
round(avg(duration),5) avg_dur,
-> round(sum(cpu_user),5) sum_cpu,
round(avg(cpu_user),5) avg_cpu
->
from information_schema.profiling
-> where query_id = 3
-> group by state
->
order by seq;
+------+--------------------------------+----------+---------+---------+---------+---------+
|
seq | state | numb_ops | sum_dur
| avg_dur | sum_cpu | avg_cpu |
+------+--------------------------------+----------+---------+---------+---------+---------+
| 1 | starting | 1 | 0.00002 | 0.00002 | 0.00000 |
0.00000 |
| 2 | checking query cache for query | 1 | 0.00006 | 0.00006 | 0.01000 |
0.01000 |
| 3 | Opening tables | 1 | 0.00002 | 0.00002 | 0.00000 |
0.00000 |
| 4 | System lock | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 5 | Table lock | 1 | 0.00003 | 0.00003 | 0.00000 | 0.00000
|
| 6 | init | 1 | 0.00002 | 0.00002 | 0.00000 |
0.00000 |
| 7 | optimizing | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 8 | statistics | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 9 | preparing | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 10 | executing | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 11 | Sending data |
1 | 0.00006 | 0.00006 | 0.00000 | 0.00000 |
| 12 | end | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
| 13 | query end | 1 | 0.00000 | 0.00000 | 0.00000 |
0.00000 |
| 14 | freeing items | 1 | 0.00002 | 0.00002 | 0.00000 |
0.00000 |
| 15 | storing result in query cache |
1 | 0.00001 | 0.00001 | 0.00000 | 0.00000 |
| 16 | logging slow query | 2 | 0.00007 | 0.00004 | 0.00000 |
0.00000 |
| 18 | cleaning up | 1 | 0.00001 | 0.00001 | 0.00000 |
0.00000 |
+------+--------------------------------+----------+---------+---------+---------+---------+
17
rows in set (0.00 sec)
mysql> show profile cpu for query 4;
+----------------------+------------+------------+------------+
| Status | Duration | CPU_user | CPU_system |
+----------------------+------------+------------+------------+
| (initialization) | 0.00002900 | 0.00000000 | 0.00000000 |
| checking permissions | 0.00000800 | 0.00000000 | 0.00000000 |
| init | 0.00004000 | 0.00000000 | 0.00000000 |
| Opening table | 0.00009400 | 0.00100000 | 0.00000000 |
| System lock | 0.00000500 | 0.00000000 | 0.00000000 |
| Table lock | 0.00000700 | 0.00000000 | 0.00000000 |
| setup | 0.00004200 | 0.00000000 | 0.00000000 |
| creating table | 0.00195800 | 0.00000000 | 0.00100000 |
| After create | 0.00010900 | 0.00000000 | 0.00000000 |
| copy to tmp table | 0.52264500 | 0.55591600 | 0.04199300 |
| rename result table | 0.11289400 | 0.00199900 | 0.00000000 |
| end | 0.00004600 | 0.00000000 | 0.00000000 |
| query end | 0.00000700 | 0.00000000 | 0.00000000 |
| freeing items | 0.00001300 | 0.00000000 | 0.00000000 |
+----------------------+------------+------------+------------+
14 rows in set (0.00 sec)
Other parameters that can be passed to
the
SHOW
PROFILE
command include:
·
ALL - displays all
information
·
BLOCK IO - displays
counts for block input and output operations
·
CONTEXT SWITCHES -
displays counts for voluntary and involuntary context switches
·
IPC - displays
counts for messages sent and received
·
MEMORY - is not
currently implemented
·
PAGE FAULTS - displays
counts for major and minor page faults
·
SOURCE - displays the
names of functions from the source code, together with the name and line number
of the file in which the function occurs
·
SWAPS - displays swap
counts
5.
Indexing Strategies
·
you should minimally use one index per table
·
however, don't index every column!!
–
indexes are more costly to update
·
always try and use indexes with high
·
selectivity
5.1.1.
Index selectivity
Selectivity of an
index - the ratio of the number of distinct values in the
indexed column(s) to the number of records. The ideal selectivity is one. Such
selectivity can be reached only by unique indexes on NOT NULL columns (UNIQUE
or PRIMARY KEY columns).
Query A select count(*) from admin;
mysql> select
count(*) from admin;
+----------+
| count(*) |
+----------+
| 9 |
+----------+
1 row in set
(0.00 sec)
Query B select count(distinct email) from admin;
mysql> select
count(distinct email) from admin;
+-----------------------+
| count(distinct
email) |
+-----------------------+
| 5 |
+-----------------------+
1 row in set
(0.00 sec)
Query B/Query A =
selectivity
9/5 = 0.5556=
selectivity
A higher selectivity (closer to one) is more
desirable. If selectivity is too low the query optimizer won't use it.
Finding Index
selectivity Percentage:
mysql> SELECT
t.TABLE_SCHEMA AS "DB", t.TABLE_NAME AS "TABLE", -> s.INDEX_NAME AS "INDEX NAME",
s.COLUMN_NAME AS "COLUMN
-> NAME",
s.SEQ_IN_INDEX "SEQ_IN_INDEX", s2.max_columns AS
->
"COLUMNS", s.CARDINALITY AS "CARDINALITY", t.TABLE_ROWS AS
-> "TOTAL ROWS", ROUND(((s.CARDINALITY / IFNULL(t.TABLE_ROWS,
-> 0.01)) * 100), 2) AS
"SELECTIVITY %"
-> FROM
-> INFORMATION_SCHEMA.STATISTICS s
-> INNER JOIN
-> INFORMATION_SCHEMA.tables t
-> ON
-> s.TABLE_SCHEMA = t.TABLE_SCHEMA
-> AND
-> s.TABLE_NAME = t.TABLE_NAME
-> INNER JOIN
-> (SELECT TABLE_SCHEMA, TABLE_NAME,
INDEX_NAME, MAX(SEQ_IN_INDEX) AS max_columns
-> FROM
-> INFORMATION_SCHEMA.STATISTICS
-> WHERE TABLE_SCHEMA = 'everest_test'
GROUP BY TABLE_SCHEMA, TABLE_NAME, INDEX_NAME) AS s2
-> ON
-> s.TABLE_SCHEMA = s2.TABLE_SCHEMA
-> AND
-> s.TABLE_NAME = s2.TABLE_NAME
-> AND
-> s.INDEX_NAME = s2.INDEX_NAME
-> WHERE t.TABLE_SCHEMA != 'mysql'
-> AND
-> t.TABLE_ROWS > 10
-> AND
-> s.CARDINALITY IS NOT NULL
-> AND
-> (s.CARDINALITY / IFNULL(t.TABLE_ROWS,
0.01)) < 1.00 ORDER BY "SELECTIVITY %",
-> s.TABLE_SCHEMA, s.TABLE_NAME LIMIT
10\G
***************************
1. row ***************************
DB: everest_test
TABLE: company_addon
INDEX NAME: FK_company_addon_1
COLUMN NAME: companyid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 25
TOTAL ROWS: 50
SELECTIVITY %:
50.00
***************************
2. row ***************************
DB: everest_test
TABLE: company_addon
INDEX NAME: FK_company_addon_2
COLUMN NAME: type_nr
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 10
TOTAL ROWS: 50
SELECTIVITY %:
20.00
***************************
3. row ***************************
DB: everest_test
TABLE: company_default_data
INDEX NAME: PRIMARY
COLUMN NAME: default_id
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 11
TOTAL ROWS: 16
SELECTIVITY %:
68.75
***************************
4. row ***************************
DB: everest_test
TABLE: company_standard_fields
INDEX NAME: FK_company_standard_fields_1
COLUMN NAME: companyid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 25
TOTAL ROWS: 50
SELECTIVITY %:
50.00
***************************
5. row ***************************
DB: everest_test
TABLE: custom_content
INDEX NAME: FK_custom_content_1
COLUMN NAME: catid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 29
TOTAL ROWS: 59
SELECTIVITY %:
49.15
***************************
6. row ***************************
DB: everest_test
TABLE: data_activity
INDEX NAME: FK_data_activity_1
COLUMN NAME: companyid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 20
TOTAL ROWS: 40
SELECTIVITY %:
50.00
***************************
7. row ***************************
DB: everest_test
TABLE: forward_form
INDEX NAME: PRIMARY
COLUMN NAME: forward_id
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 10
TOTAL ROWS: 19
SELECTIVITY %:
52.63
***************************
8. row ***************************
DB: everest_test
TABLE: forward_form
INDEX NAME: FK_forward_form_1
COLUMN NAME: companyid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 10
TOTAL ROWS: 19
SELECTIVITY %:
52.63
***************************
9. row ***************************
DB: everest_test
TABLE: list_members
INDEX NAME: FK_list_members_1
COLUMN NAME: listid
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 67
TOTAL ROWS: 134
SELECTIVITY %:
50.00
***************************
10. row ***************************
DB: everest_test
TABLE: member
INDEX NAME: status_idx
COLUMN NAME: status
SEQ_IN_INDEX: 1
COLUMNS: 1
CARDINALITY: 4
TOTAL ROWS: 109
SELECTIVITY %:
3.67
No comments:
Post a Comment