mysql - How to improve this nested select statment -

how improve nested select statement because think has bad performance because see take long time execute , gets server load high

select user_tagtag, user_lastcontentid, (select count( content_id )    contents   contents.content_id > user_lastcontentid ,         contents.content_id in (           select contenttag_contentid           contents_vs_tags           contenttag_tagtag = user_tagtag         )  ) tag_newnews_no users_interests_tags   user_userid = 1  order user_tagsorder asc 

users_interests_tags table structure is:

+--------------------+--------------+------+-----+---------+-------+ | field              | type         | null | key | default | | +--------------------+--------------+------+-----+---------+-------+ | user_userid        | bigint(20)   | no   | pri | null    |       | | user_tagtag        | varchar(255) | no   | pri | null    |       | | user_lastcontentid | bigint(20)   | yes  | mul | null    |       | +--------------------+--------------+------+-----+---------+-------+ 

contents table structure is:

+---------------------+----------------+------+-----+---------+----------------+ | field               | type           | null | key | default |          | +---------------------+----------------+------+-----+---------+----------------+ | content_id          | bigint(20)     | no   | pri | null    | auto_increment | | content_title       | varchar(255)   | no   | mul | null    |                | +---------------------+----------------+------+-----+---------+----------------+ 

contents_vs_tags table structure is:

+----------------------+--------------+------+-----+---------+-------+ | field                | type         | null | key | default | | +----------------------+--------------+------+-----+---------+-------+ | contenttag_contentid | bigint(20)   | no   | pri | null    |       | | contenttag_tagtag    | varchar(255) | no   | pri | null    |       | +----------------------+--------------+------+-----+---------+-------+ 

where contents_vs_tags.contenttag_contentid index , foreign key of contents.content_id

any appreciated.

often correlated subquery (one must executed every row, , causing huge performance problems) can converted join, , query falls category:

select     user_tagtag,     user_lastcontentid,     count( distinct content_id ) tag_newnews_no users_interests_tags left join contents_vs_tags on contenttag_tagtag = user_tagtag left join contents on contents.content_id = contenttag_contentid user_userid = 1 , contents.content_id > user_lastcontentid group 1, 2 order user_tagsorder 

by using left join, tags without matching rows still returned, 0 count.

note use of distinct same results query returning.

this perform significantly better query (and better other answer!) because access directly tsbles, rather thsn via subqueries.

make sure have indexes on key columns:

create index contents_vs_tags_1 on contents_vs_tags(contenttag_tagtag); create index contents_1 on contents(content_id);