mysql storage question

rakennedy75

I am storing al lot of info in a table using mySQL. I am not sure if there is a max amount that will start to slow the search down. I could have potentially 10 to 100 thousands of entries in the table alone. This will have about 16 to 20 columns and most of the data is small. The smallest being 2 characters and the largest is about 35 characters.

At this point should I consider another approach where I will be speaking to multiple tables?

Up to this point most of my database/tables have been relatively small (usually no more than 1000 entries). It has been extremely fast in the searches. So before I attempt to build this one, I was wondering if anyone had any input on this.

Thanks for your time.

AstroTeg

This is more along the lines of database design principles.

The idea is to seperate like data into their own table whenever possible.

Quick example: 1 table stores user name and the cars they drive. Notice cars is plural - this should be a seperate table. I'm not sure if you have this situation or not with your current table design.

By splitting the data off into seperate tables, you can get better performance depending on what you need to do. It is also possible you have too many tables and performance will start to go down hill (but thats after some really big numbers or records).

To through some numbers at you, I have a MySQL database with 42 tables in it. One has 1,744,025 row and another has 2,385,188. I have 22 tables that have on average 40,000 rows. On the smaller 40k row tables, performance is great. But depending on what you want out of the 1+ million row tables, performance is ugly. It just depends on your query and how much work you try getting MySQL to perform in that query. The 22 tables are actually summary tables based off the two 1+ million row tables so I could work around the performance issues and have MySQL do the least amount of work needed.

rakennedy75

Thanks for the reply

That is the answer I was looking for. To give you a better illustration. I am buidling something similiar to a data system like hotels.com

I have multiple tables holding information but I was posting in particular to one table.

some of the tables:
owner (information of the owner of a building)
buildings( information about building/condo/resort)
events
ranking
amenities
promotions

those are a few of the tables I am using in the database. The one I was concerned about was the buildings. The reason being is that it holds the building name, and the number of rooms plus other cols. I already have two entries going in with over 200 rooms each. Not a big deal now, but potentially this could get up to much larger. The rooms have to have their own listing due to the fact they will have different features/amenities, rankings, promotions, etc.

I have a lot of repetitive info in the buildings table, like address, city, state. Should I make a table with just the building info and than create a table specifically just for the room info. Would that provide better performance?

Hopefully this is a good illustration of what i am doing. I will have to do some brushing up on the database design principles. Do you think this is a good approach?

Thanks again for your response and time

AstroTeg

It sounds like you have some more normalizing to do, as its called, but it sounds like you're thinking in the right direction. Things like address in the buildings table should be pulled out especially if its being duplicated (it also takes up less space to store an ID number and repeat the ID then repeat the full address).

Basically, if the data is being repeated, thats a good opertunity to put that data in a seperate table.

You might find you will end up with a building table and maybe another table for rooms. Each room would have a building ID that is stored. Then you can match the building info with the room info by matching the building IDs.

rakennedy75

I think I will do that, Plus it sounds a lot easier to organize in the long run. Thanks for the info not to mention your speedy response.