Nrthugu

Is there a way I can speed this up? Right now it's taking an unbelievably insane amount of time to query.

SELECT trades.*, trader1.user_name as trader1_name,

trader2.user_name as trader2_name FROM trades

LEFT JOIN logs_players trader1 ON trader1.user_id = trader1_account_id

LEFT JOIN logs_players trader2 ON trader2.user_id = trader2_account_id

ORDER BY time_added

LIMIT 20 OFFSET 0;

I've done as much as I could in terms of searching online for a solution. Or even just trying to get some more information why it's taking so long to execute.

The query takes about 45 seconds or so to complete.

Create statements:

CREATE TABLE `trades` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `trader1_account_id` int(11) DEFAULT NULL,

  `trader2_account_id` int(11) DEFAULT NULL,

  `trader1_value` bigint(20) DEFAULT NULL,

  `trader2_value` bigint(20) DEFAULT NULL,

  `trader1_ip` varchar(16) DEFAULT NULL,

  `trader2_ip` varchar(16) DEFAULT NULL,

  `world` int(11) DEFAULT NULL,

  `x` int(11) DEFAULT NULL,

  `z` int(11) DEFAULT NULL,

  `level` int(11) DEFAULT NULL,

  `trader1_user` varchar(12) DEFAULT NULL,

  `trader2_user` varchar(12) DEFAULT NULL,

  `time_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8





CREATE TABLE `logs_players` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `user_id` int(11) DEFAULT NULL,

  `user_name` varchar(20) DEFAULT NULL,

  `world_stage` varchar(20) DEFAULT NULL,

  `world_type` varchar(20) DEFAULT NULL,

  `bank` longtext,

  `inventory` longtext,

  `equipment` longtext,

  `total_wealth` mediumtext,

  `total_play_time` mediumtext,

  `rights` int(11) DEFAULT NULL,

  `icon` int(11) DEFAULT NULL,

  `ironmode` int(11) DEFAULT NULL,

  `x` int(11) DEFAULT NULL,

  `z` int(11) DEFAULT NULL,

  `level` int(11) DEFAULT NULL,

  `last_ip` varchar(16) DEFAULT NULL,

  `last_online` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

  `muted_until` timestamp NULL DEFAULT NULL,

  `banned_until` timestamp NULL DEFAULT NULL,

  PRIMARY KEY (`id`)

) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=utf8

I filled a sample database with 10k rows each, and found that a few indexes were what you needed:

ALTER TABLE `logs_players` ADD INDEX(`user_id`);

ALTER TABLE `trades` ADD INDEX(`time_added`);

The main index we need is an index on user_id. Changing that we went from a query time of 20.1390 seconds, to 0.0130 seconds:

We can even get that down further, by adding an index on time_added to make sorting a lot faster, now we ended up with an impressive query time:

Do some research on indexes! A simple EXPLAIN query would show you that you're using filesort (Which is rather bad!):

After indexes, this looks a lot better: