[TYPO3-english] Slow Queries From TT-News

[Louis Coppola]

Hey Typo3 Friends - I need some help.

We have a large tt_news database (500,000+) records.  On certain pages
that contain 4 or more tt_news list content elements, this is causing
a long time for the page to generate (sometimes more then 30 seconds)
and is unacceptable.

After looking through queries to see what might be take so long we
found several queries that are similar to the one below that were
causing a long load time.  We believe that these queries are causing
the database to be unable to use an index and forced to scan through
every row because of several "wildcards" in the query.  Has anyone had
similar problems with a large tt_news database and have they been able
to fix this problem and speed loading times?

Here is an example of one of the queries:

SELECT DISTINCT tt_news.uid, tt_news.*
                       FROM tt_news LEFT OUTER JOIN tt_news_cat_mm ON
tt_news.uid = tt_news_cat_mm.uid_local
                       WHERE
                               1=1  AND (tt_news_cat_mm.uid_foreign IN
(632,635,633)) AND tt_news.sys_language_uid IN (0,-1) AND tt_news.pid >
0 AND tt_news.pid IN
(835,2905,1610,1585,1555,1392,1390,1290,1223,844,1434,971,1013,978,985,842,840,839,838,845,1195,1486,2064,
2063,2062,2061,1799,993,1800,2563,1802,3426,1840,3381,3262,3261,3254,3250,3248,3246,3236,3235,3392,3228,3224,
3220,3592,3216,3212,3199,3198,3197,3196,3195,3191,3190,3189,3170,3103,3102,3101,3100,3087,3086,3079,3075,3071,
2986,2985,2984,2983,2982,2981,2909,2856,2855,2854,2840,2841,2797,2796,2795,2794,2793,2792,2791,2790,2718,2717,
2716,2715,2714,2627,2626,2625,2624,2623,2622,2621,2620,2619,2591,2587,2453,2452,2451,2450,2449,2448,2447,2446,
2445,2416,2415,2414,2413,2412,2411,2392,2387,2002,1856,1861,3587,1860,1859,1858,1857,1916,1915,1914,1913,1912,
1911,1910,1909,1908,1917,1923,1922,1921,1920,1919,1918,1950,1949,1948,1947,1946,1945,1944,1943,1942,1953,1952,
1951,2060,2059,2058,2057,2056,2055,2054,2053,2052,3541,3555,3631,3692,3700,3710,3720,3726,3741,3740,3739,3760,
3761,3762,3763,3786,3793,3798,3803,3809,3814,3819,3888,3894,3903,3792,3923,3924,3940,4115,4121,4143,4150,4157,
4163,4169,4174,4179,4185,4190,4184,4199,4263,4272,4280,4290,4302,4308,4433,4442,4451,4457,4463,4470,4476,4484,
4491,4499,4507,4515,2184,3280,3285,3295,3296,3303,3304,3311,3312,3316,3272,3271,2880,2879,2878,2877,2876,2815,
2814,2813,2812,2811,2810,2809,2657,2656,2655,2654,2351,2350,2349,2348,2347,2346,2345,2373,2303,2282,2281,2280,
2268,2264,2260,2253,2245,2244,2185,2197,2196,2195,2194,2193,2192,2191,2190,2189,2243,3547,4127,4131,4135,2492,
2493,3617,3040,3624,3613,3039,3038,3037,3036,2860,3602,3601,2736,2735,2671,2670,2669,2532,2531,2530,2529,3600,
2528,2527,2526,2525,4216,4222,4227,4235,4240,4246,4342,4346,4351,4357,4362,4367,4372,4377,4382,4387,4392,4397,
4402,4407,4412,4417,4422,4427,3476,4208,3477,3484,3488,3489,3497,3498,3643,3499,3500,3501,3502,3503,3648,3577,
3637,3825,4253,4258,2322,3346,2910,2381,2323,2362,2544,2376,2377,1999,2560,2304,1841,1862,2553,2555,34)
AND tt_news.deleted=0 AND tt_news.t3ver_state<=0 AND tt_news.hidden=0
AND (tt_news.starttime<=1315613160) AND (tt_news.endtime=0 OR
tt_news.endtime>1315613160) AND (tt_news.fe_group='' OR tt_news.fe_group
IS NULL OR tt_news.fe_group='0' OR (tt_news.fe_group LIKE '%,0,%' OR
tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR
tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR
tt_news.fe_group LIKE '-1,%' OR tt_news.fe_group LIKE '%,-1' OR
tt_news.fe_group='-1'))
                       ORDER BY datetime DESC
                       LIMIT 35;

SELECT *
                       FROM pages
                       WHERE
                               uid=2102 AND pages.deleted=0 AND
pages.hidden=0 AND (pages.starttime<=1315613400) AND (pages.endtime=0 OR
pages.endtime>1315613400)  AND NOT (pages.t3ver_state>0) AND
pages.doktype<200 AND (pages.fe_group='' OR pages.fe_group IS NULL OR
pages.fe_group='0' OR (pages.fe_group LIKE '%,0,%' OR pages.fe_group
LIKE '0,%' OR pages.fe_group LIKE '%,0' OR pages.fe_group='0') OR
(pages.fe_group LIKE '%,-1,%' OR pages.fe_group LIKE '-1,%' OR
pages.fe_group LIKE '%,-1' OR pages.fe_group='-1'));

SELECT tt_news.*
                       FROM tt_news LEFT OUTER JOIN tt_news_cat_mm ON
tt_news.uid = tt_news_cat_mm.uid_local
                       WHERE
                               1=1 AND tt_news.sys_language_uid IN
(0,-1) AND (IFNULL(tt_news_cat_mm.uid_foreign,0) IN (268)) AND
tt_news.deleted=0 AND tt_news.t3ver_state!=1 AND tt_news.hidden=0 AND
(tt_news.starttime<=1315613459) AND (tt_news.endtime=0 OR
tt_news.endtime>1315613459) AND (tt_news.fe_group='' OR tt_news.fe_group
IS NULL OR tt_news.fe_group='0' OR (tt_news.fe_group LIKE '%,0,%' OR
tt_news.fe_group LIKE '0,%' OR tt_news.fe_group LIKE '%,0' OR
tt_news.fe_group='0') OR (tt_news.fe_group LIKE '%,-1,%' OR
tt_news.fe_group LIKE '-1,%' OR tt_news.fe_group LIKE '%,-1' OR
tt_news.fe_group='-1'));


All of these queries share the problem of multiple un-indexable "LIKE
'wildcard'" clauses.


Thanks in advance for any help that can be given.

Louis Coppola