1

I am using AWS RDS MySQL 5.6.38 to store my application data. I have one query which checks wheteher particular user is blocked or not and i am using primary key to check that record. It scans whole table if record does not exist. So to avoid full scan of this table and to check existence of particular record, is there any other method which avoids full scan of table because i wonder if i have 1 million entries in my table. and my table may and may not have that entry in a table which needs to be checked. but if entry exists then it scans only one record because we use primary key.

user3406181
  • 91
  • 1
  • 10
  • so you are looking for a key in a table? – cruxi Mar 26 '18 at 12:38
  • @cruxi We have a key in a table and checking record with primary key only but we may or may not have that record which we are checking in a table. suppose pk is userid and we are checking userid = ? but that userid may or may not be present in a table. If not present then scans whole table. – user3406181 Mar 26 '18 at 12:40
  • 2
    if you have a column with a anny index and you search on it it will not scan the complete table. – Raymond Nijland Mar 26 '18 at 12:46
  • Does this helps: https://stackoverflow.com/questions/8702905/table-scan-and-index-scan-in-sql ? – cruxi Mar 26 '18 at 12:49
  • Suppose this is the query select otherUserId from blocked_following where userId = 3141947830 and otherUserId IN (1899265466) and status = 2 and output of explain command is SELECT TYPE: SIMPLE TABLE: null POSSIBLE KEY: null KEY: null ROWS: null In this query, userId and otherUserId is composite PK. – user3406181 Mar 26 '18 at 12:55
  • This query should not cause a full table scan, even if the optimizer is for some reason not handling the `IN (...)` ideally... and if it is, you should see something like `Using where` in the `Extra` column, which you didn't mention. What are the data types? Why are you using `IN()` with single scalar? What happens if you remove status = 2? – Michael - sqlbot Mar 27 '18 at 00:28

0 Answers0