[TYPO3-dev] Remove records from extbase query result

[bernd wilke]

Am 15.01.14 10:39, schrieb Xavier Perseguers:
> Hi,
>
>>> For an actual *radius* I have to compute the distance between the
>>> location and each object. This can not be done as a query constraint. So
>>> I have to narrow down the result later.
>>>
>>
>> are you sure?
>> you may use your restrictions to a square area to get a first and easy
>> selection and afterwards (if SQL optimizes the query-selections) compute
>> the radius by only using squares which are less expensive than squareroots
>>
>> sqrt( dX^2 + dY^2) = r    <=>   dX^2 + dY^2 = r^2
>
> Dirk is right here, of course you may do it in pure SQL but it will lead
> to a full table scan whereas Dirk's solution with a two-step query (one
> in SQL, the other in PHP) leads to using the SQL indexes and is much
> more efficient as soon as you have many records.

of cause it will be a full table scan, he is doing it also.
but my question is: will SQL do a shortcut in the selection condition or 
will it always compute all parts of the condition on every record.

probably Dirk does a select in the kind of:

select * from ...
where x_min < x AND x < x_max AND y_min < y AND y < y_max

  x_min = target_x - radius
  x_max = target_x + radius
  y_min = target_y - radius
  y_max = target_y + radius

expanding (not replacing with the pure square-calculation) this query to

select * from ...
where x_min < x AND x < x_max AND y_min < y AND y < y_max
   AND (target_x - x)*(target_x - x) + (target_y - y)*(target_y - y) < 
radius_x_radius

  radius_x_radius = radius * radius

as a lot of records will not match the first conditions SQL does not 
need to check the last more expensive condition at all (condition 
shortcut as realized in most programming languages)

bernd
-- 





http://www.pi-phi.de/cheatsheet.html