Will multiple table reduce the speed of the result?

I do have a datbase with multiple tables.

this multiple table is related to single name for example..

Table 1 contains name of the person, joined date,position,salary..etc

Table2 contains name of the person,cur开发者_如何学Gorent projects,finished,assigned...etc

Table 3 contains name of the person,time sheets,in,out,etc...

Table 4 contains name of the person,personal details,skill set,previous experiance,...etc

All table contains morethan 50000 names, and their details.

so my question is all tables contains information related to a name say Jose20856 this name is unique index of all 4 tables. when I search for Jose20856 all four table will give result and output to a front end software/html. so do I need to keep multiple table or combined to a single table??

If so

CASE 1
Single table -> what are the advantages? will result will be faster? what about the system resource usage?

CASE 2
Multiple table ->what are the advantages? will result will be faster? what about the system resource usage?

As I am new to MySQL I would like to have your valuable opinion to move ahead

You can combine these into a single table but only if it makes sense. It's hard to tell if the relationships in your tables are one-to-one or one-to-many but seem to be one-to-many. e.g. A single employee from table 1 should be able to have multiple projects, skills, time sheets in the other tables. These are all one-to-many relationships.

So, keep the multiple table design. You also should consider using an integer-based primary key for the employee rather than the name. Use this pkey as the fkey in your other tables and you'll see performance improvement. (Also consider the amount of work you need to do if and when you want to change the name. You have to change all the names in all the tables. If you use a surrogate key, the int pkey, as suggested above, you only have to update a single row.)

Read on the web about database normalization.

E.g. http://en.wikipedia.org/wiki/Database_normalization

I think you can even add more tables to it. It all depends on the data and the relations.

Table1 = users incl. userdata
Table2 = Projects (if multiple users work on the same project)
Table3 = Linking user to projects (if multiple users work on the same project)
Table4 = Time spent? Contains the links to the user and to the project.

I think your table 4 can be merged into table 1 cause it also contains data specific to 1 user.

There is probably more you can do but as already stated it all depends and the relations.

What we're talking about here is vertical table partitioning (as opposed to horizontal table partitioning). It is a valid database design pattern, which can be useful in these cases:

1. There are too many columns to fit into one table. That's pretty obvious.
2. There are columns which are accessed relatively often, and some that are accessed relatively rarely. For example, if you very often need to display columns joined date,position,salary and columns personal details,skill set,previous experiance very rarely, then it makes sense to move these columns to separate a table, as it will (probably) improve performance in accessing those most commonly used. In MySQL this is especially true in case of TEXT and BLOB columns, since they're stored apart from the rest of the fileds, so accessing them takes more time.
3. There are NULLable columns, where majority of rows are NULL. Once again, if it's mostly null, moving it to a separate table will let you reduce size of your 'mani' table and improve performance. The new table should not allow null values and have entries only for rows where value is set. This way you reduce amount of storeage/memory resources as well.
4. MySQL specific - You might want tom move some of your columns from nnoDB table to MyISAM, so that you can use full text indexing, while still being able to use some of the features InnoDB provides. It's not a good design gnerally speaking though - it's better to use a full text search engine like Sphinx.

Last but not least. I'd suggest using a numeric field as a key joining all these tables, not a string.

Additional reading aboout MySQL partitioning (a bit outdated, since MySQL 5.5 added some new features)