understanding mysql limit with non indexed

Question

i have this query which is very simple but i dont want to use index here due to some constraints.

so my worry is how to avoid huge load on server if we are calling non indexed item in where clause.

the solution i feel will be limit. i am sure of having data in 1000 rows so if i use limit i can check the available values.

SELECT * 
from tableA 
where status='1' and student='$student_no' 
order by id desc 
limit 1000

here student column is not indexed in mysql so my worry is it will cause huge load in server

i tried with explain and it seems to be ok but problem is less no of rows in table and as u know mysql goes crazy with more data like millions of rows.

so what are my options ??

i should add index for student ?? if i will add index then i dont need 1000 rows in limit. one row is sufficient and as i said table is going to be several millions of rows so it requires lot of space so i was thinking to avoid indexing of student column and other query is 1000 row with desc row should not cause load on server as id is indexed.

any help will be great

Are you using LIMIT just to let the database "search" in the first 1000 rows ? In other words are you using LIMIT just for performance ? Just for clarification — EEAH, Jun 23 '20 at 20:47
@EEAH yes. if i wont use limit it will search million of data — sujara, Jun 23 '20 at 20:51
this code is vulnerable to **sql injection** so use **prepared statements with parameters** see https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php — nbk, Jun 23 '20 at 20:51
@sujara What happens if it didn't find any record in those 1000 first rows ? — EEAH, Jun 23 '20 at 20:53
mysql would still search all rows, to find a match, an index on Status and stundent should held. so try it — nbk, Jun 23 '20 at 20:54

score 0 · Accepted Answer · answered Jun 23 '20 at 21:01

You say:

but i dont want to use index here due to some constraints...

and also say:

how to avoid huge load on server...

If you don't use an index, you'll produce "huge load" on the server. If you want this query to be less resource intensive, you need to add an index. For the aforementioned query the ideal index is:

create index on tableA (student, status, id);

This index should make your query very fast, even with millions of rows.

EEAH · Answer 2 · 2020-06-23T21:08:19.607

LIMIT 100 doesn't force the database to search in the first 100 rows. It just stop searching after 100 matches are found.

So it is not used for performance.

In the query below

SELECT * 
from tableA 
where status='1' and student='$student_no' 
order by id desc 
limit 1000

The query will run until it finds 1000 matches.
It doesn't have to search only the first 1000 rows

So this is the behaviour of the above query:

int nb_rows_matched = 0;
while (nb_rows_matched < 1000){
  search_for_match();
}

understanding mysql limit with non indexed

2 Answers2