Let me give a little background to my question. I have a large amount of data, currently at around 15,000 Rows 60MBs, and it grows at the rate of about 4,000 rows (16MBs) a year. When the database was smaller I used to have just one table with everything in it. But at around 8,000 rows (32MBs) it was running too slow, even simple select statments took a long time to complete. So I divided the data up, made catagories and gave each catagory its own table, and everything worked fine.
The way it worked is that each row is a member of a catagory (catagories are defined in a seperate table), but now I have been asked to make it so that each row can be a member of more than one catagory.
Normally I would have made a table connect the catagoryID to the articleID from the one main table, but because I have all the articles spread out over different tables that won't work. I can't combine all the rows into one table again because it is too slow. I could add a table field in the connecting table that has the name of the table that contains the actual data, but that would require 2 sql statements to get the final data which i'd rather not do if i can avoid it. Does any one have any suggestions as to how i can design my tables so the information can be called with one sql statement without having to have all the rows in one table?
Bringing them all back together isn't a problem if I can solve the speed issue. From past conversations I was told that my table size (60MB right now) was too much. Can you point me in the right direction as far as indexing so i can see if that will solve my problem? I currently just have a Primary key.