CODEWITHSUNDEEP
phpmysql
Devner
Devner

Reputation: 7235

php & mySQL: Which of the following query is faster? Why?

  1. Which of the following 3 Queries will be faster? Why?
  2. I use such queries with a lot of other joins a lot within my app. so is there any way I can benchmark their speed? If yes, can you please mention what it is/they are?

Query 1:

$q = "SELECT COUNT(books.id) FROM books 
INNER JOIN books_type ON books.id = books_type.id
WHERE books_type.book_type = 'Comedy'";

Query 2:

$q = "SELECT COUNT(*) FROM books 
INNER JOIN books_type ON books.id = books_type.id
WHERE books_type.book_type = 'Comedy'";

Query 3:

$q = "SELECT books.id FROM books 
INNER JOIN books_type ON books.id = books_type.id
WHERE books_type.book_type = 'Comedy'";

$books_count = mysql_num_rows($q);

Thank you.

Upvotes: 0

Views: 158

Answers (2)

raveren
raveren

Reputation: 18523

Even though you can easily test it yourself, here's an article that goes into the why's. According to it, the second one should be fastest.

Upvotes: 1

mga
mga

Reputation: 1970

You can try EXPLAIN query_here to find out.

For example:

EXPLAIN SELECT books.id FROM books 
INNER JOIN books_type ON books.id = books_type.id
WHERE books_type = 'Comedy'

This will give you some information on each query and how they perform. More information in the MySQL manual for the EXPLAIN statement:

"When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order"

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

I also recommend this tutorial for optimizing MySQL queries in Database Journal:

http://www.databasejournal.com/features/mysql/article.php/1382791/Optimizing-MySQL-Queries-and-Indexes.htm

Upvotes: 2

Related Questions