title: Optimize MySQL table structure with millions of records
body: <p>Okay, I am new to MySQL and SQL in general. I use sql in my access databases and aspx web pages but I don't think i'm being very efficient with it. Below is my current Mysql table and my end result. Currently I feel I am doing way too much to get the results i want.</p>
  
  <p>So my question is what is the best way to optimize this table? The table is filled from hundreds of android devices sending the apps they have installed on their devices. I am collecting this info and running a count on how many times they showed up to see which are the most popular. </p>
  
  <p>Right now i have accumulated over 3 million rows and I am exporting this table and importing to access. I then run two queries in access to get my count and export to excel to create the report below.</p>
  
  <p>The unique identifier is the activity and class name combined with /
  That is how i determine my counts</p>
  
  <p>I know this question might be considered a discussion but really to answer the question i would just like to know the structure i should be using in my base table.</p>
  
  <p>I know i don't even have an index setup but not sure which field could do it? </p>
  
  <p>If this question is considered a discussion i will take it down right away.</p>
  
  <p><img src="https://i.stack.imgur.com/pQI8r.png" alt="original table"></p>
  
  <p><img src="https://i.stack.imgur.com/MbPfR.png" alt="End result"></p>
  
  <p>Based on the recommendations i added a index shown below and ran the following query to get the data i needed. Did i add the index correctly? What about a primary key? do i just add an auto generated one?</p>
  
  <pre><code>SELECT COUNT( name ) , CONCAT( userapps.activity,  '/', userapps.class ) 
  FROM userapps
  GROUP BY name
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/eAMp1.png" alt="enter image description here"></p>