Hi,

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!

Member Avatar for diafol

If you have identically structured tables, I don't see much point in having more than one. If they have a different purpose, simply put a new field, say type_id, so records can be identified easily that way.

You can create one table easily via SQL or even via a MySQL GUI.

commented: thank you for the clear advice! +1

If you have identically structured tables, I don't see much point in having more than one. If they have a different purpose, simply put a new field, say type_id, so records can be identified easily that way.

You can create one table easily via SQL or even via a MySQL GUI.

Thank you! The tables have a different purpose, but I never thought about selecting them with an id from one large table. (I'm just starting with this so forgive my ignorance :) )

Of course I do understand that you can create one table easily, what I meant was that it would be annoying to do both (creating different tables for different purposes and combining them in one large table).

But, as you explained, there is really no point in making a lot of different identical tables, so I will but them all together. Problem solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.