[TYPO3-v4] Improving backend search for 4.4 and 4.5

[Ernesto Baschny [cron IT]]

Dmitry Dulepov schrieb am 19.09.2011 13:09:

> Ernesto Baschny [cron IT] wrote:
>> Speed improvements are welcome in 4.5. If they of course don't break
>> backwards compatibility or change the user interface.
> 
> That's the point, they break it in a certain way. It needs explanation.
> 
> Before the patch TYPO3 did the following:
> - for each table
>     - add uid to search fields (even if you search for a string)
>     - for each field in the table
>         - if it is any text field, add it to search
>     - do "LIKE '%'" . $field . "%'"
> - limit pids to current pid + 4 sublevels, don't tell anyone about it
> 
> If you have 30 tables in the database you get a ton of LIKE statments
> connected by OR. The whole thing cannot use indexes. On a 10000 pages
> you get a really long time search.
> 
> Patch does this:
> - for each table
>     - add uid to search only if searching for numbers
>     - look if there is a 'searchField' in 'ctrl' of the $TCA for table
>         - if 'searchFields' exist
>             - for each field
>                 - add it to search
>     - do "'LIKE ' . $field . '%'"
> - limit to pid(s) only of the user said so explicitly in the List module
> 
> This runs much faster because it uses only fields, that are really
> searchable. Also it can uses indexes on text fields. But you see the
> drawback: no "searchFields" in the $TCA for a table means no search of
> that table. It is easy to fix but extensions have to make such fixes if
> their table should be searched. On the other hand it is also good
> because not every table should be searched (there are many tables
> without user-readable data).
> 
> So it can be seen as a breaking change.

To make it less intrusive, you could change the backported patch to only
look at "searchField" in "ctrl" if this key is defined, else go through
all text fields (as usual), so that at least the same content will be found.

For new extensions / tables where searchFields are defined, those will
be considered.

For old extensions without searchField, those would at least remain
searcheable.

For the core tables (tt_content / pages / etc), we could define the
"searchField" like it was done for 4.6.

As for the backporting procedure:

As discussed today in our release team meeting, we would love to see
this improvement also in 4.5 (as it is basically a scalability /
performance improvement), but would like to wait until at least 4.6.0 is
"out" and this feature has proven to work great. I know you have had
this patch working for some time now in your environment, but having a
bigger audience testing it will be nice.

For the mean-time, I propose that you proceed as you suggested: Post the
patch for 4.5 either in your sandbox (which is public and goes without
review system) or publish it as a review to the 4.5 branch, but abandon
the change right away (or -1 or -2 it). 2-3 months after 4.6.0 is out,
we re-visit it, maybe rebase the patch to the current 4.5 state and have
it included in 4.5.X.

We won't include it in 4.4 thou, so for these installations, please
consider providing the patch through some other means (through your git
sandbox for example).

Cheers,
Ernesto