Times out – causes huge server load

Can you run these two queries directly in mysql and let me know how long it is taking to execute the queries?
Please run these queries on dev server first and then prod.
Before running the queries, please replace the prefix.

These two queries are used to fetch records on admin page.
Hope you are not using multisite network feature of WordPress.

What are the number of records in the tables : users and user_meta?

SELECT FaUserLogin.*, UserMeta.meta_value, TIMESTAMPDIFF(SECOND,FaUserLogin.time_login,FaUserLogin.time_last_seen) as duration FROM {prefix}_fa_user_logins AS FaUserLogin LEFT JOIN {prefix}_usermeta AS UserMeta ON ( UserMeta.user_id=FaUserLogin.user_id AND UserMeta.meta_key LIKE '{prefix}_capabilities' ) WHERE 1 ORDER BY id DESC LIMIT 20 OFFSET 0;

SELECT COUNT(FaUserLogin.id) AS total FROM {prefix}_fa_user_logins AS FaUserLogin LEFT JOIN {prefix}_usermeta AS UserMeta ON ( UserMeta.user_id=FaUserLogin.user_id AND UserMeta.meta_key LIKE '{prefix}_capabilities' ) WHERE 1;

Thanks

Sorry for slow reply.

1st command:
id column? -> 95976

2nd command:
95995

Thank you

@welshdemon

Please let me know how long it took to execute the queries.
Are you using the latest plugin version 1.7.3?

around 2-3 minutes, while the site is unusable due to server load (error establishing database connection). on a dedicated server with quad core CPU and 32GB RAM.

I do use s2member plugin for my membership website which adds lots of fields to user_meta, maybe that slows it down.

Thanks

If you using the latest plugin version 1.7.3, may be it slows down due to s2member plugin.

I am guessing that most people who need to check login history are running a membership website.
If your plugin does not work with one of the biggest membership plugins, this is not so good.
I have many people on my site all the time so a few minutes of it being offline is no good.
I hope you can look in to it and make it do less queries so it does not bring the server to a halt perhaps?

377,344 items ? ? Current Page 1 of 37,735 Next page? Last page?

Also the amount of results, at 10 entries per page nearly 40,000 pages.

How can we reduce this? Maybe if there was a way to remove entries over a certain age it would help?

@welshdemon In the premium plugin, there is auto delete feature which delete the records older than X days automatically.
The plugin is under testing phase and will be released soon.

Hows the premium version going?

The plugin just took my site offline for 5 mins while it loaded the login history page since I have so many users and records…

Thanks

Premium version is under testing phase.

What is the total number of records in your database table “fa_user_logins”?
I will try to reproduce this issue with same or more number of records.