On my website I have a group of tables (about 40). I want visitors to be able to search all these (identical) tables at the same time.
Right now I'm using MATCH and UNION to do a FULL INDEX search on all tables. The problem is that this method is not very effective. The search query treats all the tables as unique. My tables often have a lot of information on the same subject, so MATCH often doesn't give results in my Full Index search (because of the more than 50% rule).
What should I change to make this search function more effective? Or should I also combine all tables in one large table? (that would be a lot more work, but would solve the MATCH problem)
I'm fairly new to PHP and MySQL, so all ideas are very welcome. Thank you in advance!