Complex website database

MySQL & MariaDB Other MySQL® Questions
1

Hello, i m new to this forum

I ve got a little question about a database structure

for two given website, i have one database

database

table:articles_site1
table:articles_site2
table:users

when i m logged in the first website, the article are taken from article_site1

when i m logged in the second website, the article are taken from article_site2

when i m logged in the second website, i m logged in the first website (vice versa)

now i would like to do a view of an union of articles_site1 and articles_site2, in order to display information (like similar discusions for an article) taken from the union of the two tables

what is the best chose temporarytable or merge, i think it s temporarytable but i m not sure, my other problem is that fulltext is not supported on view,

i looked at this article, but didn’t manage to find the answer
http://www.mysqlperformanceblog.com/2007/08/12/mysql-view-as -performance-troublemaker/

i was thinking to merge this two articles_siteX table in one, and to update them via a cron at night, will this give me bette performance ?

thanks in advance

2

Why not consolidate the two tables into a single table with a tinyint (1 byte) column “site_id”?

3

hello

thanks for your answer,

indeed i could do that, but i m using a “ready to serve” cms, and that will be too much work for maintenance

4

Why not just do

SELECT * FROM articles_site1
UNION
SELECT * FROM articles_site2

Then you can take advantage of the fulltext indexes on each table and sort the final results.

5

hello

the goal is to hide complexity (as i m using this view for similar discussion and other stuff)

thanks

more over i ve done a “union all” (see http://www.mysqlperformanceblog.com/2007/10/05/union-vs-unio n-all-performance/)