I need advice on these queries

Posted on

Question :

could you help me optimize these queries?

here are queries, please note that you see something like OR 1=1 OR those are values I pass via php and may change.

query#1:

SELECT posts.status, posts.reports, posts.post_id, posts.reply_counts, posts.sender, posts.content, posts.image, posts.thumb, posts.date, members.username, members.avatar,members.avatar_path,members.level, members.state 
FROM posts 
INNER JOIN members 
ON posts.sender = members.member_id 
WHERE posts.parent_id=0 AND posts.wall_id=0 AND ((members.status=1 AND posts.reports<3 AND posts.status=1)  OR 1=1 OR (-2=1 AND posts.status<>3)) AND posts.private_id=0  ORDER BY posts.post_id DESC, posts.date DESC LIMIT 0,25;

explain:

 id      select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
 1   SIMPLE      posts   ALL                     11647   Using where; Using filesort
 1   SIMPLE      members eq_ref  PRIMARY     PRIMARY     8   posts.sender    1   

query#2: this took around 0.68sec!

SELECT COUNT(notifies.notify_id) as cnt 
FROM notifies 
INNER JOIN posts 
ON (notifies.post_id = posts.parent_id OR notifies.post_id = posts.post_id) 
WHERE (posts.date>notifies.last_date AND posts.sender<>1) AND notifies.member_id=1 AND posts.private_id=0 AND posts.status=1 ;

explain:

 id      select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
 1   SIMPLE      notifies    ALL                     3650    Using where
 1   SIMPLE      posts   ALL     PRIMARY                 11647   Using where; Using join buffer

query#3:

SELECT posts.status, posts.reports, posts.private_id, posts.post_id, posts.sender, posts.post_ip, posts.content, posts.image, posts.thumb, posts.date, members.username, members.avatar, members.avatar_path,members.level, members.state 
FROM posts 
INNER JOIN members ON posts.sender = members.member_id 
WHERE posts.post_id=446 AND ((posts.reports<3 AND members.status=1 AND posts.status=1) OR 1=1 OR (-2=1 AND posts.status<>3)) AND posts.parent_id=0 AND ((posts.private_id=0 OR (posts.private_id<>0 AND posts.sender=1) OR posts.private_id=1) OR 1=1) LIMIT 0,1;

explain:

 id      select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
 1   SIMPLE      posts   const   PRIMARY     PRIMARY     8   const   1   
 1   SIMPLE      members     const   PRIMARY     PRIMARY     8   const   1   

query#4:

UPDATE likes INNER JOIN posts ON likes.post_id = posts.post_id  SET likes.seen=1  WHERE (posts.post_id=446 OR posts.parent_id=446) AND posts.sender=1 AND likes.seen=0;

and here table definition:

CREATE TABLE `posts` (
  `post_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `sender` bigint(20) NOT NULL,
  `content` blob NOT NULL,
  `image` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `thumb` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `date` datetime NOT NULL,
  `last_update` datetime NOT NULL,
  `status` tinyint(4) NOT NULL,
  `reports` int(11) NOT NULL,
  `reply_counts` int(11) NOT NULL,
  `parent_id` bigint(20) NOT NULL,
  `wall_id` bigint(20) NOT NULL,
  `private_id` bigint(20) NOT NULL,
  `post_ip` varchar(16) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`post_id`)
) ENGINE=MyISAM AUTO_INCREMENT=11584 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

CREATE TABLE `likes` (
  `like_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `post_id` bigint(20) NOT NULL,
  `member_id` bigint(20) NOT NULL,
  `seen` tinyint(4) NOT NULL,
  `date` datetime NOT NULL,
  PRIMARY KEY (`like_id`)
) ENGINE=MyISAM AUTO_INCREMENT=1793 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;


CREATE TABLE `members` (
  `member_id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`member_id`)
) ENGINE=MyISAM AUTO_INCREMENT=686 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

UPDATE

explain of select queries added.

Answer :

To optimize these query first you need to index on each table.

query#1:

From explain results itself you can know that add sender as index

query#2:

add *notifies.post_id* as index key

Try to make add indexing on your table . Check this http://dev.mysql.com/doc/refman/5.0/en/select-speed.html and http://net.tutsplus.com/tutorials/other/top-20-mysql-best-practices/

Leave a Reply

Your email address will not be published. Required fields are marked *