Members lists takes forever to load

I opened a trouble ticket about 10 hours ago with no response yet.

We have 45,000 members in our database, which we imported to CB, but when you click on the list from frontend, it takes 15-60 sec to load and maxs out the cpu.
I have all the info in trouble ticket, please advise

This is the reply you got on your ticket:

Hi and thanks for contacting us.

Depending on your users-list, your database may need some optimizations (adding tailored indexes to your database allowing your filtering/sorting to run fast, increasing default database caches and so on).

Usually, your hoster or web-developer can help you for that.

We are here to help you !

Best Regards,
Nick

At this point if you need assistance, perhaps you can post information of the user list you created and even turn on joomla debug to see the actual query statements it produces.

This way you can see if the SQL statements need extra indexes or if something else is wrong.

thanks for the reply.
I did increase the dabase cache and made few teaks to mysql config, but it did not help.
From admin it loads fast without any issue, however from frontend it takes for ever to load.
In the ticket i pass all the server info, please take a look and advise us on what to do.
Thanks in advance

Please post information about the user list you have created here.

tbl_comprofiler_lists.sql
I'm not sure what you mean by user list, please clearify, i have attached tbl_comprofiler_list in this post.
for some reason it wont let me attache a file, bellow is the sql export.
If you wish I can provide you with server access so you can take a look at it, we need to resolve this, everything else just waiting for this to be resolved.
thanks in advance


--
-- Database: `AghDB`
--

--

---

--
-- Table structure for table `tbl_comprofiler_lists`
--

CREATE TABLE IF NOT EXISTS `tbl_comprofiler_lists` (
`listid` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(255) NOT NULL DEFAULT '',
`description` mediumtext,
`published` tinyint(1) NOT NULL DEFAULT '0',
`default` tinyint(1) NOT NULL DEFAULT '0',
`viewaccesslevel` int(10) unsigned NOT NULL DEFAULT '0',
`usergroupids` varchar(255) DEFAULT NULL,
`useraccessgroupid` int(9) NOT NULL DEFAULT '18',
`sortfields` varchar(255) DEFAULT NULL,
`filterfields` mediumtext,
`ordering` int(11) NOT NULL DEFAULT '0',
`col1title` varchar(255) DEFAULT NULL,
`col1enabled` tinyint(1) NOT NULL DEFAULT '0',
`col1fields` mediumtext,
`col2title` varchar(255) DEFAULT NULL,
`col2enabled` tinyint(1) NOT NULL DEFAULT '0',
`col1captions` tinyint(1) NOT NULL DEFAULT '0',
`col2fields` mediumtext,
`col2captions` tinyint(1) NOT NULL DEFAULT '0',
`col3title` varchar(255) DEFAULT NULL,
`col3enabled` tinyint(1) NOT NULL DEFAULT '0',
`col3fields` mediumtext,
`col3captions` tinyint(1) NOT NULL DEFAULT '0',
`col4title` varchar(255) DEFAULT NULL,
`col4enabled` tinyint(1) NOT NULL DEFAULT '0',
`col4fields` mediumtext,
`col4captions` tinyint(1) NOT NULL DEFAULT '0',
`params` mediumtext,
PRIMARY KEY (`listid`),
KEY `pub_ordering` (`published`,`ordering`),
KEY `default_published` (`default`,`published`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `tbl_comprofiler_lists`
--

INSERT INTO `tbl_comprofiler_lists` (`listid`, `title`, `description`, `published`, `default`, `viewaccesslevel`, `usergroupids`, `useraccessgroupid`, `sortfields`, `filterfields`, `ordering`, `col1title`, `col1enabled`, `col1fields`, `col2title`, `col2enabled`, `col1captions`, `col2fields`, `col2captions`, `col3title`, `col3enabled`, `col3fields`, `col3captions`, `col4title`, `col4enabled`, `col4fields`, `col4captions`, `params`) VALUES
(2, 'Members List', '<p>my Description</p>', 1, 1, 1, '1, 2, 3, 4, 5, 6, 7, 8', -2, '`username` ASC', '', 0, 'Image', 1, NULL, 'Username', 1, 0, '42', 0, 'Other', 1, '26|*|28|*|27', 1, '', 0, NULL, 0, 'list_search=1\nlist_compare_types=0\nlist_limit=\nlist_paging=1\nhotlink_protection=0');

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Please enable debug mode and maximum error reporting within Joomla global configuration. Once done view the userlist to see if there are any fatal errors that could be slowing down the page. If none review the query debugging at bottom of the page and find the queries to _comprofiler_lists and provide them here. You're going to also need to probably contact a database specialist and make some optimizations as querying over 40,000 rows at once is not going to be fast.