def test_temporary(self):
reports = list(
check_queries_using_temporary(
self.connection, read_queries_from_log('0020-big-table-log')))
self.assertEqual(len(reports), 1)
self.assertEqual(
str(reports[0]),
'0020_big_table: "SELECT val, count(*) FROM 0020_big_table WHERE ite..." query used temporary'
)
self.assertEqual(
reports[0].context['query'],
'SELECT val, count(*) FROM 0020_big_table WHERE item_id BETWEEN 10 AND 20 GROUP BY val ORDER BY val'
)
self.assertEqual(reports[0].context['explain_extra'],
'Using where; Using temporary; Using filesort')
self.assertEqual(reports[0].context['explain_rows'], 11)
self.assertEqual(reports[0].context['explain_key'], 'PRIMARY')
def test_queries(self):
reports = list(
check_selects_with_like(
database=self.connection,
queries=read_queries_from_log('0027-selects-with-like-log')))
print(reports, reports[0].context)
self.assertEqual(len(reports), 1)
self.assertEqual(
str(reports[0]),
'0020_big_table: "SELECT * FROM 0020_big_table WHERE text LIKE \'%00\'" query uses LIKE with left-most wildcard'
)
self.assertEqual(reports[0].table_name, '0020_big_table')
self.assertEqual(
str(reports[0].context['query']),
'SELECT * FROM 0020_big_table WHERE text LIKE \'%00\'')
self.assertEqual(str(reports[0].context['explain_extra']),
'Using where')
self.assertTrue(reports[0].context['explain_rows'] > 10000)
def test_high_offset_selects(self):
reports = list(
check_high_offset_selects(
self.connection,
queries=read_queries_from_log('0118-high-offset-selects-log')))
print(reports, reports[0].context)
self.assertEqual(len(reports), 1)
self.assertEqual(
str(reports[0]),
'page: "SELECT /* CategoryPaginationViewer::processSection..." query uses too high offset impacting the performance'
)
self.assertEqual(reports[0].table_name, 'page')
self.assertEqual(
str(reports[0].context['query']),
"SELECT /* CategoryPaginationViewer::processSection */ page_namespace,page_title,page_len,page_is_redirect,cl_sortkey_prefix FROM `page` INNER JOIN `categorylinks` FORCE INDEX (cl_sortkey) ON ((cl_from = page_id)) WHERE cl_type = 'page' AND cl_to = 'Spotify/Song' ORDER BY cl_sortkey LIMIT 927600,200"
)
self.assertEqual(reports[0].context['limit'], 200)
self.assertEqual(reports[0].context['offset'], 927600)
def test_queries(self):
reports = list(check_queries_not_using_indices(
database=self.connection, queries=read_queries_from_log('0019-queries-not-using-indices-log')))
print(*[f"{report.message} ({report.context['explain_extra']})" for report in reports], sep="\n")
assert len(reports) == 3
self.assertEqual(str(reports[0]), '0019_queries_not_using_indices: "SELECT item_id FROM 0019_queries_not_using_indices..." query did not make use of any index')
self.assertEqual(reports[0].table_name, '0019_queries_not_using_indices')
self.assertEqual(str(reports[0].context['query']), 'SELECT item_id FROM 0019_queries_not_using_indices WHERE foo = "test" OR item_id > 1;')
self.assertEqual(str(reports[0].context['explain_extra']), 'Using where')
self.assertEqual(str(reports[0].context['explain_rows']), '3')
self.assertEqual(reports[1].table_name, '0019_queries_not_using_indices')
self.assertEqual(str(reports[1].context['query']), 'SELECT item_id FROM 0019_queries_not_using_indices WHERE foo = "test"')
self.assertEqual(str(reports[1].context['explain_extra']), 'Using where')
self.assertEqual(str(reports[1].context['explain_rows']), '3')
self.assertEqual(reports[2].table_name, '0019_queries_not_using_indices')
self.assertEqual(str(reports[2].context['query']), 'SELECT 1 AS one FROM dual WHERE exists ( SELECT item_id FROM 0019_queries_not_using_indices WHERE foo = "test" );')
self.assertEqual(str(reports[2].context['explain_extra']), 'Using where')
self.assertEqual(str(reports[2].context['explain_rows']), '3')
def test_full_table_scan(self):
reports = list(
check_full_table_scan(
self.connection,
read_queries_from_log('0026-full-table-scan-log')))
self.assertEqual(len(reports), 2)
self.assertEqual(
str(reports[0]),
'0020_big_table: "SELECT * FROM 0020_big_table" query triggered full table scan'
)
self.assertEqual(reports[0].context['query'],
'SELECT * FROM 0020_big_table')
self.assertTrue(reports[0].context['explain_rows'] > 8000)
self.assertEqual(
str(reports[1]),
'0020_big_table: "SELECT * FROM 0020_big_table LIMIT 5" query triggered full table scan'
)
self.assertEqual(reports[1].context['query'],
'SELECT * FROM 0020_big_table LIMIT 5')
self.assertTrue(reports[1].context['explain_rows'] > 8000)
def test_having_clause(self):
reports = list(
check_having_clause(
self.connection,
read_queries_from_log('0093-having-clause-log')))
print(list(map(str, reports)))
assert len(reports) == 3
assert str(
reports[0]
) == 'foo: "SELECT * FROM foo HAVING bar = 2" query uses HAVING clause'
assert reports[0].table_name == 'foo'
assert reports[0].context[
'query'] == 'SELECT * FROM foo HAVING bar = 2;'
assert str(reports[1]) == 'SH.sales: "SELECT s.cust_id,count(s.cust_id) ' \
'FROM SH.sales s ..." query uses HAVING clause'
assert reports[1].table_name == 'SH.sales'
assert str(reports[2]) == '0019_queries_not_using_indices: "SELECT * FROM ' \
'`0019_queries_not_using_indices` WHE..." query uses HAVING clause'
assert reports[2].table_name == '0019_queries_not_using_indices'
def queries(self):
return read_queries_from_log('0098-handle-sql-errors-log')