title: How to use multi-index for better performance in mysql also into python
body: <p>I have a table in MySQL that consists of more than 400 million rows. It looks something like this:</p>
  
  <pre><code>device    date       type .....
    A     2019-06-16    1
    A1    2019-06-20    2 
    A2    2019-10-16    3
    B     2019-06-16    3
  </code></pre>
  
  <p>Currently when I run show index query it outputs:</p>
  
  <pre><code>Table         Non_unique     key_name       seq_in_index    col_name   Collation    Cardinality   Index_type
  device_table      1      table_device_date      1             device       A           6444       BTREE
  device_table      1      table_device_date      2             date         A           418571     BTREE
  </code></pre>
  
  <p>Since my data is too large, I want to call day by day in python then append each days. (Calling month of data in python is not working, and I do not want to scale up).</p>
  
  <p>so I want to call one day worth of data in most efficient way.</p>
  
  <pre><code>Explain select * from device_table
  where date between DATE('2019-10-01') AND DATE('2019-10-02');
  </code></pre>
  
  <p>which outputs</p>
  
  <pre><code>Id    select_type table         type       rows      Extra
  1         simple  device_table   All       400mil   Using Where
  </code></pre>
  
  <p>I have index on date column, why is it not using it in the above query? </p>
  
  <p>Also what does seq_in_index mean in show index query?</p>
  
  <p>Thanks.</p>
  
  <p>EDIT:
   Thanks to EdmCoff, Akina. </p>
  
  <p>From their help, if I've understood correctly. Since my table's indexing is in order of [device, date] I cannot go straight and look for [date] so in order to leverage date index, need to do it through device.</p>
  
  <p>so to do that, I do something like</p>
  
  <pre><code>select * from device_table
  where 
    device = 'A' and
    date between DATE('2019-10-01') and DATE('2019-10-02');
  </code></pre>
  
  <p>and It seems to work perfectly.</p>
  
  <p>Only now, I have about 7000 different device and need to get date from 2019-10-01 ~ 2019-12-01.</p>
  
  <p>My approach right now is by using python (using pymysql), </p>
  
  <pre><code>date_range = pd.date_range(start='2019-10-01', end='2019-10-10', freq='1d').strftime('%Y-%m-%d')
  
  for device_id in device_list:
      for i in range(1, len(date_range)):
          query = f"""
          select * from device_table
          where
            device = '{device_id}' and
            date between DATE('{date_range[i-1]}') and DATE('{date_range[i]}');
          """
  
          df = pd.read_sql(query, con=conn)
  
          final_df.append(df, sort=False, ignore_index=True)
  </code></pre>
  
  <p>Note: device_list = ['A', "B", ....etc]</p>
  
  <p>Would there be another way either by changing indexing or other?</p>