Benchmarking – SQL with and without index

Everybody likes when processes are running at optimal performance. SQL queries is a typical example where the answer of the two equal queries can be delivered with different speed.
Indexes on a table can have a large impact on the performance of a SQL query. I’ve done some benchmark tests running the same query on a table with and without a index.

The table structure: TestIndex (rowId PK int, productId int not null, countryId int not null). The table contains of 100000 rows.

The SQL query:
set statistics time on;
select productId, count(productId) name, min(countryId)
from testindex T2
where productId > 29500000 and productId < 30000000 AND productId IN
(
select productId from testindex
group by productId having count(productId) < 2
)
AND
productId IN
(
select productId from testindex
group by productId having count(productId) < 3
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
AND
productId IN
(
select productId from testindex where countryId < 100
group by productId having count(productId) > 0
)
group by productid, name, countryId
order by productId desc
set statistics time off;

The index was created like this:
CREATE NONCLUSTERED INDEX testIndexOne
ON testindex (productId)

Benchmark results

CPU time no index CPU time with index Elapsed time no index Elapsed time with index
94 0 7006 60
15 0 6267 30
31 0 6000 61
47 0 6218 32
235 0 6276 31

As you can see in the benchmark results above there is a huge different in both CPU- and elapsed time for running the exactly the same query. The only difference was an added index on one of the columns in the table that was queried.

View current sql queries on MSSQL database

To view current sql queries running on sql database you can use this sql script to get current query and elapsed time.

SELECT sqltext.TEXT,
req.session_id,
req.status,
req.command,
req.cpu_time,
req.total_elapsed_time
FROM sys.dm_exec_requests req
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext

I have only tested the script Microsoft SQL Server 2012 and therefore I’m not sure if it’s working on MySQL.