Long query using cb user list search feature

I am not sure if anyone is aware about it as I can't seem to find it on the forum..

I am using the search function for a user list as a search feature for almost over a million records.. I have indexed it so searches are quite fast..

The only problem is if a user does not enter in a field (empty field) and click search, the query goes on and on.. I have seen such query stretching to a few hundred seconds before I kill it.. Is there a way I can predefine some information in the search criteria to avoid this from happening?

If no criteria is specified it does "List All", which retrieves and displays all records (all users). There's no removing this without either writing a plugin to stop it using the userlist triggers or editing the userlist usage to remove "List All" capability entirely. Alternative is to reduce your userlist to as few specific rows as possible by adding Filters to it, which adds to the WHERE statement.

If no criteria is specified it does "List All", which retrieves and displays all records (all users).

This is going to be bad as I have over a million (close to 2 million actually) record and it is going to burden the server. I saw in my process list that it is taking alot of resources and time to write to the temp table..

There's no removing this without either writing a plugin to stop it using the userlist triggers

I have limited skills so I may not have the skill to write a plugin.. is there a way, we can use auto action to do this?

or editing the userlist usage to remove "List All" capability entirely.

How do I remove the list all capability, and if this is done, is it possible to isolate it to one list only (one particular list ID)..

Is there a way to make the search field mandatory to be filled?

Is is possible to do the search field mandatory for a particular list id? perhaps also using the auto action?

This is going to be bad as I have over a million (close to 2 million actually) record and it is going to burden the server. I saw in my process list that it is taking alot of resources and time to write to the temp table..

Then you need to hire an optimization expert to go through your site and help you with optimizing it if you don't know how to do this your self. I'm not aware of any Joomla site being this large except maybe Joomla.org community site. I've never heard of a CB site being this large either nor have I simulated one this large so this is "uncharted waters" and I've no idea how all of this will behave in such an environment. You'll probably need to use heavy caching, but that's going to result in some pages displaying out of date data until the cache refreshes.

I have limited skills so I may not have the skill to write a plugin.. is there a way, we can use auto action to do this?

Probably not, you need to edit the reference of a variable which CB Auto Actions does not pass as reference.

How do I remove the list all capability, and if this is done, is it possible to isolate it to one list only (one particular list ID).

You need to first edit the default.php CB template file to remove "List All" link. Once done you'd need to edit further into CB to remove it from doing this automatically when no search criteria is available. The best solution is to create a CB plugin that stops it from doing the query if no search criteria then just hide the "List All" link with CSS. We don't support core edits however so I can not help you with editing core code.

Is there a way to make the search field mandatory to be filled?

Not that I am aware.

Is is possible to do the search field mandatory for a particular list id? perhaps also using the auto action?

No, not that I am aware. I guess you can try adding jQuery to the page to stop the form from submitting if no input is completed, but I've no idea how well that'll work.

Remember i asked about editing the moderator user approves page? www.joomlapolis.com/forum/153-professional-member-support/218315-solved-modifying-moderator-approve-members-page

Do you think I can modify the search field? It may look something below:

But this is a check box.. I may have to find the search box and make it compulsory or put a regex to it?

Can you tell me where the codes (user list search fields) will be sitting?

I went to try to change in field manager.. However, you can change the size of search field but not the minimum or maximum characters and also the regex..



It is crazy to see mysql trying to list such a big file..

The search inputs are generated dynamically and aren't static like that HTML. The most you can get to for static HTML is the FORM element and a few hidden elements.

We don't support core edits, I'm sorry but I can't help you in that regard. You really need to implement custom database and server optimization for your site to run properly. A large query shouldn't be a problem.