[ic] Customers.HTML Crashes With Large UserDB

[interchange-users@interchange.redhat.com (Ed LaFrance)]

At 08:09 PM 5/23/2002 -0500, you wrote:
>Hi -
>
>I'm currently running IC 4.8.4 on FreeBSD with MySQL.
>
>When I access /admin/customer.html, I get an Internal Server Error.
>Once upon a time, this wasn't a problem; however, we have almost 15k 
>customers in our userdb now.
>
>After some creative MySQL logging, I figured out that IC is passing the 
>following query to MySQL when customer.html loads:
>"SELECT * FROM userdb"
>
>Each time I load the customer.html page, IC is requesting all 15,000 records.
>I think that IC waits so long for the query to finish that it sees the 
>instance as a "spurious process" and kills it before it displays the page 
>-- hence the 505 Internal Server Error.
>
>Here's the code snipped that causes the problem.
>
>I tried adding a couple result limiting options to the search, but IC 
>doesn't seem to apply them when it translates the search into an SQL query.
>
>[search-region more=1 search="
>                 fi=userdb
>                 ml=25
>                 mm=25
>                 md=1
>                 st=db
>                 [if cgi mv_like_spec]
>                     [cgi ui_text_qualification]
>                 [elsif cgi ui_text_qualification]
>                     se=[cgi ui_text_qualification]
>                 [/elsif]
>                 [else]
>                     co=yes
>                     sf=inactive
>                     se=1
>                     op=[scratch active_sense]
>                 [/else]
>                 [/if]
>                 [if cgi ui_sort_field]
>                     tf=[cgi ui_sort_field]
>                     to=[cgi ui_sort_option]
>                 [else]
>                     tf=0
>                 [/else]
>                 [/if]
>                 [scratch start_at][set start_at][/set]
>                 rf=username,company,lname,fname,city,state,country
>"
>]
>
>Any advice would be very helpful...  :)

I've seen similar behavior with some clients who have large userdb tables. 
You might want to consider modifying admin/customer.html to dispense with 
the default listing of all active customers in favor of a purely 
search-driven listing. This could mean that the admin must enter a keyword 
or two in the "Limit with search" box to get a listing. You could also do a 
series of click-based searches linked to an alphabetized index across the 
top of the page (A B C D etc).

In other words, the page needs to be redesigned to optimize it for a large 
customer base.

- Ed L.


===============================================================
Increase profits from your Interchange store... FREE!
http://www.newmediaems.com/cgi-bin/nm/software_fus.html
===============================================================
New Media E.M.S.               Software Solutions for Business
463 Main St., Suite D          eCommerce | Consulting | Hosting
Placerville, CA  95667         edl@newmediaems.com
(530) 622-9421                 http://www.newmediaems.com
(866) 519-4680 Toll-Free       (530) 622-9426 Fax
===============================================================