Query optimization needed.

[peycho]

Hello.

All looks good, but I have issue with loading data from mysql server.
The result comes, but at the cost of 2 min. 100% usage of 2 cores.

Is there a way to optimize the query pulling data from mysql?

[FrancescoBorzi]

that's weird, @Aokromes do you have the same issues? personally I've never tried it on a live environment

[peycho]

Maybe is because I have very big data in my table. About 35Mb table size.

[Aokromes]

No problem here with 235 mb world db.

[peycho]

My api and quest abandoned/complete is hosted on remote server and remotely access characters database.

[peycho]

SELECT t1.id, t2.LogTitle, COUNT(t1.quest_abandon_time) AS abandoned_times, COUNT(t1.quest_complete_time) AS completed_times, MAX(t1.quest_abandon_time) AS last_abandoned, MAX(t1.quest_complete_time) AS last_completed FROM (SELECT id, quest_abandon_time, quest_complete_time, core_hash, core_revision FROM characters.quest_tracker) AS t1 JOIN (SELECT ID, LogTitle FROM world.quest_template) AS t2 ON t1.id = t2.ID GROUP BY t1.id HAVING abandoned_times > 0 ORDER BY abandoned_times DESC LIMIT 0, 100;

100 rows in set (1 min 34.37 sec)

cat /proc/cpuinfo | grep -E 'model name|bogomips'
model name  : Intel(R) Core(TM) i5-3570S CPU @ 3.10GHz
bogomips    : 6185.98
model name  : Intel(R) Core(TM) i5-3570S CPU @ 3.10GHz
bogomips    : 6185.98
model name  : Intel(R) Core(TM) i5-3570S CPU @ 3.10GHz
bogomips    : 6185.98
model name  : Intel(R) Core(TM) i5-3570S CPU @ 3.10GHz
bogomips    : 6185.98

I have 16Gb Ram with ECC and 4 cores with 3.10 Ghz. The server load is under 0.50

[Aokromes]

Oh wait, i mixed projects, yes, indeed, quest tracker is VERY slow.