Hi Peter,
Sorry for my late reply, I was not at my home computer and couldn't give you any 'usable' info...
> > it could be something about the
> "group_concat_max_len" attribute
> being to small ...
>Not a query you've posted.
[] Yes sorry, I was testing so many queries and started messing up stuff
> when we get a list of articles we do a
> search for a word inside one of 5 text
> columns that I need to put into SELECT
>No text columns in the DDL you posted. If there's a problem, let's see that query and the DDL it depends on.
[] I'll post it at the bottom of the post..
> SELECT query that has Title,subtitle,body,author,summary ...
> then the fetch takes 56 seconds every time
>Yes, Text/Blob fetch in volume is slower the bigger the text/blob volume. Why in the world would you be fetching many rows of text data? Search them on the server, >fetch just the bits that need to be returned.
[] I wrote that wrongly .... I never fetch all the rows ... we have a LIMIT at the end so only 10,20 or 30 rows get displayed at one time ...
> newly created indexes on new date columns
> are not used if I use BETWEEN ...
Can't replicate that, can you post a sql script with enough Inserts to allow replication of the effect.
[] will prepare this over the weekend...
Here is my query :
SELECT A.* ,E.fk_media,E.nrscanpages,E.dateOfArticle,E.createdate,E.ID,E.title,E.body,E.subtitle,E.author,E.summary
FROM
(SELECT CA.* from econtracts_articles AS CA
WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user='ukommiha') AND CA.articledate BETWEEN '2018.01.01' AND '2020.02.02') as A
JOIN earticles AS E ON A.fk_earticle = E.ID
WHERE (UPPER(CONCAT_WS(title,' ',subtitle,' ',body,' ',author,' ',summary)) LIKE '%drzava%' ) GROUP BY E.id ORDER BY E.dateOfArticle ASC LIMIT 0,10
For example this inner part :
SELECT CA.* from econtracts_articles AS CA
WHERE CA.fk_econtract IN (SELECT fk_contract FROM ecust_user_contract WHERE fk_cust_user='ukommiha') AND CA.articledate BETWEEN '2018.01.01' AND '2020.02.02'
this one took 0.031 sec ...to return 10 rows ....
the whole query took 32sec and fetch was 0.016sec....
Btw ... we plan to change the table from utf8 to utfmb4 over this weekend to see if this will help.
Thank you for helping.
Best regards,
Kris