Checking the presence of records in the database: in one step or in two steps?
Let's say you want to retrieve a record from a database that returns a large amount of data and requires multiple joins.
So my question is, is it better to use a single query to check if the data exists and get the result if it exists? Or run a simpler query to check if the data exists, if the id entry exists, query again to get the result, knowing it exists.
Example:
3 tables a, b and ab (connection 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 there is a request above.
So I don't know if there is any reason to do the second one. Any ideas how this affects the performance of the DB, or does it matter at all?
a source to share
Usually the slowest part of the query is the filter, then it is indexed search, union, etc. If he needs to transfer a lot of data, the transfer also takes a lot of time. It will be twice as slow if you check for existence and then fetch the data. Just extract the data. If it is there, you will get it, and if not, you will not get anything.
JOIN
Faster and more flexible when retrieving from multiple tables .
a source to share
I usually prefer the "two requests" approach, where the results must be paginated or cached. For instance:
$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 request could return a potentially long list of IDs, it would be a rather waste of time to get data for all of them.
a source to share
Another note to keep in mind if you're worried about runtime. Avoid using '*' in the select statement. This may be a simple note, but it directly affects the execution time of the request. Update the select statement, use aliases, u'r pointers and select only the fields you want.
a source to share