Check efficiency of PHP code that will query over 8500 orders

I am querying all orders from a WordPress database that uses the WooCommerce Bookings plugin. I want to display upcoming bookings, and there is a potential to have every hour within 2015 to be booked; which means that I might query over 8700 orders.

I'm new to PHP and did most of this following code myself. I'm not sure if there is a more efficient way of writing this code. I don't want my code to hinder site performance.

```
get_results("SELECT tzYeb_2_posts.* FROM tzYeb_2_posts INNER JOIN tzYeb_2_term_relationships ON (tzYeb_2_posts.ID = tzYeb_2_term_relationships.object_id) WHERE 1=1 AND ( tzYeb_2_term_relationships.term_taxonomy_id IN (11) ) AND tzYeb_2_posts.post_type = 'shop_order' AND ((tzYeb_2_posts.post_status = 'publish')) GROUP BY tzYeb_2_posts.ID ORDER BY tzYeb_2_posts.post_date DESC");

$time_array = array();
$fname_array = array();
$church_array = array();
$city_array = array();
$state_array = array();

$new_array = array();

foreach ($results as $result) {
$order_items = $wpdb->get_results("SELECT order_item_id, order_item_name, order_item_type
FROM {$wpdb->prefix}woocommerce_order_items
WHERE order_id = " . $result->ID . "
AND order_item_type IN ('line_item')
ORDER BY order_item_id");

foreach ($order_items as $order_item) {
$item_meta = $wpdb->get_results("
SELECT *
FROM {$wpdb->prefix}woocommerce_order_itemmeta
WHERE order_item_id = " . $order_item->order_item_id . "
");

$meta_array = objectToArray($item_meta);

$timestamp = "";
$date = "";
$time = "";
$fname = "";
$church = "";
$city = "";
$state = "";

f

The biggest performance issue is probably here:

foreach( $order_items as $order_item ) {
   $item_meta = $wpdb->get_results("
       SELECT  *
       FROM        {$wpdb->prefix}woocommerce_order_itemmeta
       WHERE   order_item_id = " . $order_item->order_item_id . "
   ");
   // ...
}

If you have 1000 order items, you will run a 1000 queries. You could change this to a single query (with a different WHERE condition) to return 1000 records, that will make a big difference in performance.

Another important thing here is to avoid SELECT queries. It's better to name explicitly the columns you need so you don't select stuff you will throw away anyway. It will also make your implementation somewhat more robust, because the columns you need will always appear in the same expected order, even if you change the schema and reorder the columns. As a general rule of thumb, you should never use SELECT queries.

Another thing that can improve performance is using prepared statements. Instead of queries like this:

"SELECT x FROM sometable WHERE id = " . $the_id . "

you should use something like this:

$wpdb->query($wpdb->prepare("SELECT x FROM sometable WHERE id = %d", $the_id))

This form protects you from SQL injection attacks because the placeholders will be checked for type, and prepared statements can be compiled and reused by the database.

Finally, there are some coding style issues: the original post had too wide indentation on the left. You also have a very long SQL statements on one line, which is really hard to read, as it forces me to scroll to the right. It's good to make code easy to read for others.

StackExchange Code Review Q#56678, answer score: 8