CODEWITHSUNDEEP
sql
Lin Yu Cheng
Lin Yu Cheng

Reputation: 667

How to measure that how many calculation will be made in a SQL select operation?

Assume that there is a user_profile table with 10000 records and half of records is female user ( = 5000 female users ).

If I send a query " select age(birth) from user_profile where sex = 'female' "

how many times will age function be performed? 5000 or 10000 times ?

Upvotes: 0

Views: 33

Answers (2)

Ke Zhu
Ke Zhu

Reputation: 225

Depending on the implementation of databases. Remember SQL is an ANSI standard not a product. The standard only specifies the the input (your sql statements) and output.

For most databases it would be 5000 times as it would be dumb to waste calculation power on unwanted rows.

It does selections first (where clause), then the projection (your select clause).

Upvotes: 1

Praveen Lobo
Praveen Lobo

Reputation: 7187

5000 times. WHERE clause eliminates all those that don't match 'female' and on the remaining result, the function will be called.

Order of execution will be

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

As you can see half of the results will be eliminated in WHERE and you will have 5000 records when SELECT is done.

Upvotes: 1

Related Questions