Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – optimizing a query that is always scan full table

we have in our system a query that supposed to filter reports based on some factors
the query is indexed , and when use explain it shows like it uses the indexes but the row numbers are the same amount that the table has , like if the scan is useless

[tag:original query] :

SELECT COUNT(*)  FROM p_rpts r WHERE
                                ( (r.to_player_id='191717' AND r.delete_status!=1) OR (r.from_player_id='191717' AND r.delete_status!=2) )  AND delete_status!=3;

what I tried so far :

  SELECT COUNT(*)
  FROM p_rpts AS r
  WHERE r.delete_status != 3 AND (
  (r.to_player_id = '191717' AND r.delete_status != 1) OR 
  (r.from_player_id = '191717' AND r.delete_status != 2)
  );


  SELECT COUNT(*) FROM (
  SELECT 1 FROM p_rpts AS r WHERE r.to_player_id = '191717' AND r.delete_status NOT IN (1, 3)
  UNION ALL
  SELECT 1 FROM p_rpts AS r WHERE r.from_player_id = '191717' AND r.delete_status NOT IN (2, 3)
   ) AS combined;



  SELECT COUNT(*)
  FROM p_rpts AS r FORCE INDEX (idx_player_status)
  WHERE 
  (r.delete_status != 3) AND
  (
    (r.to_player_id = '191717' AND r.delete_status != 1) OR
    (r.from_player_id = '191717' AND r.delete_status != 2)
  );



  SELECT COUNT(*)
  FROM p_rpts r
  WHERE 
  (r.delete_status != 3) AND
  (
    (r.to_player_id = '191717' AND r.delete_status != 1) OR
    (r.from_player_id = '191717' AND r.delete_status != 2)
  );

I tried many changing in the query and indexes but nothing worked

update using union :

SELECT COUNT(*)
FROM (
    SELECT r.id
    FROM p_rpts AS r
    WHERE r.to_player_id = '191717' AND r.delete_status != 1 AND r.delete_status != 3

    UNION

    SELECT r.id
    FROM p_rpts AS r
    WHERE r.from_player_id = '191717' AND r.delete_status != 2 AND r.delete_status != 3
) AS combined_results;
Tags:

2

Answers


  1. 0

    Do your UNION DISTINCT (the last one you list) with

    INDEX(to_player_id,   delete_status, id),
INDEX(from_player_id, delete_status, id)

    The query will do two range scans (not table scans).

    How many values can delete_status take on? If it only (1,2,3), then use DELETE_STATUS = 2 for the first one and =1 for the second one. That will make it more efficient.

    Login or Signup to reply.
  2. 0

    First of all: A full table scan is not per se bad. It is quite often the fastest approach. Walking though an index instead makes sense, when only a very small part of the table is affected.

    Let’s assume that this is the case; the criteria is true for only a small part, maybe 0.1%, of the table. Then the following applies:

    As has been mentioned in the request comments: MySQL is not good at optimizing queries with OR conditions. The solution to this is using UNION:

    SELECT COUNT(*) 
FROM 
(
  SELECT * FROM p_rpts WHERE to_player_id = '191717' AND delete_status NOT IN (1, 3)
  UNION
  SELECT * FROM p_rpts WHERE from_player_id = '191717' AND delete_status NOT IN (2, 3)
) combined;

    If it is guaranteed that a row cannot have the same to_player_id and from_player_id, then you can use UNION ALL instead, which is one of your tried alternatives. In that case you just want to add two counts, however, which you can make obvious with the following query:

    SELECT 
  (SELECT COUNT(*) FROM p_rpts WHERE to_player_id = '191717' AND delete_status NOT IN (1, 3))
  +
  (SELECT COUNT(*) FROM p_rpts WHERE from_player_id = '191717' AND delete_status NOT IN (2, 3)
;

    Now how to access the data via an index? The most appropriate indexes would be:

    create index idx1 on p_rpts (to_player_id) where delete_status NOT IN (1, 3);
create index idx2 on p_rpts (from_player_id) where delete_status NOT IN (2, 3);

    MySQL, however, does not support partial indexes where a WHERE clause reduces the index content. So the most appropriate indexes in MySQL are

    create index idx1 on p_rpts (to_player_id, delete_status);
create index idx2 on p_rpts (from_player_id, delete_status);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search