[TYPO3-dev] Remove records from extbase query result

[Stephan Schuler]

Hey there.


I think we don't need discuss MySQL query optimization here. That's something very specific and needs to be reviewed case by case. Just by looking at what I throw against the repository very often doesn't even slightly touch the way MySQL thinks it needs to be optimized. So I completely refuse trying to optimize MySQL behavior without my debugger and the "EXPLAIN" statement. Maybe you're better on that topic, but I for my part am not.
So I'll stick to "from an SQL perspective it's possible, but I don't want to tell anything about performance here".

But using pure SQL doesn't completely solve the problem described.
Try to express this situation with QOM. If it's even possible, you will most likely end up having something that is completely different from what you think that MySQL can optimize sufficiently.

If you're going to do it with a Statement instead of QOM, you're exactly where we are currently: It doesn't play nice with any kind of query post modification through TYPO3 interns, like pagination or limit or reordering or whatever.

So even if you do the whole select thing with an SQL statement, you still need to do a second query against the database that fetches records "WHERE uid IN()" to gain a processable query result.

Maybe someone is willing to implement both (two-step PHP based and pure SQL based) and do performance tests? Most likely I never will, because all of my projects that tend to have those kind of problems usually involve Solr. And there is distance search already :).


Regards,



Stephan Schuler
Web-Entwickler

Telefon: +49 (911) 539909 - 0
E-Mail: Stephan.Schuler at netlogix.de
Website: media.netlogix.de



--
netlogix GmbH & Co. KG
IT-Services | IT-Training | Media
Neuwieder Straße 10 | 90411 Nürnberg
Telefon: +49 (911) 539909 - 0 | Fax: +49 (911) 539909 - 99
E-Mail: info at netlogix.de | Internet: http://www.netlogix.de

netlogix GmbH & Co. KG ist eingetragen am Amtsgericht Nürnberg (HRA 13338)
Persönlich haftende Gesellschafterin: netlogix Verwaltungs GmbH (HRB 20634)
Umsatzsteuer-Identifikationsnummer: DE 233472254
Geschäftsführer: Stefan Buchta, Matthias Schmidt



-----Ursprüngliche Nachricht-----
Von: typo3-dev-bounces at lists.typo3.org [mailto:typo3-dev-bounces at lists.typo3.org] Im Auftrag von bernd wilke
Gesendet: Mittwoch, 15. Januar 2014 11:32
An: typo3-dev at lists.typo3.org
Betreff: Re: [TYPO3-dev] Remove records from extbase query result

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
_______________________________________________
TYPO3-dev mailing list
TYPO3-dev at lists.typo3.org
http://lists.typo3.org/cgi-bin/mailman/listinfo/typo3-dev