Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>Indexing the columns in the GROUP BY clause would be the first thing to try, using a composite index. A query such as this can potentially be answered using only the index data, avoiding the need to scan the table at all. Since the records in the index are sorted, the DBMS should not need to perform a separate sort as part of the group processing. However, the index will slow down updates to the table, so be cautious with this if your table experiences heavy updates.</p> <p>If you use InnoDB for the table storage, the table's rows will be physically clustered by the primary key index. If that (or a leading portion of it) happens to match your GROUP BY key, that should speed up a query such as this because related records will be retrieved together. Again, this avoids having to perform a separate sort.</p> <p>In general, bitmap indexes would be another effective alternative, but MySQL does not currently support these, as far as I know.</p> <p>A materialized view would be another possible approach, but again this is not supported directly in MySQL. However, if you did not require the COUNT statistics to be completely up-to-date, you could periodically run a <code>CREATE TABLE ... AS SELECT ...</code> statement to manually cache the results. This is a bit ugly as it is not transparent, but may be acceptable in your case.</p> <p>You could also maintain a logical-level cache table using triggers. This table would have a column for each column in your GROUP BY clause, with a Count column for storing the number of rows for that particular grouping key value. Every time a row is added to or updated in the base table, insert or increment/decrement the counter row in the summary table for that particular grouping key. This may be better than the fake materialized view approach, as the cached summary will always be up-to-date, and each update is done incrementally and should have less of a resource impact. I think you would have to watch out for lock contention on the cache table, however.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow to speed up "select count(*)" with "group by" and "where"?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. UScheduardo
    1. UScheduardo
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COSmaller columns may help: if the table scan is unavoidable, a smaller table will take less time to scan. Perhaps you could post the table structure and some sample data along with the exact query.
      singulars
      1. PO
      1. UScheduardo
 

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