[TYPO3-dev] strange enableFields SQL with T3 4.0

[Martin Schoenbeck]

Hi Martin,

Martin Kutschker schrieb:

> Martin Schoenbeck schrieb:
>> 
>>>If so, normalizing won't speed things up. As 
>>>you're doing a join even more records are read.
>> 
>> Of course it would. You don't have to read any data to find the records for
>> a join if you have prepared usable indexes.  But you can't prepare an index
>> for the current situation.
> 
> So your statement is true for specific conditions. That's not bad as such. 
> But IMHO you should specify the conditions that are necessary to gain a 
> performance boost for something.

Sorry, but creating the necessary indexes is no specific condition when
using a database. 

> Summing up: to redeuce the number of disk reads for whole records the 
> complete where clause of the query must be satisfied by an index.

No. That summary is wrong. If for example one of the used tables is very
small there will be no need for an index on this table. But of course
you'll have to prepare indexes to avoid that queries fill memory with tons
of data which are read only to determine they are not what you want. At
least if you want your system to perform.

> This is IMHO not easy for the average query. But it can be set up for 
> specific key queries of the application.

Because your assumption is false this needn't to be true, to get a
performance benefit.

Anyway: after thinking a little about it I don't believe the proposed where
clause will be more efficient than the old one even if the whole database
fits into memory.

For the new idea you have to
1) allocate memory for the concat
2) copy three strings to that buffer
3) search this buffer for the pattern ',-2,'
on the other hand you have
1) search the original field (two characters shorter as in 3) above
2) do three compares on two or three characters.

It'll depend very much on the implementation which one will perform better,
so without measuring you'll not be able to tell which one. But anyway it'll
be neglectable as long as you don't have a database completely in memory.

Therefore as long as none measure a performance benefit in real life, I'ld
never change this query into a form which is sort of cryptic.

Martin