-2

I am working for one mysql db query performance improvement task and i faced below one query where the "where clause is 1=1".

my question is, what is different in term of performance for executing time of below two db query

  1. Select * from Table1 where 1=1

  2. Select * from Table1

nos of records : 159954 for table1 for one client

nos of records :2452798 for table1 for second client.

and in some cases, this table is joined with another table with same where clause 1=1.

can mysql query compiler optimize this query while execution ?

Version of MySQL Server 5.1

Community
  • 1
  • 1
javaamtho
  • 382
  • 3
  • 7
  • 20
  • I would suggest a simple time analysis. For example: http://stackoverflow.com/questions/4501876/how-to-get-the-execution-time-of-a-mysql-query-from-php – Thomas Jun 04 '12 at 12:41
  • tomas, without getting relative answer, how would i accept it ? if i accepted any answer, does it means not more response ? – javaamtho Jun 04 '12 at 12:53
  • @javaamtho http://meta.stackexchange.com/a/134070/175151 – DisplayName Jun 04 '12 at 12:54
  • possible duplicate of ["where 1=1" statement](http://stackoverflow.com/questions/8149142/where-1-1-statement) – George Stocker Jun 06 '12 at 00:30
  • @GeorgeStocker, it looks same but my concern here is about the performance impact my database. i do not work on dynamic query impl. as said how mysql optimiser work on "1=1".. – javaamtho Jun 07 '12 at 10:35

4 Answers4

1

Both are same as both will fetch all records and so no such big difference. Here 1 = 1 will always be true. 

1)Select * from Table1 where 1=1  

2)Select * from Table1

See this other posts on same topic

Importance of WHERE 1 in MySQL queries

"where 1=1" statement

Community
  • 1
  • 1
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • Rahul, thanks for quickly reply but in term of nos records are huge. would it make any different ? – javaamtho Jun 04 '12 at 12:44
  • That's what I mentioned `NO`; as both the queries are logically same. They will fetch all `N` records. – Rahul Jun 04 '12 at 12:45
  • ok :) , So whats about the "IN" operator with single value and "=" operator ? i have validated this and found little ms different in performance.. whats your opinion ? – javaamtho Jun 04 '12 at 12:47
  • I have never tested but `where col1 IN (val)` and `where col1 = val` will surely not make huge performance difference; since implicitelu your DB engine will flatten that `in(val1)` to a OR'ed stack. – Rahul Jun 04 '12 at 12:49
  • @javaamtho, consider accepting it as an answer if it answered your question. – Rahul Jun 04 '12 at 14:09
1

The two queries return the same set of rows, and they will have identical run time.

The reason 1=1 is often used is to simplify the process of producing computer-generated where clauses; optimizers of all non-toy RDBMS engines ignore this condition as true. Search around on Stack Overflow for explanations of how 1=1 helps with generated queries.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
0

Put explain before your query or turn on query profiler and you'll find out. I'd imagine 1=1 would be an extremely small overhead, barely noticeable.

Luc
  • 985
  • 7
  • 10
  • i have seen some milisecond impact on execution of my dbquery with where clause "1=1" and i suggest, it should ignore it if really not required. – javaamtho Jun 07 '12 at 10:39
0

For those that did computer architecture, I think we would all agree that there is less computing if you changed WHERE 1 = 1 to WHERE true. Less involved with the registers and no need for the compiler to token up 1 = 1 into smaller calculations.

So in the case where you are building up a query and for simplicity you need to have that first condition the query might look like this:

SELECT * FROM user WHERE true AND UserID = 1

Really we are talking very very small computations here. Unlikely to save you the amount of computations needed just from posting this stack overflow question.

Luc
  • 985
  • 7
  • 10