sql queries casting as binary are slow

  • Resolved shortcutsolutions

    (@shortcutsolutions)


    1 year, 5 months ago

    Using mysql’s cast() function breaks index usage and ultimately slows those queries down substantially. Performance improves greatly by just doing string comparisons. Doing select foo from bar where cast(baz as binary) causes all rows to be retrieved and cast for comparison.

    Suggestions:

    1. Remove the ‘cast as binary’ logic completely.
    2. If you insist on using binary searches, do the cast conversion at insert time and only use cast() on the WHERE clause.

Viewing 4 replies - 1 through 4 (of 4 total)
  • Plugin Author Aaron

    (@aaron13100)

    1 year, 5 months ago

    The casts are there to avoid “Illegal mix of collations” errors. Do you know how I can avoid these and avoid using ‘cast’ ? Or where and how specifically do you think I should change the sql?

    thanks

    Thread Starter shortcutsolutions

    (@shortcutsolutions)

    1 year, 5 months ago

    WP docs cover how to make a WP compatible table: https://codex.wordpress.org/Creating_Tables_with_Plugins

    example: $charset_collate = $wpdb->get_charset_collate();

    WP gives you a function to determine the correct collation when you create a table. For existing tables you can use ALTER TABLE to change it.

    https://dev.mysql.com/doc/refman/8.0/en/charset-table.html

    Those errors should only happen in cases where the table specified a different collation, or if the wordpress collate differs from the server default and no value was given when creating the table.

    Plugin Author Aaron

    (@aaron13100)

    1 year, 5 months ago

    The tables where the collation don’t match are the wordpress tables, like wp_posts. Some users have changed their collations to something strange (I remember seeing swedish). So I created some code to read the collations of the wp_posts table and then update all of the collations of the 404solution tables to match whatever that collation is. You can see that code in DatabaseUpgradesEtc.php -> function correctCollations(). It does look like that was done after I added the “cast” stuff though. So maybe the casts aren’t necessary anymore.

    It will take some time to try removing the casts and retesting it though. It’s working now so I’m not sure when I might get to that.

    Plugin Author Aaron

    (@aaron13100)

    1 year, 4 months ago

    Everything seems to be working fine after removing the casts.

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘sql queries casting as binary are slow’ is closed to new replies.