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/