optimizing a query that is always scan full table

Question

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;

Answer 1

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);