MySQL - Count the total number of a row without `Full Table Scans` and `WHERE` a condition

Asked May 07 '19 at 08:08

Active May 07 '19 at 08:36

Viewed 688 times

I am using the following query to fetch a total number of rows from MySQL but it used Full table scans. I just want to avoid full table scans.

SELECT COUNT(id) as total_rows FROM <table_name>;

Please consider that I don't want to use the WHERE condition. id is my PK. This link may help you for better understanding https://drive.google.com/open?id=1nvyUHkzjWlCji8rZoH0aMq9pqSSDMqJO.

Is there any way to fetch a total number of rows without Full table scans?

edited May 07 '19 at 08:36

asked May 07 '19 at 08:08

Pinal Patel

Query has to count every row so a full table scan is appropriate - why is this a problem for you? – P.Salmon May 07 '19 at 08:10
1

Possible duplicate of [MySQL: Fastest way to count number of rows](https://stackoverflow.com/questions/5060366/mysql-fastest-way-to-count-number-of-rows) – kiks73 May 07 '19 at 08:11
@P.Salmon - I just want to optimize my query. Plz tell me is it possible without full table scans? – Pinal Patel May 07 '19 at 08:15
@PinalPatel Is `id` your PK? could you show us your execute plan? – D-Shih May 07 '19 at 08:16
@P.Salmon Yes, `id` is my PK & I've edited my question for better understanding. – Pinal Patel May 07 '19 at 08:28
The query in the image is not the same as the query in the question - i'm not suggesting the difference is substantive but still..and read this https://stackoverflow.com/questions/1697137/countid-vs-count-in-mysql – P.Salmon May 07 '19 at 08:30
My fastest way of getting total rows from a table is using `EXPLAIN SELECT * FROM Table` .. – FanoFN May 07 '19 at 08:36
@P.Salmon I've edited the image. Plz can you check? – Pinal Patel May 07 '19 at 08:37
The image shows the query is doing a full index scan? – P.Salmon May 07 '19 at 09:01
P.Samlo Is there any difference between the full index scan & full table scan? – Pinal Patel May 07 '19 at 09:13
I know about table scan but don't know about index scan – Pinal Patel May 07 '19 at 09:13
You should research mysql full index scan vs full table scan. I have a feeling your query is optimized but I guess you could check by removing the index and trying it. – P.Salmon May 07 '19 at 11:01
@P.Salmon Thank you so much for giving me the right guidance! – Pinal Patel May 08 '19 at 04:31

MySQL - Count the total number of a row without `Full Table Scans` and `WHERE` a condition

0 Answers0