participants-database.php list breaks above 20K entries, frontend unaffected

Never mind. Seems my error logging wasn’t configured correctly:

[04-Dec-2012 17:44:50 UTC] PDb_List_Admin::initialize list query= SELECT * FROM wp_participants_database ORDER BY date_updated desc
[04-Dec-2012 17:44:52 UTC] PHP Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 77 bytes) in /xxx/wp-includes/wp-db.php on line 1449

Seems this plugin will not be able to meet my needs after all :(. That’s a shame, because I really like it.

PayBas,

Yes, I haven’t gone into optimizing the code for very large databases.

You could try increasing the memory allocation for PHP by editing the ‘memory_limit’ setting on your PHP initialization.

Yeah I could try it, but since with 256MB php memory is barely enough for 50% of the database, it would need to be increased above 512MB, maybe 768MB. My host won’t allow that :D.

Anyway, since the list view from the shortcode doesn’t seem to be as resource intensive, I’ll try to see how the query differs from the backend list.

This is good feedback…it could be that I just need to optimize that one query in the admin…will look into that. If you have any insights on how that might be done, I’d love to hear it.

I’ll get back to you when I find something.

For now, maybe an idea for the documentation:

I don’t know whether it’s specific to my server setup, but when importing CSV files with dates, it’s important to format all dates as yyyy/mm/dd or else it will not get converted to unix timecode properly. Additionally, all date columns should have an int(11) data type or else sorting query results by date will not work properly.

In my case, I used BIGINT(20), since my records date back to 1828

Well I’ve taken a look at the queries and there is definitely a difference.

The front-end: SELECT only the columns to display
The back-end: SELECT * and only display the columns as requested

So the back-end will use way more memory than the front-end. This doesn’t mean that the front-end is without problems. But it does mean that the front-end can potentially handle more records without exploding.

PayBas,

OK, I found it…I think this will solve it for you.

I will post it in a few minutes.

OK, here’s the edit:

In classes.PDb_List_Admin.class.php on line 111 find:
self::$num_records = count( $wpdb->get_results( self::$list_query, ARRAY_A ) );

Change this to:
self::$num_records = $wpdb->get_var( str_replace('*', 'COUNT(*)', self::$list_query) );

This way, it’s not loading the entire database to get the count.

Works great:

List Participants: 39162 records found, sorted by: Date Updated.

The back-end list also feels much quicker now. 5/5 stars!

The only thing I now need are more advanced filtering option for the front end [pdb_list] with operators like in the admin panel (contains, is, greater than, etc.) and this plugin would be just about perfect for me.

On the date fields, I am doing a CAST( date AS SIGNED) so the comparisons will work as expected. Your suggested change shouldn’t break that.

Date parsing is kinda weird with PHP, it does like slashes if you’re not using the standard American format.

I appreciate these comments…I’m adding them to my issues list.