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.

Aditya Shah
  • 113
  • 7
  • 1
    SELECT SQL_CACHE * FROM customer; SELECT SQL_NO_CACHE * FROM customer; – Aditya Shah Sep 22 '18 at 07: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 07:30
  • 0.2ms is insignificant. Run them again; you might get a totally different difference. – Rick James Oct 09 '18 at 20:47

1 Answers1

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.

Andriy M
  • 22,983
  • 6
  • 59
  • 103
danblack
  • 7,719
  • 2
  • 10
  • 27