Title: sql queries casting as binary are slow
Last modified: November 15, 2022

---

# sql queries casting as binary are slow

 *  Resolved [shortcutsolutions](https://wordpress.org/support/users/shortcutsolutions/)
 * (@shortcutsolutions)
 * [3 years, 6 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/)
 * 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](https://wordpress.org/support/users/aaron13100/)
 * (@aaron13100)
 * [3 years, 6 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/#post-16201962)
 * 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](https://wordpress.org/support/users/shortcutsolutions/)
 * (@shortcutsolutions)
 * [3 years, 6 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/#post-16202106)
 * WP docs cover how to make a WP compatible table: [https://codex.wordpress.org/Creating_Tables_with_Plugins](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](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.
    -  This reply was modified 3 years, 6 months ago by [shortcutsolutions](https://wordpress.org/support/users/shortcutsolutions/).
 *  Plugin Author [Aaron](https://wordpress.org/support/users/aaron13100/)
 * (@aaron13100)
 * [3 years, 6 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/#post-16203184)
 * 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](https://wordpress.org/support/users/aaron13100/)
 * (@aaron13100)
 * [3 years, 5 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/#post-16285355)
 * 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.

 * ![](https://ps.w.org/404-solution/assets/icon-256x256.jpg?rev=1610739)
 * [404 Solution](https://wordpress.org/plugins/404-solution/)
 * [Frequently Asked Questions](https://wordpress.org/plugins/404-solution/#faq)
 * [Support Threads](https://wordpress.org/support/plugin/404-solution/)
 * [Active Topics](https://wordpress.org/support/plugin/404-solution/active/)
 * [Unresolved Topics](https://wordpress.org/support/plugin/404-solution/unresolved/)
 * [Reviews](https://wordpress.org/support/plugin/404-solution/reviews/)

 * 4 replies
 * 2 participants
 * Last reply from: [Aaron](https://wordpress.org/support/users/aaron13100/)
 * Last activity: [3 years, 5 months ago](https://wordpress.org/support/topic/sql-queries-casting-as-binary-are-slow/#post-16285355)
 * Status: resolved