I need assistance with a MySQL query that’s running very slowly. I’m trying to speed it up, but I’m not quite sure how. Here’s the query I’m currently using:
SELECT
d.idcartera,
CONCAT(d.cedula, ':', planilla.fuerza, ':', planilla.cuotas, ':', d.libranza, ':', planilla.promotor) AS ClienteCedula,
CONCAT(d.nombres, ':', planilla.fuerza, ':', planilla.cuotas, ':', d.libranza, ':', planilla.promotor) AS ClienteNombre,
d.valor,
d.tipo,
CONCAT(d.ano, '/', d.mes, '-', LEFT(d.tipo, 3)) AS anoMes,
planilla.fuerza,
planilla.cuotas,
planilla.promotor
FROM cartera AS d
INNER JOIN cartera AS x ON d.cedula = x.cedula
INNER JOIN cartera AS y ON d.cedula = y.cedula
INNER JOIN planilla ON d.libranza = planilla.libranza
WHERE (d.tipo NOT LIKE '%RI-%') AND (LEFT(x.tipo, 3) LIKE 'RC-') AND (LEFT(y.tipo, 3) LIKE 'RCN')
GROUP BY d.idcartera, d.cedula
The join with the ‘y’ table is meant to filter out some data. If anyone has tips on optimizing this query, it would be greatly appreciated!
Your performance issue is coming from those multiple self-joins on the cartera table. Don’t join cartera three times - use EXISTS subqueries instead. They’re way more efficient.
Replace your x and y table joins with: WHERE d.tipo NOT LIKE '%RI-%' AND EXISTS (SELECT 1 FROM cartera x WHERE x.cedula = d.cedula AND LEFT(x.tipo, 3) = 'RC-') AND EXISTS (SELECT 1 FROM cartera y WHERE y.cedula = d.cedula AND LEFT(y.tipo, 3) = 'RCN').
Make sure you’ve got indexes on cedula and tipo - especially a composite index on (cedula, tipo). Those LEFT function calls are expensive too. If you can, normalize your tipo field or add a computed column with the prefix.
These changes should cut your execution time significantly since you’re not multiplying data unnecessarily with joins.
Your LEFT() functions in the WHERE clause are killing performance. When you wrap columns in functions, MySQL can’t use indexes - even perfect ones on cedula and tipo. Hit this same wall last year with a slow reporting query.
Ditch LEFT(x.tipo, 3) LIKE ‘RC-’ and use x.tipo LIKE ‘RC-%’ or x.tipo LIKE ‘RCN%’ instead. Now MySQL can actually use your indexes. The speed difference was insane for me.
That GROUP BY with d.idcartera looks redundant too if idcartera’s already unique. You’re probably forcing unnecessary grouping. Run EXPLAIN on your query - look for filesorts or temporary tables. Those usually murder performance in complex joins like yours.
those joins are destroying your performance! add indexes on cartera(cedula, tipo) and planilla(libranza) first - that’ll help a lot. also, that group by looks weird when you’re selecting specific ids. might be doing extra work you don’t need.
I’ve dealt with slow MySQL queries for years. Database optimization is just putting bandaids on bigger problems. You can add indexes and rewrite queries all day, but you’re still hitting the same bottlenecks.
What changed everything? Moving this logic outside the database entirely. Instead of making MySQL handle heavy lifting with complex joins and string operations, I pull raw data and process it with proper automation tools.
Latenode nails this. Fetch your cartera and planilla data with separate simple queries, then use Latenode’s data processing nodes for filtering, concatenation, and grouping. No more expensive self-joins or LEFT functions killing performance.
Once it’s in Latenode, you can cache results, run schedules, and trigger updates when new data comes in. Way more flexible than optimizing a single monster query.
I’ve moved dozens of similar reporting queries out of MySQL this way. Performance went from minutes to seconds, and now I can modify logic without rewriting SQL.