Magento2 – How to Get All Orders from Database Tables

Option 1. Multiple queries - Not the best one

$attributeSetId = 4; //replace with own attribute set.
$productIds = Mage::getModel('catalog/product')->getCollection()
              ->addAttributeToSelect('attribute_set_id', $attributeSetId)
              ->getAllIds();

Now in $productIds you have all the products ids from attribute set 4. If this is an empty array then it's clear you will not have any orders for this attribute set and you don't need to go on.

If it's not empty....

$orderItsms = Mage::getModel('sales/order_item')->getCollection()
        ->addFieldToFilter('product_id', array('in'=>$productIds));

$orderIds = array();
foreach ($oderItems as $item) {
    $orderIds[$item->getOrderId()] = 1;
}
$orderIds = array_keys($orderIds);

now get the order objects.

$orders = Mage::getModel('sales/order')->getCollection()
        ->addFieldToFilter('entity_id', array('in'=>$orderIds));

I'm sure there is a way to optimize the queries that run, but this should work.

Option 2. With a query - if you need a fast look in the db
And here is a query, just for the fun of it.

SELECT 
    o.*
FROM
    sales_flat_order o
LEFT JOIN
        sales_flat_order_item i 
        ON o.entity_id = i.order_id
LEFT JOIN
        catalog_product_entity p
        ON i.product_id = p.entity_id
WHERE
    p.attribute_set_id = 4 
GROUP BY o.entity_id

Option 3. One select - clean.
Based on the query above I realized you can get the list of orders with a single select built the magento way.

$attributeSetId = 4;
$collection = Mage::getModel('sales/order')->getCollection();
$collection->join(
    array('i'=>'sales/order_item'),
    'order_id = entity_id',
    array()
);
$collection->join(
    array('p'=>'catalog/product'),
    'i.product_id = p.entity_id',
    array()
);
$collection->getSelect()->where('p.attribute_set_id = ?', $attributeSetId);
$collection->getSelect()->group('main_table.entity_id');

Then just do foreach loop on the $collection and you will have the orders.

This question has been asked before, please see this: https://stackoverflow.com/questions/12205714/list-of-tables-to-safely-truncate-in-magento

This should help you.

EDIT: For posterity sake:

When you log an issue with Magento support and they ask you to provide a database dump, the script they give you dumps the schema only for the following tables:

core_cache
core_cache_option
core_cache_tag
log_customer
log_quote
log_summary
log_summary_type
log_url
log_url_info
log_visitor
log_visitor_info
log_visitor_online
enterprise_logging_event
enterprise_logging_event_changes
index_event
index_process_event
report_event
report_viewed_product_index
dataflow_batch_export
dataflow_batch_import

If Magento support doesn't need the contents of these tables to resolve issues, it would be a safe assumption that they can be safely truncated.

The catalog_product_flat_* tables and catalog_category_flat_* tables can also be truncated as a reindex will re-populate them.

A user can add entries to the core_url_rewrite table manually from the back end and I wouldn't like to guarantee that two products pr categories with identical URL keys will always have the same URLs after truncating core_url_rewrite. It's not one I'd rely on being able to truncate safely.

Source - Jim OHalloran