Cloudways Requests GD To Rewrite SQL Query In A Compound Form

Based on the recommendation of GD support, I switched my hosting company from GoDaddy to Cloudways.

I have just over 12,000 entries in my GD places database.

When I do a full map, the SQL query puts a large drain on server resources. I’ve doubled them and Cloudways support has optimized the SQL Max Connections as much as possible. Still, the query GD is using is not efficient.

Cloudways has identified that the problem is such a heavy query, in which multiple joins are used. Here are the results from the query

mysql> SELECT COUNT(*) FROM wp_t092pz9v2w_terms;
+———-+
| COUNT(*) |
+———-+
| 137 |
+———-+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM wp_t092pz9v2w_term_taxonomy;
+———-+
| COUNT(*) |
+———-+
| 137 |
+———-+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM wp_t092pz9v2w_term_relationships;
+———-+
| COUNT(*) |
+———-+
| 49268 |
+———-+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM wp_t092pz9v2w_geodir_gd_place_detail;
+———-+
| COUNT(*) |
+———-+
| 12093 |
+———-+
1 row in set (0.00 sec)

Here is the query:

SELECT concat( t.name, ‘|’, sum( count ) ) name, sum( count ) cnt FROM wp_t092pz9v2w_terms t, wp_t092pz9v2w_term_taxonomy tt LEFT JOIN wp_t092pz9v2w_term_relationships tr ON tr.term_taxonomy_id = tt.term_taxonomy_id LEFT JOIN wp_t092pz9v2w_geodir_gd_place_detail pd ON tr.term_taxonomy_id = tt.term_taxonomy_id WHERE t.term_id = tt.term_id AND t.name LIKE ‘%church%’ AND tt.taxonomy in (‘gd_placecategory’,’gd_place_tags’) GROUP BY t.name ORDER BY cnt DESC LIMIT 10;

Cloudways told me: Actually in JOIN query, the whole table is being read and when you are using multiple tables and multiple joins it slows down the query and takes time to execute.

They asked if I wrote the query or did GD Development. When I told them that GD Development wrote the query, they asked me to ask you to rewrite the query in a compound query manner.

“Can you please ask them to write it in a compound query manner.” They explained to me that a compound query means sub query it has different query plan and works more efficiently than JOIN.

This is a critical need. What can you do for me?