Optimizing Mysql Query

Question

This is the query that I'm looking to optimize:

SELECT Id, Title, Text,
(SELECT count(*) from ReaderFeedback as b
where b.EmailAddress=ReaderFeedback.EmailAddress) AS totalPosts
FROM ReaderFeedback FORCE INDEX (ParentFeedbackId)
WHERE ParentFeedbackId = 123 AND Approved=1 ORDER BY Date ASC

Below is the Mysql explain query results:

    id: 1
    select_type: PRIMARY
    table: ReaderFeedback
    Type: ref
    possible_keys :ParentFeedbackId
    Key: ParentFeedbackId
    key_len: 7
    ref: const,const
    rows: 2
    Extra: Using where; Using filesort

    id: 2
    DEPENDENT SUBQUERY
    b
    ALL
    NULL
    NULL
    NULL
    NULL
    101369
    Using where

Show create table freader feedback if below:

CREATE TABLE `ReaderFeedback` (
`Id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`ParentFeedbackId` int(11) DEFAULT NULL,
`Text` text NOT NULL,
`Title` varchar(75) NOT NULL DEFAULT '',
`EmailAddress` varchar(75) NOT NULL DEFAULT '',
`Date` date NOT NULL DEFAULT '0000-00-00',
`Approved` tinyint(4) DEFAULT '1',
PRIMARY KEY (`Id`),
KEY `ParentFeedbackId` (`ParentFeedbackId`,`Approved`),
KEY `Title` (`Title`),
KEY `Id` (`Id`),
KEY `Approved` (`Approved`),
) ENGINE=MyISAM AUTO_INCREMENT=142781 DEFAULT CHARSET=utf8

Please help out a fellow developer.

Thanks!

Can you please show us the explain plan? (ie. EXPLAIN EXTENDED SELECT ...) Also show us your show create table ReaderFeedback — Jaylen
– Jaylen, Commented Nov 24, 2014 at 1:22
Yeah you should consider my answer below. adding the index that I mention,and changing your query to a join will perform better for sure. Also, consider changing your engine to innodb instead of MyISAM — Jaylen
– Jaylen, Commented Nov 24, 2014 at 1:56

O. Jones · Accepted Answer · 2014-11-24 01:43:18Z

OK, let's break this down.

First, you're using a correlated subquery to get the post count. Let's turn that into a joined aggregate subquery. This gets you the count of rows for each distinct email address.

      SELECT COUNT(*) AS Count,
             EmailAddress
        FROM ReaderFeedback
       GROUP BY EmailAddress

Then, let's join that into your other query, like so.

SELECT a.Id, a.Title, a.Text
       b.Count
  FROM ReaderFeedback AS as
  JOIN (
          SELECT COUNT(*) AS Count,
                 EmailAddress
            FROM ReaderFeedback
           GROUP BY EmailAddress
       ) AS b ON a.EmailAddress = b.EmailAddress
 WHERE a.ParentFeedbackID = 123
   AND a.Approved = 1
 ORDER BY a.Date ASC

That should perform a little better than what you have already.

Going further than that involves experimenting with indexes. Here are some things to try.

Create a compound index on (ParentFeedbackID, Approved, EmailAddress, Date). This may (or may not) help satisfy the outer query.
Create an index on (EmailAddress). This will very likely help satisfy the inner GROUP BY query.

Jaylen · Accepted Answer · 2014-11-24 02:00:21Z

Without seeing your table structure and the explain plan for the query it is hard to offer much help

But, try this query as it should give you better results.

SELECT R.Id, R.Title, R.Text, b.totalPosts
FROM ReaderFeedback R
INNER JOIN (
    SELECT EmailAddress, count(*) AS totalPosts
    FROM ReaderFeedback
    WHERE ParentFeedbackId = 123 AND Approved=1
    GROUP BYEmailAddress
) AS b ON b.EmailAddress = R.EmailAddress
WHERE R.ParentFeedbackId = 123 AND R.Approved=1 
ORDER BY R.Date

In addition, you should have add an index (if it does not already exists) on the ReaderFeedback table like so

ALTER TABLE ReaderFeedback  ADD INDEX (EmailAddress, ParentFeedbackId , Approved, Date);

Also, consider changing your Table engine from MyISAM to INNODB.

ALTER TABLE ReaderFeedback ENGINE=INNODB;

You can also change your EmailAddress column to CHARACTER SET latin1 COLLATE latin1_general_ci as this will make the lookup part quicker.

ALTER TABLE ReaderFeedback CHANGE `EmailAddress` EmailAddress VARCHAR(75) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL,

Good Luck!

Collectives™ on Stack Overflow

Optimizing Mysql Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related