1

I need help with a select query.

I have a table with millions of records, and I have used: 

 select * from table where status=1 limit $start,$pagesize

for paging so it loads 25 records per page.

My concern is that when I run the query with thousand of records in the table, the load time is acceptable, but as soon as I run the query with bulk records, the load time increase.

So I tried to find a way, and now I want to show only 2500 latest records from table with limit 0 to 25 .

select * from (select * from table 
where status=1 order by id  limit  0,2500)t  
where status=1 order by id limit $start,$pagesize

but the load time isn't affected.

Can anyone suggest a better way please?

I'm using indexing already and have indexes on table for the status and id fields.

Shadow
  • 33,525
  • 10
  • 51
  • 64
phpdev
  • 89
  • 2
  • 10

2 Answers2

0

LIMIT is difficult to optimize in the case that an offset is specified.

Since you have indexes on id, and you are ORDER BY id, though, you can actually make direct use of the indexes by replacing the offset in your LIMIT with a WHERE:

SELECT * FROM table WHERE status=1 AND id > $lastIDRetrieved LIMIT $pageSize

That is, instead of using (page number * page size) as an offset in LIMIT, you use the last id you received instead, thus making direct use of your index on id.

jbafford
  • 5,528
  • 1
  • 24
  • 37
  • This is not correct. Mysql does not prepare the entire resultset when limit is used, unless there is a having clause, or group by and indexes cannot be used, or the number of records in the resultset is smaller than the limit. None of these conditions are present in this particular case. – Shadow Jan 15 '16 at 06:20
  • @Shadow I'll edit that detail once I find some more suitable language, but the point was more that it can't optimize the query because it doesn't actually know where it can start. – jbafford Jan 15 '16 at 06:22
  • This query should be relatively quick, since the limit is based on the pk. I'm not sure that your point applies at all in this case. – Shadow Jan 15 '16 at 06:26
  • @Shadow The closest the limit optimization chapter comes for LIMIT M,N is `SELECT ... FROM single_table ... ORDER BY **non_index_column** [DESC] LIMIT [M,]N;`. It's still unclear how you would go straight from the offset to an optimization on the PK, because, especially with the extra column in the `where`, it can't turn the offset into a PK id. – jbafford Jan 15 '16 at 06:30
  • @Shadow yes, but it mentions nothing about limit with offset. Limit with offset is pretty much a row_count of (offset + row_count) with the head lopped off after sorting is complete. – jbafford Jan 15 '16 at 06:45
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/100735/discussion-between-jbafford-and-shadow). – jbafford Jan 15 '16 at 06:47
  • I do not agree eith you. See the topic I marked as duplicate. Took me some time to find it. – Shadow Jan 15 '16 at 06:57
0

As my other friend said, yes its difficult to optimize LIMIT. I suggest you to implement two things i hope these will help you.

  1. Add index on status column as:

    ALTER TABLE table ADD INDEX index_name (status);

  2. Make sure id column is Primary Key of table because PK is faster than to other keys.

UPDATE 1:

Use EXPLAIN, and check how many rows are fetching or is query using index properly?

EXPLAIN select * from table where status=1
devpro
  • 16,184
  • 3
  • 27
  • 38