Hi,
I have a very large database and one of the tables has 100,000+ items. When clicking next and previous, it takes 4-5s which can be tolerated but when clicking last and first it takes 20s.
Do you have any idea?
Thank you in advance!
Hi,
I have a very large database and one of the tables has 100,000+ items. When clicking next and previous, it takes 4-5s which can be tolerated but when clicking last and first it takes 20s.
Do you have any idea?
Thank you in advance!
It sounds like you’re missing a suitable index. Is there an index on the column you’re ordering by?
100,000 items is not that many. With suitable indexes, querying it should be very quick.
I appreciate the reply and it is really true that using the proper index in ordering makes the pagination fast.
I wasn’t able to give the whole situation, the columns used for ordering has VARCHAR type and accepts alphanumeric values. Thus, ordering it shows 1,10,12,2,3 instead of 1,2,3,10,12. I hope somebody has the solution for this.
I’ve had a similar problem recently.
If you can run a SQL command then try this on the table with the speed issues:
SELECT * FROM [yourtablename] PROCEDURE ANALYSE(10, 2000)
This will give you a listing telling you the optimal datatypes and their sizes for each column. As well as a lot of other optimisation info.
For more information you can check the MySQL documentation for the "PROCEDURE ANALYSE()" function.