Is there a better way to query the following?

[PaulRyan]

I have the following query, which selects threads from a specific category (in the case I set it as "1"), it also queries another table to get when the thread was last viewed to accommodate a "thread view" feature, displaying a different image if it has been viewed or not.

 

 

SELECT `ft`.`id`, `ft`.`title`, `ft`.`author_id`, `ft`.`reply_count`, `ft`.`view_count`, `ft`.`timestamp`, `ft`.`locked`,
                                          `u`.`username`,
                                          IFNULL(`u2`.`username`, `u`.`username`) AS `last_activity_name`,
                                          IFNULL(`ftr`.`author_id`, `ft`.`author_id`) AS `last_activity_id`,
                                          IFNULL(`ftr`.`timestamp`, `ft`.`timestamp`) AS `last_activity_time`,
                                          `ftvl`.`timestamp`, ftr.timestamp
                                   FROM `forum_threads` AS `ft`
                                   LEFT JOIN `users` AS `u` ON `u`.`id` = `ft`.`author_id`
                                   LEFT JOIN (SELECT `id`, `thread_id`, `author_id`, `timestamp` FROM `forum_thread_replies` ORDER BY `timestamp` DESC) AS `ftr` ON `ftr`.`thread_id` = `ft`.`id`
                                   LEFT JOIN `users` AS `u2` ON `u2`.`id` = `ftr`.`author_id`
                                   LEFT JOIN `forum_thread_view_log` AS `ftvl` ON `ftvl`.`thread_id` = `ft`.`id` AND `ftvl`.`user_id` = 4
                                   WHERE `ft`.`category_id` = 1
                                   GROUP BY (`ft`.`id`)
                                   ORDER BY `ft`.`id` DESC
                                   LIMIT 0, 25

 

I've performed an explain on the query and I'm not really liking how its querying, as I'm sure there is a better way to do it.

Here is an image of the EXPLAIN on the query.

 

 

Here are my table structures also:

 

 

CREATE TABLE IF NOT EXISTS `forum_threads` (
  `id` mediumint(5) NOT NULL AUTO_INCREMENT,
  `category_id` tinyint(1) NOT NULL,
  `title` varchar(255) NOT NULL,
  `content` text NOT NULL,
  `author_id` int(11) NOT NULL,
  `reply_count` smallint(4) NOT NULL DEFAULT '0',
  `view_count` mediumint(7) NOT NULL DEFAULT '0',
  `locked` tinyint(1) NOT NULL DEFAULT '0',
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `forum_threads`
--

INSERT INTO `forum_threads` (`id`, `category_id`, `title`, `content`, `author_id`, `reply_count`, `view_count`, `locked`, `timestamp`) VALUES
(1, 3, '234', '123', 4, 3, 3, 0, '2013-04-28 02:19:15'),
(2, 1, 'asdsdf', 'sfdsfsdf', 4, 1, 6, 1, '2013-04-28 02:20:11'),
(3, 2, 'sss', 'asdasdads', 3, 1, 1, 0, '2013-04-28 02:37:25'),
(4, 1, 'sdfsdf', 'dsdfsdf', 3, 3, 4, 0, '2013-04-28 02:52:16'),
(5, 1, 'Another thread?', 'Another thread?', 3, 8, 4, 0, '2013-04-28 02:54:16');

 

 

CREATE TABLE IF NOT EXISTS `forum_thread_replies` (
  `id` mediumint(7) NOT NULL AUTO_INCREMENT,
  `thread_id` mediumint(5) NOT NULL,
  `content` text NOT NULL,
  `author_id` int(11) NOT NULL,
  `timestamp` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ;

--
-- Dumping data for table `forum_thread_replies`
--

INSERT INTO `forum_thread_replies` (`id`, `thread_id`, `content`, `author_id`, `timestamp`) VALUES
(1, 2, 'dsdf', 19, '2013-04-28 02:22:00'),
(2, 2, 'dsfsdf', 4, '2013-04-28 02:25:00'),
(3, 1, '12345', 3, '2013-04-28 02:50:05'),
(4, 4, 'Test reply.', 3, '2013-07-01 22:33:37'),
(5, 5, 'Another test.', 3, '2013-07-01 22:33:50'),
(6, 5, 'sdfsdfsdf', 3, '2013-07-01 22:36:07'),
(7, 5, 'sdfsfsdf', 3, '2013-07-01 22:40:22'),
(8, 5, 'sfdsfdsdfsfd', 3, '2013-07-01 22:40:34'),
(9, 5, '1', 3, '2013-07-01 22:47:09'),
(10, 4, '2', 3, '2013-07-01 22:47:18'),
(11, 1, '242424', 3, '2013-07-01 22:48:26'),
(12, 5, '234', 3, '2013-07-01 22:49:05'),
(13, 5, 'sfdsdf', 3, '2013-07-01 22:50:47'),
(14, 4, 'sfdsfsf', 3, '2013-07-01 22:51:01'),
(15, 5, 'wer', 3, '2013-07-01 22:54:53'),
(16, 3, 'adad', 3, '2013-07-01 23:18:07');

 

 

CREATE TABLE IF NOT EXISTS `forum_thread_view_log` (
  `user_id` int(11) NOT NULL,
  `thread_id` mediumint(5) NOT NULL,
  `timestamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `forum_thread_view_log`
--

INSERT INTO `forum_thread_view_log` (`user_id`, `thread_id`, `timestamp`) VALUES
(3, 1, '2013-07-01 22:48:35'),
(3, 2, '2013-07-01 22:48:11'),
(3, 3, '2013-07-01 23:18:08'),
(3, 4, '2013-07-01 22:53:51'),
(3, 5, '2013-07-01 22:54:53'),
(19, 2, '2013-04-28 03:10:24'),
(5, 2, '2013-04-28 03:11:23'),
(4, 2, '2013-07-02 00:26:37'),
(4, 4, '2013-07-01 22:39:47'),
(4, 5, '2013-07-02 00:29:53'),
(4, 1, '2013-07-02 00:12:39');

 

So after all of that, my question is: Can someone help improve that query?

 

If you need any more information, please let me know.

 

Thanks, Paul.

[n1concepts]

Hi,

 

I know what you are saying and asking - dealing with similar issues myself.

From a quick glance at your SQL statements and the 'EXPLAIN" results - one thing that sticks out to me is not having any defined 'indexes'.

 

I would suggest - based on your actual queries - establish indexes based on your "WHERE" clauses (look at 'ON' clauses too which I assume those are all primary/foriegn keys.

Reason: without those constraints/keys, the SQL query peforming a full search - as shown in the EXPLAIN results (indexes NULL). ALso, I see temp tables and file sorts being established b/c of 'Order by' clause, etc... Might not be any way around that and ok as long as indexes set.

 

Got cha: be careful on how much you rely on indexes b/c if there are alot of INSERTS, UPDATES, or DELETES, then all of those processes will take a hit - although your searches will benefit greatly.

You basically have to figure out the priorities to decide which out weigh the other  - this thinking is that all READ/WRITE operations running on one db server; There's a whole other discussion if you slaving or replicating db operations.

 

Hope this, at leasts, gives you some insight on how to optimize your query.

Stary w/looking at your indexes.