CODEWITHSUNDEEP
phpmysqldatabase
Sinan
Sinan

Reputation: 5980

Checking if records exist in DB: in single step or two steps?

Suppose you want to get a record from database which returns a large amount of data and requires multiple joins.

So my question would be, is it better to use a single query to check if data exists and get the result if it exists? Or do a more simple query to check if data exists then id record exists, query once again to get the result knowing that it exists.

Example:

3 tables a, b and ab(junction table)

select * from 
from a, b, ab 
where condition
and condition 
and condition 
and condition etc...

or 

select id 
from a, b ab 
where condition

then if exists do the query above.

So I don't know if there is any reason to do the second. Any ideas how this affects DB performance or does it matter at all?

Upvotes: 2

Views: 536

Answers (3)

user187291
user187291

Reputation: 53940

I usually prefer "two queries" approach when results must be paginated or cached. For example:

$found_ids = "select id from products where ...complex $condition...."

maybe add to cache ($condition => $found_ids)

$ids_to_show = array_slice($found_ids, $_GET['page'], $pagesize);

$data = "select * from products, more tables ... where products.id IN($ids_to_show)";

Since the first query can return a potentially long list of ids, it would be quite a waste of time to fetch data for all of them.

Upvotes: 1

Hanseh
Hanseh

Reputation: 717

Another note that should be considered if you are concern with the runtime. Avoid using '*' at the select statement. It may be a simple note but it directly affects the runtime of the query. Update the select statement, use aliases, index u'r keys and select only needed fields.

Upvotes: 0

Alex
Alex

Reputation: 14618

Usually the slowest part in a query is the filter, then it's the indexed search, joins, etc. If it's a lot of data to be transfered - the transfer is also time-consuming. It will be twice slower if you check the existence then extract the data. Just extract the data. If it is there you get it, and if not - you get nothing.

When extracting from multiple tables, JOIN is faster and more flexible.

Upvotes: 3

Related Questions