php - Merging two various MySQL tables, order and limit it for a combined result -

i need combine various mysql tables common result. needed , expected result list of last x comments on website tables individuals, like: news_comments, pages_comments, etc. individually, tables has joins users database.

the structure of 3 of comments table are:

news_comments, pages_comments, other_comments id int ai id_new/id_page/id_other int id_user int comment text created int (timestamp) 

the table users:

id int ai login int password varchar ... 

i don't complete last because need id , login.

the normal use of queries (individuals) are:

select users.id, users.login, news_comments.*, news_comments.id id_comment users, news_comments users.id=news_comments.id_user , news_comments.id_new=$id order created desc limit $offset,$rows 

and works fine!

but in backend want have list of comments on website moderate , control it. need combine comments tables, order created desc , limit result make pagination.

i use mysql , php, if need use php there no problem that.

how can it?

thanks in advance!

select  *    (         select  *            (                 select  *                    news_comments                   id_new = $id                 order                         created desc                 limit $rows                 ) q         union         select  *            (                 select  *                    pages_comments                   id_new = $id                 order                         created desc                 limit $rows                 ) q         union         select  *            (                 select  *                    other_comments                   id_new = $id                 order                         created desc                 limit $rows                 ) q         ) q join    users u on      u.id = q.id_user order         created desc limit   $offset, $rows 

note limits applied each of inner queries benefit indexes.

create following indexes:

create index ix_newscomments_new_created on news_comments (id_new, created) create index ix_pagescomments_new_created on pages_comments (id_new, created) create index ix_othercomments_new_created on other_comments (id_new, created) 

for work fast