[Solved] query not using index

EverSQL Database Performance Knowledge Base

query not using index

Can anyone tell me why this query is not using index idx_accessorieslinkid I have created index:

    CREATE INDEX idx_accessorieslinkid ON `accessorieslink` (actionid);

I am new to indexes and trying to understand how they work (and why sometimes they don't!)

sql

 SELECT action.actionid
     FROM
      ACTION 
      LEFT JOIN 
(SELECT 
  SUM(accprice) AS accprice,
  actionid
FROM
  accessorieslink 
GROUP BY actionid) AS accprice 
ON accprice.actionid = action.actionid 
    WHERE action.typeid = '2'
      AND actiondate2 BETWEEN '20130301' AND '20130301' 
    ORDER BY actiondate2 DESC

explain results

    id  select_type  table     type         possible_keys                   key key_len ref rows Extra
    1   PRIMARY      action    index_merge  idx_actiontypeid,idx_actiondate idx_actiondate,idx_actiontypeid  4,5 141    Using intersect(idx_actiondate,idx_actiontypeid); Using         where; Using index; Using temporary; Using filesort  
    1   PRIMARY      <derived2>       ALL   7804                    
    2   DERIVED      accessorieslink  ALL   12175                             Using temporary; Using filesort

How to optimize this SQL query?

The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:

  1. Description of the steps you can take to speed up the query.
  2. The optimal indexes for this query, which you can copy and create in your database.
  3. An automatically re-written query you can copy and execute in your database.
The optimization process and recommendations:
  1. Avoid Subqueries (query line: 7): We advise against using subqueries as they are not optimized well by the optimizer. Therefore, it's recommended to join a newly created temporary table that holds the data, which also includes the relevant search index.
  2. Explicitly ORDER BY After GROUP BY (modified query below): By default, the database sorts all 'GROUP BY col1, col2, ...' queries as if you specified 'ORDER BY col1, col2, ...' in the query as well. If a query includes a GROUP BY clause but you want to avoid the overhead of sorting the result, you can suppress sorting by specifying 'ORDER BY NULL'.
  3. Use Numeric Column Types For Numeric Values (query line: 19): Referencing a numeric value (e.g. 2) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
  4. Use Numeric Column Types For Numeric Values (query line: 20): Referencing a numeric value (e.g. 20130301) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.
The optimized query:
SELECT
        action.actionid 
    FROM
        ACTION 
    LEFT JOIN
        (
            SELECT
                SUM(accprice) AS accprice,
                accessorieslink.actionid 
            FROM
                accessorieslink 
            GROUP BY
                accessorieslink.actionid 
            ORDER BY
                NULL
        ) AS accprice 
            ON accprice.actionid = action.actionid 
    WHERE
        action.typeid = '2' 
        AND actiondate2 BETWEEN '20130301' AND '20130301' 
    ORDER BY
        actiondate2 DESC

Related Articles



* original question posted on StackOverflow here.