Query with a few subqueries and appropriate indicies taking, on average, 613ms to execute I have a ton of queries that do a similar thing as to what I'm doing here, but none of them have the performance issues this one does:

SELECT 
  (SELECT lead_id FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_id, 
  (SELECT CONCAT(first_name, " ", last_name) FROM leads WHERE to_id = lead_id OR from_id = lead_id) as lead_name 
FROM 
  text_messages t 
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx" 
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC;


That query right there takes on average 613ms to execute.

I have indicies on from_id, to_id, sent_to_number, and sent_from_number.

Why is this query taking so long to execute?

EDIT:

Added in EXPLAIN EXTENDED:

+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
| id | select_type        | table | partitions | type        | possible_keys                      | key                   | key_len | ref  | rows | filtered | Extra                                                                            |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+
|  1 | PRIMARY            | t     | NULL       | index_merge | organization,from_number,to_number | to_number,from_number | 62,62   | NULL |   83 |   100.00 | Using union(to_number,from_number); Using where; Using temporary; Using filesort |
|  3 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
|  2 | DEPENDENT SUBQUERY | leads | NULL       | ALL         | PRIMARY                            | NULL                  | NULL    | NULL | 7910 |    19.00 | Range checked for each record (index map: 0x1)                                   |
+----+--------------------+-------+------------+-------------+------------------------------------+-----------------------+---------+------+------+----------+----------------------------------------------------------------------------------+


Edit 2:

Server Spec:
Cloud SQL - MySQL 5.7 - 1 CPU - 3.75 GB RAM - 100 GB SSD
Your query plan shows two table scans on leads, which is not surprising given the search predicates with OR: FROM leads WHERE to_id = lead_id OR from_id = lead_id.

It also shows a spilled sort, suggesting that the sort buffer is too small.

Consider rewriting your query with joins instead of subselects, something like:

SELECT 
  COALESCE(leads_to.lead_id, leads_from.lead_id) as lead_id, 
  COALESCE(CONCAT(leads_to.first_name, " ", leads_to.last_name), CONCAT(leads_from.first_name, " ", leads_from.last_name)) as lead_name 
FROM 
  text_messages t 
LEFT JOIN leads leads_to ON to_id = leads_to.lead_id
LEFT JOIN leads leads_from ON to_id = leads_from.lead_id
WHERE sent_to_number = "xxx" OR sent_from_number = "xxx" 
  AND organization = 1 GROUP BY lead_id ORDER BY sent_at DESC


To address the sort spill try increasing sort_buffer_size.