Yii Framework Forum: Clistview Pagination Speed Up - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Clistview Pagination Speed Up Rate Topic: -----

#1 User is offline   jcagentzero 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 14-October 11
  • Location:Philippines

Posted 14 April 2014 - 03:44 AM

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!
Thank you and regards,

jcagentzero

http://www.jundycosmod.com

If you think this post is helpful, please don't hesitate to click + button.
0

#2 User is offline   Keith 

  • Elite Member
  • Yii
  • Group: Moderators
  • Posts: 1,670
  • Joined: 04-March 10
  • Location:UK

Posted 16 April 2014 - 09:04 AM

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.
2

#3 User is offline   jcagentzero 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 58
  • Joined: 14-October 11
  • Location:Philippines

Posted 02 May 2014 - 12:59 AM

View PostKeith, on 16 April 2014 - 09:04 AM, said:

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.
Thank you and regards,

jcagentzero

http://www.jundycosmod.com

If you think this post is helpful, please don't hesitate to click + button.
0

#4 User is offline   LokisHat 

  • Newbie
  • Yii
  • Group: Members
  • Posts: 5
  • Joined: 25-April 14

Posted 15 May 2014 - 11:26 AM

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.
0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users