title: mysql relational table partitioning
body: <p>Hellow to all of you guys.
  I have a three-table like below:</p>
  <p>1-The movie table:</p>
  <pre><code>+----+-------------+
  | id | movie_name  |
  +----+-------------+ 
  </code></pre>
  <p>2-The actor table:</p>
  <pre><code>+----+-------------+
  | id | actor_name  |
  +----+-------------+ 
  </code></pre>
  <p>3-The movie_actor table:</p>
  <pre><code>+----------+-------------+
  | movie_id | actor_id    |
  +----------+-------------+
  </code></pre>
  <p>ok, I realize that if I have 20 million movies inside movie table and each movie have 20 actors then The movie_actor table should have 400 million rows and query on this large table will be slow even if I use the index on movie_id and actor id because after a long time the rows in movie_actor will be very very large like 1billion and more.
  then I read about partitioning. but partitioning the movie_actor is a question for me. I want basically two queries on this table:</p>
  <blockquote>
  <p>to get all movies that one actor is acting on:</p>
  </blockquote>
  <p>1-</p>
  <pre><code>`select movie_id from movie_actor where actor_id = 102547;`
  </code></pre>
  <blockquote>
  <p>to get all actors that acting in a movie:
  2-</p>
  </blockquote>
  <pre><code>`select actor_id from movie_actor where movie_id = 200145364;`
  </code></pre>
  <p>but I don't know have and on which column and on what condition I should partition this database to does not affect my queries. Any Body can help me to do this? This is very important to me.this is a test that I create for myself to simulate real-world applications. I know any application about movies does not have 20 million movies. Anybody can help me I will be great full?</p>