def test_explain_queries(self):
res = list(explain_queries(self.connection, self.queries))
tables_used = [item[1] for item in res]
print(res, tables_used)
assert '0020_big_table' in tables_used
def check_queries_not_using_indices(database, queries):
"""
:type database indexdigest.database.Database
:type queries list[str]
:rtype: list[LinterEntry]
"""
for (query, table_used, index_used,
explain_row) in explain_queries(database, queries):
# print(query, explain_row)
# EXPLAIN can return no matching row in const table in Extra column.
# Do not consider this query as not using an index. -- see #44
if explain_row['Extra'] in [
'Impossible WHERE noticed after reading const tables',
'no matching row in const table', 'No tables used'
]:
continue
if index_used is None:
context = OrderedDict()
context['query'] = query
# https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
context['explain_extra'] = explain_row['Extra']
context['explain_rows'] = explain_row['rows']
context['explain_filtered'] = explain_row.get(
'filtered') # can be not set
context['explain_possible_keys'] = explain_row['possible_keys']
yield LinterEntry(
linter_type='queries_not_using_index',
table_name=table_used,
message='"{}" query did not make use of any index'.format(
shorten_query(query)),
context=context)
def check_full_table_scan(database, queries):
"""
Full table scan
An operation that requires reading the entire contents of a table, rather than just selected
portions using an index. Typically performed either with small lookup tables, or in data
warehousing situations with large tables where all available data is aggregated and analyzed.
How frequently these operations occur, and the sizes of the tables relative to available memory,
have implications for the algorithms used in query optimization and managing the buffer pool.
:type database indexdigest.database.Database
:type queries list[str]
:rtype: list[LinterEntry]
"""
for (query, table_used, _, row) in explain_queries(database, queries):
# The output from EXPLAIN shows ALL in the type column when
# MySQL uses a full table scan to resolve a query.
if row['type'] != 'ALL':
continue
context = OrderedDict()
context['query'] = query
context['explain_rows'] = int(row['rows']) # we get string here when using MariaDB 10.5
yield LinterEntry(linter_type='queries_using_full_table_scan', table_name=table_used,
message='"{}" query triggered full table scan'.
format(shorten_query(query)),
context=context)
def check_not_used_indices(database, queries):
"""
:type database indexdigest.database.Database
:type queries list[str]
:rtype: list[LinterEntry]
"""
logger = logging.getLogger(__name__)
used_indices = defaultdict(list)
# EXPLAIN each query
for (query, table_used, index_used,
_) in explain_queries(database, queries):
if index_used is not None:
logger.info("Query <%s> uses %s index on `%s` table", query,
index_used, table_used)
used_indices[table_used].append(index_used)
# analyze all tables used by the above queries
# print(used_indices)
for table_name, table_indices in used_indices.items():
for index in database.get_table_indices(table_name):
if index.name not in table_indices:
yield LinterEntry(
linter_type='not_used_indices',
table_name=table_name,
message='"{}" index was not used by provided queries'.
format(index.name),
context={"not_used_index": str(index)})
def filter_explain_extra(database, queries, check):
"""
Parse "Extra" column from EXPLAIN query results, e.g.
"Using where; Using temporary; Using filesort"
:type database indexdigest.database.Database
:type queries list[str]
:type check str
:rtype: list
"""
for (query, table_used, _,
explain_row) in explain_queries(database, queries):
extra_parsed = str(explain_row['Extra']).split('; ')
if check in extra_parsed:
context = OrderedDict()
context['query'] = query
context['explain_extra'] = explain_row['Extra']
context['explain_rows'] = int(
explain_row['rows']) # string when using MariaDB 10.5
context['explain_filtered'] = explain_row.get(
'filtered') # can be not set
context['explain_key'] = explain_row['key']
yield (query, table_used, context)
def check_selects_with_like(database, queries):
"""
:type database indexdigest.database.Database
:type queries list[str]
:rtype: list[LinterEntry]
"""
for (query, table_used, index_used, explain_row) in explain_queries(database, queries):
if index_used is None and query_uses_leftmost_like(query):
context = OrderedDict()
context['query'] = query
# https://dev.mysql.com/doc/refman/5.7/en/explain-output.html#explain-extra-information
context['explain_extra'] = explain_row['Extra']
context['explain_rows'] = explain_row['rows']
yield LinterEntry(linter_type='selects_with_like', table_name=table_used,
message='"{}" query uses LIKE with left-most wildcard'.
format(shorten_query(query)),
context=context)
