I'm developing a system, which in some parts, when I'm going to display the data to the client, I'm using in my query the group_concat since I need to display for an item the items related to it on the same line. This worked very well in that it had few records (2000). Now that I have been doing an inclusion test of many records (200000) then it takes an eternity to display the data. Does anyone have any idea what I can do to improve this?
I created an example of what I'm talking about
I made a test here and saw that the query is doing select_full_join, that is, giving this select in the entire table to generate this query, what I did is the execution plan that Mr Jorge Campos is referring to.
Possible solutions: 1 - Write your query so as not to do full scan, how? change those left join.
select
a.id AS aspecto_id,
a.name,
group_concat(ap.name order by ap.name ASC separator '||') AS aplicacao
from aspecto a, aspecto_aplicacao aa, aplicacao ap
where
aa.aspecto_id = a.id and
ap.id = aa.aplicacao_id
group by a.id , a.name
order by a.name;
But this will fetch complete results, without any null, if you need the results null just a stir in the query.
Possible solution 2: Doing a stored procedure for this query will result in 99.9% gain in queries, but of course, if you do the full scan, maybe this will only remedy by the hour, and it will cost you memory and processing to continue with a good performance.
Conclusion, I suggest you make the necessary adjustments to the query, and if it is still slow, send it to the procedure so you will be using both solutions, but I say focus on the way you write the conditions, always use pk, fk or indexes, and how is doing sort by "name" can do an index in this attribute and also to gain in performance. (index type fulltext)