2

I would like to know how an SQL query is parsed.

For Eg:

Select count(*) from Users where name = 'John' and age = '24'

Will it take the count of records with name 'John' first , and then from it filter according to Age? Or is it the other way round?

Chillax
  • 4,418
  • 21
  • 56
  • 91

3 Answers3

1

Run the Explain function on your query or better yet turn your query profiler on.

Luc
  • 985
  • 7
  • 10
  • It wil depend on the engine. MySQL, SQLite, SQL server etc. also can sometimes depend on how your database learns that table (optimising based on lookup and indexing). It's worth mentioning too, you probably should be more worried about Count(*). Id change that to count(1) – Luc Feb 28 '12 at 13:05
  • But isn't count(*) and count(1) same in terms of performance? – Chillax Feb 28 '12 at 13:12
  • Nope. Big performance hit. Well thats from my knowledge of – Luc Feb 28 '12 at 13:13
  • Nope. Big performance hit. Well thats from my knowledge of mysql – Luc Feb 28 '12 at 13:13
  • As per the below discussion, theres no difference. I wouldn't no for sure since Im relatively new to this http://stackoverflow.com/questions/1221559/count-vs-count1 – Chillax Feb 28 '12 at 13:26
  • Wow news to me. Thanks chillax. – Luc Feb 28 '12 at 13:34
0

Selection/filtering prioritisation is not dependant on the parser, but on the optimiser. The way this is done will be dependant on a number of issues, including database engine, indexes set up on the database tables, partitioning on the database tables, joins in the query to other tables, the selection conditions included within the query, etc.

If this is specific to Oracle, then "Cost-Based Oracle Fundamentals" by Jonathan Lewis may be helpful.

0

it will filter all of your record first based on where ( if you are having any group by it will do that ) and after that it will count it

Mukesh
  • 63
  • 1
  • 8