Databases
StackOverflow2013
Postsdbo
POHow to optimize a count SQL query on a big table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to optimize a count SQL query on a big table
    primarykey
    data
    text
    <p>I have a big table on mysql (innodb) which contains products assets (13 millions of rows). Here a little schema of my database :</p> <pre><code>product &lt;-many2one-- file_item --one2many--&gt; family --many2one--&gt; download_type </code></pre> <p>The *file_item* table is the big table with millions of rows. I try to count products by download types with the following sql query :</p> <pre><code>select t.name as type, count(p.product_id) as n from file_item p inner join family f on f.id = p.family_id inner join type t on f.id_type = t.id group by t.id order by t.name; </code></pre> <p>There are 3 indexes on *file_item* table:</p> <ul> <li>product_family_idx (product_id, family_id) </li> <li>family_idx (family_id) </li> <li>product_idx (product_id) Explain output :</li> </ul> <pre> +----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+ | 1 | SIMPLE | p | ALL | FAMILY_IDX,PRODUCT_FAMILY_IDX | NULL | NULL | NULL | 13862870 | Using temporary; Using filesort | | 1 | SIMPLE | f | eq_ref | PRIMARY,TYPE_ID | PRIMARY | 4 | MEDIA.p.FAMILY_IDX| 1 | | | 1 | SIMPLE | t | eq_ref | PRIMARY | PRIMARY | 4 | MEDIA.f.TYPE_ID | 1 | | +----+-------------+-------+--------+-----------------------------------+---------+---------+-------------------+----------+---------------------------------+ </pre> <p>The query takes more than 1 hour to return the results. Please how I can optimize the query ?!</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USjuliusdev
    1. USjuliusdev
    plurals
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. USmkk
      1. VTFavorite
    2. VO
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    3. VO
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. This table or related slice is empty.
      1. VTUpMod
    1. COAre the tables indexed well? Please provide schema with indexes and EXPLAIN of that query. Thanks!
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. USWiseguy
    2. COplease give us the output of "explain <yourquery>;"
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. USSpike Gronim
    3. CO@Wiseguy Of course, i added the indexes in the text.
      singulars
      1. POHow to optimize a count SQL query on a big table
      1. USjuliusdev
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload