CODEWITHSUNDEEP
mysqlsqlpdo
Toleo
Toleo

Reputation: 774

Which is Faster? Combined or Multiple Queries?

Is there any different between these queries in matter of Performance in a table with 1 million record?

id IS PRIMARY

userid IS INDEXED

name IS UNIQUE

Combined Queries:

$Query = 'SELECT name, (SELECT EXISTS (SELECT id FROM reports WHERE userid = 1)) AS status 
FROM users 
WHERE id = 1 
LIMIT 1';

$stmt->prepare($Query);
$stmt->execute();
$row = $stmt->fetch();

$name = $row['name'];
$status = $row['status'];

Multiple Queries:

1

$Query = 'SELECT name
FROM users 
WHERE id = 1 
LIMIT 1';

$stmt->prepare($Query);
$stmt->execute();
$row = $stmt->fetch();

$name = $row['name'];

2

$Query = 'SELECT EXISTS (SELECT id FROM reports WHERE userid = 1)';
$stmt->prepare($Query);
$stmt->execute();
$status = $stmt->fetchColumn();

Upvotes: 0

Views: 106

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271131

In the second version, MySQL has to compile, parse, and execute two queries. In the first, only one query has to be compiled, parsed, and executed.

Stop there. Which do you think is faster? And by about how much?

As with all performance questions, you can try it out yourself and see which is faster.

The overhead of running such simple queries can be quite important. If you care about performance, you want an index on users(id, name) and on reports(userid). The first query should then be faster because of the overhead of running queries.

Upvotes: 2

Related Questions