Why doesn’t custom mysql query return results? Is syntax correct?

You’re including the table prefix, but $wpdb takes care of that.

Change:

$query = $wpdb->prepare( "SELECT * FROM $wpdb->wp_terms INNER JOIN $wpdb->wp_term_taxonomy ON ($wpdb->wp_terms.term_id = $wpdb->wp_term_taxonomy.term_id) WHERE $wpdb->wp_terms.name LIKE %s AND $wpdb->wp_terms.count > 0", $wpdb->esc_like($keyword) . '%');

To:

$query = $wpdb->prepare( "SELECT * FROM $wpdb->terms INNER JOIN $wpdb->term_taxonomy ON ($wpdb->terms.term_id = $wpdb->term_taxonomy.term_id) WHERE $wpdb->terms.name LIKE %s AND $wpdb->terms.count > 0", $wpdb->esc_like($keyword) . '%');

(double-check in case I missed any)