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

Mysql – Why is a Full Table Scan Used Instead of an Index Scan for My Query?

I have a query that performs a count operation on a table with a specific condition. I expected that using an index would be enough to speed up the query, but the output from EXPLAIN ANALYZE indicates a full table scan is performed even though there is an index on the column used in the condition.

Here is the query and the EXPLAIN ANALYZE output:

EXPLAIN ANALYZE
SELECT COUNT(*) AS total
FROM galleries
WHERE school_id = 3451;

The EXPLAIN ANALYZE output is:

Aggregate: count(0) (cost=29900.41 rows=21745) (actual time=63.052..63.052 rows=1 loops=1)
    -> Filter: (galleries.school_id = 3451) (cost=27725.92 rows=21745) (actual time=50.530..63.026 rows=28 loops=1)
        -> Covering index scan on galleries using idx_school_id (cost=27725.92 rows=217449) (actual time=0.047..45.998 rows=197797 loops=1)

Despite the index scan being used, I am not seeing the performance improvement I expected. Why is this happening, and how can I improve the query performance?

Her is the table info


CREATE TABLE `galleries` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `video_id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL,
  `file_name` varchar(191) DEFAULT NULL,
  `school_id` varchar(191) DEFAULT NULL,
  `type` varchar(20) DEFAULT NULL,
  `file_type` varchar(20) DEFAULT NULL,
  `meta` text,
  `file_status` tinyint(1) DEFAULT '0',
  `is_processed` tinyint(1) DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `original_name` varchar(191) DEFAULT NULL,
  `creator_id` int DEFAULT NULL,
  `video_size` double(8,2) NOT NULL DEFAULT '0.00',
  `available_resolutions` text,
  `upload_by` varchar(20) DEFAULT NULL,
  `length` int DEFAULT '0',
  `parent_id` int unsigned NOT NULL DEFAULT '0',
  `is_folder` tinyint(1) NOT NULL DEFAULT '0',
  `is_deleted` timestamp NULL DEFAULT NULL,
  `captions` text,
  `drm_meta` text,
  `drm_video_id` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_video_id` (`video_id`),
  KEY `idx_school_id` (`school_id`)
) ENGINE=InnoDB AUTO_INCREMENT=200396 DEFAULT CHARSET=utf8mb3
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Ok, the issue was my school id was a varchar accidentally, when I changed it to be an int, it worked perfectly..


  2. 0

    You are doing school_id = 3451 and school_id is a character field. Because one side of the = is numeric, a numeric comparison will be done, implicitly casting school_id to a number. So it can’t simply look up the ‘3451’ entries in the index, it has to scan the whole index doing the cast on each value, so that values like ‘ 3451’ or ‘3451.0’ or ‘003451’ will be included. Though it can still use the index to find all the possible values of school_id, rather than reading the actual table data, this will only provide a little improvement.

    So if school_id is meant to only store ints, change it to an int type; otherwise do a string comparison:

    school_id = '3451'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search