1

What is difference between SELECT * FROM table and SELECT * FROM table WHERE 1

Query 1:

 SELECT * FROM table
  1. Number of records : 103
  2. Load time : 0.0005 sec

Query 2:

 SELECT * FROM table WHERE 1
  1. Number of records : 103
  2. Load time : 0.0003 sec

Question 1 : What is the difference between these 2 queries, if it loads the same data.

Question 2 : Why there is these much time difference, if it loads the same data.

Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Note :

  • The difference is minor here because number of records are small (103),

but when number of records increases then time difference is also increase.

- So let's when we have 100000 number of records then we can see notable time difference.

|improve this question
  • 1
    SELECT SQL_CACHE * FROM customer; SELECT SQL_NO_CACHE * FROM customer; – Aditya Shah Sep 22 '18 at 7:01
  • 1
    by cache differences accounting for very minor time differences I was more thinking of innodb buffer pool loading (and most recent pages), key cache (if myisam), CPU cache, CPU branch predication effects etc. – danblack Sep 22 '18 at 7:30
  • 0.2ms is insignificant. Run them again; you might get a totally different difference. – Rick James Oct 9 '18 at 20:47
4

Question 1 : What is the difference between these 2 queries, if it loads the same data.

None.

Question 2 : Why there is these much time difference, if it loads the same data.

Within margin of error and your first query may have put them in a cache making it slightly faster.

Question 3 : Why phpmyadmin by default loads SELECT * FROM table WHERE 1 query ?

Because it's a bit special. There are many odd behaviors of what phpMyAdmin manipulates queries in a browser to before it gets to SQL. Programmatic convenience maybe.

Question 4 :What MySQL do for ABOVE queries in MySQL Query Caching?

Query caching is based on the literal text of the query, and some session variables. They would be cached differently. PS. Query cache is evil.

|improve this answer
  • What does exactly meaning of bit special of here context ? – Aditya Shah Sep 22 '18 at 6:35
  • 1
    And evil query cache before you ask :-) – danblack Sep 22 '18 at 6:39
  • @AdityaShah: I believe "it's a bit special" simply means "it has its own peculiarities (aka odd behaviours)". – Andriy M Sep 22 '18 at 8:56
  • Yes, did a few edits to clarify that colloquialism. Hopefully clear now. Thanks for the formatting. I'll take note of it. – danblack Sep 22 '18 at 8:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.