Can sharding help in this scenario? I will have around 600 GB of data in single table with multiple columns in MySQL. Basically this table will store user events from application. 
Indexes are there on required columns. 

Question: If I shard the data based on some key which will be included  in every  query,
insertion and selection will happen on limited data set. Say I have 3 nodes  and data is redistributed equally on all 3 nodes with each shard of 200 GB.
Now based on shard key in query, I will send it to specific DB only.I think it will improve the search performance. Is that right ?

Constraint : Issue is I have single node. If create three shards(of 200 GB each) on single node , will it help in search performance ?

Can MongoDB(instead of MYSQL) help here with same approach ?
If create three shards(of 200 GB each) on single node, will it help in search performance ?


Short Answer: No.

Info from Comments:

16GB of RAM.

The important query is a "search", but it is rather unbounded.  Hence, we need to assume that the whole table (600GB) needs to be scanned.

So, why not have multiple instances of MySQL, each with a fraction of the data?  They could work in parallel, couldn't they?

Long Answer:

I/O is much more costly (10x?  100x?) than CPU or context switching or looking a cache.  Of the 16GB, you will allocate perhaps 11GB for  innodb_buffer_pool_size, which is the main "cache" in MySQL.  There is no way to fit 600GB into 11GB, so there will be I/O.  If you shard across ~60 separate machines, then you could avoid I/O.  Or if you had 700GB of RAM on the one machine.

So, on a single machine, the parallelism is defeated because of there being only one access to disk, and each parallel thread need to spend most of its time fetching data from disk.  You would see the CPU cores sitting there twiddling their thumbs waiting for I/O.

What if an index could be used?

Why bother with sharding?  With a usable index, the query is likely to be fast because it won't be touching much of the 600GB, hence doing very little I/O.

And if you have lots of different connections, MySQL (with the help of the OS) is happy to farm them out to different CPUs.

Hence, I/O is not a bottleneck; CPU is not a bottleneck?  The sharded system is complex, but for no benefit.

When is Sharding needed?

If you need "read scaling" -- that is, too many clients are accessing MySQL, and the system is overloaded -- use Replication.  Each Slave has a copy of the data.  You can add as many Slaves as needed to handle the load.  Note:  Each Slave needs to be on its own hardware.

If you need "write scaling" -- that is, the writes, not the reads, are the bottleneck -- then you might need multiple servers and do "sharding".

Even with either of those situations, I suggest that there may be ways to improve the performance of the queries / indexes / ETL / etc to tame it.