CODEWITHSUNDEEP
mysqlsql
Sasanka Sekhar
Sasanka Sekhar

Reputation: 33

Speed of SQL Queries

Suppose we have a students database with roll number, name and marks as the attributes. Out of the following two queries which one is fast:
SELECT roll_number FROM students
OR
SELECT name FROM students?

Upvotes: 0

Views: 4926

Answers (5)

Gordon Linoff
Gordon Linoff

Reputation: 1270503

The database processing time for the two queries should be basically the same. The query has to read all the rows and this will be a full table scan.

An index could help. If an index is on either column, then only the index needs to be read, rather than the data pages. That is a pretty small optimization.

However, fetching the data will depend on the size of the data in the column. And this could vary noticeably if, say, roll_number where an int and name were a very long string.

Upvotes: 0

bhuvnesh pattnaik
bhuvnesh pattnaik

Reputation: 1463

There is no condition, so both the queries will take approximately the same time

The column which is indexed will fetch data faster.

If none of your columns is indexed, as there is no condition in both of the queries then both queries will take same time depending on network connectivity speed to the database.

Upvotes: 2

Strawberry
Strawberry

Reputation: 33945

There would be no significant difference. You could easily test this for yourself. Equally, the presence or absence of an indexed on any given column would make no difference - except that the rows of an indexed column would, typically, be 'sorted' - although MySQL makes no guarantee in this regard:

DROP TABLE IF EXISTS x;

CREATE TABLE x
(indexed_int INT NOT NULL
,unindexed_int INT NOT NULL
,indexed_string VARCHAR(3) NOT NULL
,unindexed_string VARCHAR(3) NOT NULL
,INDEX(indexed_int)
,INDEX(indexed_string)
);

--Populate with roughly 100,000 rows of random data... 

INSERT INTO x 
SELECT RAND()*100000
     , RAND()*100000
     , 100+(RAND()*900)
     , 100+(RAND()*900);

INSERT INTO x SELECT RAND()*100000, RAND()*100000, 100+(RAND()*900), 100+(RAND()*900) FROM x;   

-- repeat line above a bunch of times. Then...

SELECT indexed_int FROM x;
131072 rows in set (0.20 sec)

SELECT unindexed_int FROM x;
131072 rows in set (0.22 sec)

SELECT indexed_string FROM x;
131072 rows in set (0.23 sec)

SELECT unindexed_string FROM x;
131072 rows in set (0.18 sec)

Upvotes: 0

Issa Khoury
Issa Khoury

Reputation: 44

You are effectively retrieving all values for that column in the table, no filters and no pagination. Both queries will be answered by a "full table scan", hence no query is evaluated more optimally. Otherwise if you are constraining the query with filters and pagination, then as mentioned above the indexed column will be retrieved faster.

Upvotes: 1

Brijesh
Brijesh

Reputation: 91

you can measure time of query execution like,

SET STATISTICS TIME ON;

SELECT roll_number FROM students; or SELECT name FROM students

Output:

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 1 ms.

(1 row(s) affected)

SQL Server Execution Times:

CPU time = 422 ms, elapsed time = 2296 ms.

Upvotes: 1

Related Questions