WordPress.org

Ready to get started?Download WordPress

Forums

Participants Database
[resolved] participants-database.php list breaks above 20K entries, frontend unaffected (11 posts)

  1. PayBas
    Member
    Posted 1 year ago #

    First of all, major props for the plugin, it works great so far.

    I have a database with about 39.000 records. And I want them to be sortable and presentable. This plugin seemed perfect for my needs. But uploading almost 40K in a single CSV is too much, so i plit it up in 10 CSV uploads. Every time, around the 6th split upload (between 20K and 25K), I notice that the list as normally displayed in the wp-admin backend nog longer works at all.

    No PHP errors, no MySQL errors... so I don't know what the real problem is. The strangest thing is that the [pdb_list] shortcode seems completely unaffected.

    http://wordpress.org/extend/plugins/participants-database/

  2. PayBas
    Member
    Posted 1 year ago #

    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.

  3. xnau
    Member
    Plugin Author

    Posted 1 year ago #

    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.

  4. PayBas
    Member
    Posted 1 year ago #

    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.

  5. xnau
    Member
    Plugin Author

    Posted 1 year ago #

    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.

  6. PayBas
    Member
    Posted 1 year ago #

    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

  7. PayBas
    Member
    Posted 1 year ago #

    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.

  8. xnau
    Member
    Plugin Author

    Posted 1 year ago #

    PayBas,

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

    I will post it in a few minutes.

  9. xnau
    Member
    Plugin Author

    Posted 1 year ago #

    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.

  10. PayBas
    Member
    Posted 1 year ago #

    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.

  11. xnau
    Member
    Plugin Author

    Posted 1 year ago #

    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.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic