MySQL Bugs: #100621: Select query is very slow for large table

Bug #100621	Select query is very slow for large table
Submitted:	24 Aug 2020 11:35	Modified:	24 Aug 2020 12:53
Reporter:	Anjali Kulkarni	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S5 (Performance)
Version:	8.0.20	OS:	Windows
Assigned to:		CPU Architecture:	Other (64 bit 4 core)

Description:
I am using mysql server 64 bit 8.0.20. Storage engine is innodb
There is one table Data with following columns
Table Test_Data
ID Primary key varchar(36) default (UUID()) 
MAsterTable1_ID varchar(36) (foreign key reference with Master table 1)
MAsterTable2_ID  varchar(36)  (foreign key reference with Master table 2)
ColumnUID  VARCHAR(200) 
And other columns say column1 to column10
There are indexes set on columns
MAsterTable1_ID,MAsterTable2_ID and ColumnUID part index with length (64)
This table has 29684232 rows Data length is 17 gb index length is also 17 gb.
Select query on this table runs very slow tooks around 60 to 70 sec
One observation is only following select query runs fast
Select ID, MAsterTable1_ID from test_data where MAsterTable1_ID='something'
Explain output shows "using index " in extra column.
Rest of the select query i.e. selecting any other columns query runs slow 
Selecy Columnuid from test_data where MAsterTable1_ID='something' is also slow.
What could be the reason.
One other obervation is insert in this table and select from this table becomes slow as rows/ data increases

 

How to repeat:
repeating will require to insert such huge records in table . table definition  similar to this.

Hi Mr. Kulkarni,

Thank you for your bug report.

However, what you are reporting is not a bug.

When you run a query that should extract many rows, then faster solution is to scan entire table. Hence, the larger the table, the more will query last.

Also, you should look into how is query executed and how is your server configured.

This is all described in our Reference Manual.

Not a bug.