WordPress.org

Support

Support » Plugins and Hacks » [Resolved] participants-database.php list breaks above 20K entries, frontend unaffected

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

  • 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/

Viewing 10 replies - 1 through 10 (of 10 total)
  • 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.

    Plugin Author xnau webdesign
    Participant

    @xnau

    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.

    Plugin Author xnau webdesign
    Participant

    @xnau

    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.

    Plugin Author xnau webdesign
    Participant

    @xnau

    PayBas,

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

    I will post it in a few minutes.

    Plugin Author xnau webdesign
    Participant

    @xnau

    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.

    Plugin Author xnau webdesign
    Participant

    @xnau

    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.

Viewing 10 replies - 1 through 10 (of 10 total)
  • The topic ‘[Resolved] participants-database.php list breaks above 20K entries, frontend unaffected’ is closed to new replies.