[TYPO3-dev] Optimizing last 20 query

[R. van Twisk]

Hi Jhon,
>> if so... is it currently slow on your server??
>>     
>
> Yes, very slow with 5000 articles.
>   
This is strange, since 5000 should have been done in a snap
for any database.
>
>   
>> You could change the pages.pid IN (1000) to = pages.pid = 1000
>> and you could put a appropriate index.
>>     
>
> I've used EXPLAIN, but didn't find any ideas for indices.
>
>
>   
>> I am not sure what SQL_NO_CACHE does, but it seems like non ANSI SQL?
>>     
>
> It's just to avoid caching, usable for optimizing SQL statements.
>   
I understand....
>   
>> The above query will find all tt_content record directly below page 1000,
>> are you looking for a query that can also find all tt_content records on
>> sub-pages below page 1000 aswell??
>>     
>
> No, it will not find content under page 1000, but under subpages of 1000.
>
>   
Ok, I think we just wanted to have that clear :)



I am sure you did a microtime right before your select statement, and
right after it to measure the time... And just out of my curiosity,
how much time does it take?


What I would suggest is create a compound key of the fields
in your where clause.

Like Peter pointed out, boolean fields are hard to optimize,
however in a compound key they can make difference under
some conditions (some percentage needs to be set or unset).

However for now I would make a compound key for the
1) pages table on: crdate, pid
2) page table on : crdate
3) and a index on  tt_content on  : pid

(pages uid should already have a index since it's a pkey).

let me know if t hat helps for my own reference,
optimizing any DB is not easy...


Ries





-- 
Ries van Twisk
Freelance Typo3 Developer
=== Private:
email: ries at vantwisk.nl
web:   http://www.rvantwisk.nl/freelance-typo3.html
skype: callto://r.vantwisk
=== Work:
email: ries at livetravelguides.com
web:   http://www.livetravelguides.com