Mysql Explain and Limit Clause

Be careful when using the explain command in mysql against a statement using the limit clause. When using explain it give the appearance that the whole table is scanned, before the limit number is extracted. For example I’ve created a table with more data off the world.city table which contains 261056 rows. If I want to select with a limit a 10 rather then showing 10 rows in the plan it show’s it as if it will run a total scan of the table, then extract the 10.


mysql> explain extended select * from citybig limit 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: citybig
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 261056
filtered: 100.00
Extra:
1 row in set, 1 warning (0.00 sec)

So how do you know mysql is actually only evaluating 10 rows and not scanning the entire table? This can be determined using the slow_log variable. By setting the slow query time to 0 (This can only be done in 5.1 and above) it allows you to capture all queries being executed. By logging to a table (Again This can only be done in 5.1 and above) you can easily see how many rows were examined.

For the query above I got the following results.

*************************** 21. row ***************************
start_time: 2008-11-19 22:06:34
user_host: kenk[kenk] @ localhost [127.0.0.1]
query_time: 00:00:00
lock_time: 00:00:00
rows_sent: 10
rows_examined: 10
db: world
last_insert_id: 0
insert_id: 0
server_id: 0
sql_text: select * from citybig limit 10
21 rows in set (0.00 sec)

Finding All Tables with specific Column Names in SQL Server

Recently I got a request from our test department to find all tables that did not contain a specific column name. For example if there are three tables defined with the following fields and you only want to return the name of the table without the field PKint in this case table2.

Create table table1
(pkint int,
Charfield varchar(10))

Create table table2
(fkint int.
Charfiedl varchar(10))

Create table table3
(pkint int,
Charfield2 varchar(10))


The following query leverages DMV’s introduced in 2005 to return the results.

select t.name from sys.tables t
where t.name not in(
select t.name from sys.tables t
inner join sys.columns c
on
c.object_id = t.object_id
where c.name = 'pkint')


If you want to go in the reverse direction that is finding all tables with the name pkint you simply change the “not in” to “in”

select t.name from sys.tables t
where t.name in(
select t.name from sys.tables t
inner join sys.columns c
on
c.object_id = t.object_id
where c.name = 'pkint')