Searching multiple tables?

nwkeeley

Hello all!

I have multiple tables in my database that have identical structure. Why you ask? Well because it would spread out the queries so I'm not querying the same table a million times. But onward.

$sql = "SELECT * FROM tableX, tableY WHERE MATCH(title, description) AGAINST ('$a_string')";

So yah the tables are tableX and tableY

the 2 columns in each table i want to search is
title and description.

Any ideas?

Thanks, this forum rocks!

pohopo

I think what you want to use is UNION

SELECT FROM tableX WHERE etc etc...
UNION
SELECT FROM tableY WHERE etc etc...

http://dev.mysql.com/doc/mysql/en/UNION.html

nemonoman

Your idea of "multiple tables with same structure" is absolutely insane. I mean that in a bad way. There is no conceivable benefit to setting up your data the way yo u describe ("spread out the queries so I'm not querying the same table a million times") and many many many many reasons to avoid this bizarre construction.

You are asking for trouble with this approach, and your troubles will only multiply.

Do yourself a big favor and rethink your objectives.

About your "spread out the queries so I'm not querying the same table a million times" reasoning, consider:

All the tables on one server, with one hard drive and one cpu.

You could make one table with a thousand rows, or a thousand tables with just one row. Which uses fewer resources?

You could query one table a thousand time, or a thousand tables one time each. Which uses fewer resources? (Hint: there's not much difference).

You could gather a bunch of identical sorts of data from one table with one query -- or you can create an endless set of UNION statements, or combine results programattically after querying thousands of tables. Which uses fewer resources?

Why do you think smart people created database engines in the first place? Why do you throw all that helpful work away by creating such a nonsensical structure?

nwkeeley

I see what your saying in terms of searching, in that if you search 5 tables with 10 records each it would be the same as searching 1 table with 50 records.

But wouldn't it come as a benefit when there is a table with say 250,000 records, and within that table there is 12 'categories', and you want to select all the records of a particular category.

This way you can select for a particular table instead of selecting WHERE category = certain_type, and having to go through every single record and check to see if it is of that type?
This way you can avoid all those extra comparisons.

Seems like it would create less of a load on the cpu when displaying the categories, but interms of searching, than yes it would be the same it has to search every record of every table which would be the same as searching one massive table with all the records.

Roger_Ramjet

Quite right when you have 250,000 records and only 12 categories, AND THAT IS ALL YOU EVER DO.

I doubt if that is the case. Understand, DBMS do not search every record, they use indexes to locate records according to search criteria. Very different thing. A single pass over the index will locate every record in 1 category and return them almost as fast as on a single table, try a benchmark if you don't believe me. On the other hand, aggregating data from multiple tables involves far more resources than selecting the same data from one table. To locate data in a table, the DBMS first has to load and interpret the table schema: 12 tables = 12 table schemas = 12 the server load of 1 table, 12 tables = 12 sets of indexes = 12 * the disk reads of 1 table, etc.

nwkeeley

So basically I should consolidate my 12 tables into one massive table and have a column that identifieds which category a record is in. Don't get me wrong this would be a lot easier on my part, I was just trying to 'normalize' my tables

Roger_Ramjet

Quite right to. Unfortunately you seem to have missunderstood the process of normalisation. In 3rd normal form, repeated information is removed to a seperate table, eg customer's name and address are not stored with each invoice, but in a seperate table referanced through an id field. However, if it just the user's name then replacing it with a ref to a table that only contains 2 fields (id, name) is pointless.

As in most programming paradigms, Occam's Razor applies: never invent more entities than are absolutely neccessary to solve the problem, and the solution that requires the invention of the fewest entities (or tables) is the correct, and most elegant, one.

Normalisation can easily get out of hand. One should always devise a db schema with an eye to how the data will be used, and aim for one that will require the fewest joins in queries whilest still elliminating duplication and redundancy in the data. That is what makes database design an art as much as a science, and an art that is only perfected through experience.

nwkeeley

Okay I understand now, just have to go back and resetup my database pain but oh well thats what happens when you try and self teach yourself....... trial and error. But I agree 100% good design is not only a science but an art form.

Does anyone have any articles I could read about benchmarking queries into my database?

Roger_Ramjet

Probably the best place to start is here http://dev.mysql.com/doc/mysql/en/MySQL_Optimization.html. In particular, the section on EXPLAIN give you some handy tips for comparing different table/index scemes and query speeds. Look at the user comments at the bottom to see just what sort of performance gains are possible.