I have tested it with 450K products.
The backend product browsing is improved. It takes 13-15 seconds to jump to next page previously but now only 4-5 seconds
The frontstore home page: It takes 1-2 minutes to load the home page previously but now 30 seconds (after clear all the caches and reload it).
The categories / products listing page : It takes 15-20 seconds to load the page previously but now 12-14 seconds.
browsing page by page in a category (jump to next page): It takes 15-20 seconds to load the page previously but now also 12-14 seconds.
However, open the product listing page in a category where it has 10-20pcs of products is the same time as the category where it has 450K products....
open product detail page : no change, it takes 4-6 seconds to open the page.
In fact, for the front store, it was failed to load the home page after add the new indexes to the tables. (I have uploaded abantecart v.2 /admin, /core to overwrites my abantecart v.1.2.9 but forget to overwrite the storefront.) The error is http 500 error and find memory exhausted in error log. I have increased memory_limit to 512M in php.ini, then it works.
It seems that the new indexes will also use some memories. But after I uploaded the abantecart v.2 /storefront to overwrites the v.1.2.9, it only needs 384M.
Besides, you have mistake in public_html/install/abantecart_database_upgrade.sql (abantecart v.2.0)
CREATE INDEX `ac_products_status_idx` ON `abc_products` (`product_id`, `status`, `date_available`);
should correct to :
CREATE INDEX `ac_products_status_idx` ON `ac_products` (`product_id`, `status`, `date_available`);