Oracle Equivalent Parallel Option in Mysql

Question

In oracle, we can create a table and insert data and select it with a parallel option.

Is there a similar option in mysql. I am switching from oracle to mysql and my system has more samples and fewer data changes, so any choice for parallel selection is what I am looking for.

for example: Let's consider that my table has 1 million rows, and if I use the parallel (5) parameter, then five threads work with the same query with a limit and select approximately 200K each, and as a final result I get 1 million records in 1/5 of normal time.

Answer 1

In short, the answer is no.

The MySQL server is designed to simultaneously run parallel user sessions, but not to execute one given user session in several parts in parallel.

This is a personal opinion, but I would refrain from applying the optimization forward by making assumptions about how RDBMS works. It is better to first measure the request and see if the response time is really a real problem or not, and only then explore possible optimizations.

"Premature optimization is the root of all evil." (Donald Knut)

Answer 2

MySQL queries are always executed in parallel. However, if you want to run different requests simultaneously through your program, you will need to open different connections between workers so that your program has asynchronous access.

You can also run tasks by creating events or using deferred inserts, however I don't think that is very good here. Something else to consider:

Typically, some operations are secured between separate session requests (called transactions). They are supported by InnoDB backend, but not MyISAM tables (but it supports a concept called atomic operations). There are different levels of isolation that differ in which operations are protected from each other (and, therefore, how operations in one parallel transaction affect another) and their performance impact. - Holger Just

He also mentions the MySQL redirect page, which briefly looks at the various types of engines available for MySQL (MyISAM is faster, but not so reliable): MySQL Transcations