MySQL sanity check

[craptakular]

I think I have just had a like epiphany type moment, I think I have realised that my first DB is totally retarded and stupid.

Lets start from the beginning:

I have a database.

Currently I have a dozen tables for each product, with identical column names which I try to join when I query but it fails and seems to complex in my eyes.

Should I have all the individual product tables merged into one single products table?

This would greatly simplify my work and would make it easier for me to query the date, this in my mind seems sensible because I am trying to join them anyway so if all the columns are the same I can just have one giant table with all products on?

If I go down the single products table route I expect to have around 10k-20k entries in 10 years. Would this effect performance when outputting data onto a website? I would only want to filter by the latest 10 entries by timestamp anyway.

 

[topdog@OC]

Single table definitely, 10-20k rows in a table is still not even walking pace for a db so long as it is adequately tuned.

Have a go at it with a test site/page and a mock table containing a lot of data that has been put there by a script and see for yourself.

 

[craptakular]

Thanks man, doing this in separate tables has reduced me to tears.

I can still filter by each product using the productid

 

[craptakular]

How Can I merge tables?

I'm looking to move all data from table2 under the data held in table1

 

[Crowze]

You can do an INSERT ... SELECT statement, something like this:

INSERT INTO table1 SELECT col1, col2, col3 FROM table2

You'll have to select all columns manually though except the primary key or you'll undoubtedly get conflicts - that might also make updating any references to table2's primary key a bit of a fiddle.

 

[craptakular]

Thanks, this worked!

INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3 FROM table2