[RESOLVED] Getting info from structurally identical tables?

[my_name]

I'm an SQL newbie. Is it possible to get info from tables that are identical in their structure?

For example:

Jewelry Table:
Item ID | Item Name | Item Price
1 | Earrings | $1.00
2 | Necklace | $3.00

Furniture Table:
Item ID | Item Name | Item Price
3 | Table | $16.00
4 | Chair | $12.00

The item ID is always unique, regardless of the table. The reason I divided the table into smaller tables is that I thought it might improve performance if most queries didn't have as much data to sort through.

First of all, is that assumption true?

Second, there are times when I do need all the tables "combined" so that I can, for example, search for the price of a specific item without knowing exactly which table the item was in. Is that possible?

Thanks!

Jason

[rincewind456]

Yes you can as they are not identical, they have different table names.
Yes bound to be faster even if only nanoseconds.
Yes you can join the tables see here for Join syntax.

[nemonoman]

breaking the tables this way is a really, really bad idea that will have no discernable speed improvements.

BEST is one table with a "type" column: jewelry, furniture.

Even if you had a 10,000 rows in current method that would only be about 8KBs, in other words, hardly a tiny mouthful for most servers. Most server disk drives have an 4-8 MB look ahead -- 1000x times bigger than a whole 10000 row table.

Performance will be practically instantaneous.

IF you plan to have millions of records, you could do some improvements:

Better would be a type-code: 1 for jewelry, 2 for furniture, etc.

Better also would be a description code: 1 for earrings, 2 for necklace,etc.

Also so some judicious indexing.

[my_name]

Thanks for the replies.

In regards to the size of the tables: the client eventually wants about 6000 items listed, so that's 6000 rows. Each item has about 8 columns associated with it:
Item# | Name | Description | Price | ExclusiveOrNot | ShippedOrNot | CustomerPaidOrNot | ItemType

Example: 123456 | CROSS PENDANT PHOTO FRAME | Exquisite pewter photo frame features a jeweled hanging cross in an oval cutout. 8.375" x 5.5" high frame holds one 3.5" x 5" photo. | 19.95 | 0 | 0 | 0 | 7

Right now, when a visitor requests, for example, the furnture category, I just have the entire furntire table displayed.

I'm using a shared-hosting plan (on iPowerWeb.com), so I was worried that I might run into problems there since other people would be hitting the database, as well. That's why I divided the table into several smaller tables. Do you think that's unnecessary even given that it's on a shared-hosting plan? It would definitely make life easier for me if it were just one table.

Thanks.

[nemonoman]

Whatever performance concerns you have, performance will NOT be improved by dividing the tables. If you divide the tables, your coding work will increase geometrically, however.

DON'T divide these tables without some compelling reason. So far you don't have a compelling reason.

PS. I have just reread your last post: Your biggest performance hit will be in the DISPLAY of the information, in other words, creating and sending the HTML. The database time should be practically nil (i.e. less .01 seconds). So work on making your HTML go fast, and don't worry about the database.

PS if your database activity takes more than say 0.08 seconds, you can bring it down to .01 seconds through query tuning and indexing. Ask for help if and ONLY if you really need it. Hint: you probably don't need it.

[Roger_Ramjet]

Just to add a point regarding your first post.

Yes, it is possible to join all the tables in a UNION QUERY but union queries are slow as the same query is run multiple times, once for each table/query in the union. Very usefull when you NEED then, but you don't need one here as Nemo has explained.