MySQL Performance (DB with many small tables or DB with fewer large tables)

jsia18

Hi,
I have a database and I am expecting a lot of data to come in. I was thinking to split up the database to multiple tables i.e. if the surname of a person starts with aa to ab i put it in table a0, if ac to ad i put it in table a1. Will this make my querying faster or is it better to put it in a large table? Or if ever are there any other ways to make my database more efficient. I am also expecting high traffic in my web site so can you suggest some techniques to ease the load on the database server?

pbmods

Use indexes. With indexes, it doesn't matter how big your table is; if your query data can be found in an index, MySQL will know exactly where to search.

If you have good indexes, you might have 100,000 rows, but MySQL still might only have to search a dozen of them at most (depends on the query and how many results you expect to get back, but for example, if you search for a primary key, MySQL only has to search ONE row, regardless of the total number of rows!).

Here's some info on indexes:
http://www.databasejournal.com/featu...le.php/1382791

If you want to test your indexes, use the EXPLAIN keyword on any query:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also, MyISAM tables are generally faster than InnoDB tables for large datasets.

jsia18

Quote:

Originally Posted by pbmods

Use indexes. With indexes, it doesn't matter how big your table is; if your query data can be found in an index, MySQL will know exactly where to search.

If you have good indexes, you might have 100,000 rows, but MySQL still might only have to search a dozen of them at most (depends on the query and how many results you expect to get back, but for example, if you search for a primary key, MySQL only has to search ONE row, regardless of the total number of rows!).

Here's some info on indexes:
http://www.databasejournal.com/featu...le.php/1382791

If you want to test your indexes, use the EXPLAIN keyword on any query:
http://dev.mysql.com/doc/refman/5.0/en/explain.html

Also, MyISAM tables are generally faster than InnoDB tables for large datasets.

Is this true even if a table contains 1 million records? If i split the tables up like what i've said in my first post what are its disadvantages? By the way one person will have multiple entries and i need to query all of the records of each person but ill do it 10 records at a time so i need to repeatedly query the table

Motoma

Quote:

Originally Posted by jsia18

Is this true even if a table contains 1 million records? If i split the tables up like what i've said in my first post what are its disadvantages? By the way one person will have multiple entries and i need to query all of the records of each person but ill do it 10 records at a time so i need to repeatedly query the table

The disadvantages is the fact that you will need to perform multiple queries and a UNION in order to return one dataset.

Indexes are the way to go in this situation, as they were designed with this purpose in mind.

MySQL Performance (DB with many small tables or DB with fewer large tables)

re: MySQL Performance (DB with many small tables or DB with fewer large tables)

re: MySQL Performance (DB with many small tables or DB with fewer large tables)

re: MySQL Performance (DB with many small tables or DB with fewer large tables)

/bytes/development

/bytes/it

/bytes/about