A faster way to query custom post types with multiple conditions?

In general, custom SQL queries are faster than the WordPress native funtions regardless. The real question is, are these custom SQL queries better, and the simple answer is “NO”. Lets look at a few important points to why not to use custom SQL queries:

• You loose all filters, in the case of WP_Query, you loose pre_get_posts and the posts_* filters

• Maintaining SQL queries are a shlep. WordPress is an ongoing project and there are constant changes being made which affects the db as well. This might negatively affect your SQL queries and can lead to unnecessary debugging work. With functions and classes like WP_Query and get_post_meta() which uses SQL queries, maintaining functionality with the db is done by the core developers, so they do the hard work. You do not even notice anything on your queries when you update to a version where changes was done to the db

• Changing SQL queries usually need a complete rewrite, and if you are not confident with SQL syntax, it can lead to other issues. With a class like WP_Query, you can simply add/remove parameters from the array of arguments as necessary

• You are loosing the complete build-in object cache system, which is there to store data like posts and its relative post data to avoid constant db calls. This cache systems are implemented to improve the performance of a query

meta_query‘s are always slower than any normal query as it involves joining of tables, and the more nested the array of variables become, the slower the query becomes, this goes for normal SQL queries and for WP_Query calls. The advantage of using WP_Query however is, the first time the query is run, it might be slow as the db is hit hard to get the posts matching a query, but once the query is ran, and everything is saved in the posts object cache, the second time the query is ran, posts are retrieved from the cache, and not the db

You can however make use of transients to store custom queries and flush the transients once a week or month or when needed. This will drastically improve performance as the query will only run when the transient is rebuilded. You can flush transients immediately and rebuild them if a post is updated, deleted, undeleted or published by using the transition_post_status hook. If you need to flush transients when post meta are updated, you can look at actions like updated_post_meta. Here is a nice post about that.

As for your edit, never store custom field values as an array. The sad story here is, if you ever want to order or search for specific values in a custom field, it won’t work with serialized data. Store single string values. Also, LIKE is also not desired, because if you search for dot, words like dot, dotcom, and mydot will all be matched which will give undesired results from your query