I have been looking at various solutions for applying proper sales tax to orders in the US. Looking at one of the simplest solutions, I worry that MySQL performance will struggle.
This solution is basically nothing more than importing a CSV into Magento's tax rates tables. The CSV would look just like any other you've used to import tax rates, with the only noticeable difference being that instead of a five digit Zip code, I would instead use a Zip+4 value (or possibly a Zip+4 range, but that is dependent on the data-set being imported).
A sample set of data comes courtesy of the State of Washington. They provide a number of different data tables to help businesses collect sales tax and one of those tables is a ~4Mb tabular file that provides detailed sales tax information for every valid Zip+4 in the state. As you can imagine, this file has lots and lots of rows. So many, in fact, that it exceeds the max allowable by LibreOffice (1,048,576),
Alternatively, they do provide a data-set that is shorter due to each row being a Zip code with a +4 high/low range (ie. 98506 3301-4211). This file may be more appropriate, but is not going to be available for all states ... regardless ...
Let's say I were to simply import a single state, Washington, and that the total rows were the max allowable in LibreOffice/Excel that I mentioned above. Should I expect terrible performance (assuming typical hardware) during every single
collect_totals call due to this massive table size? Likewise, if I add several more states (growing the table size into millions of rows) should I simply expect more choking?
If this is indeed the case, what other techniques might I use to alleviate these problems? I was thinking of something along the lines of a separate machine that would index all these values in Solr and provide them back to Magento during
tax_rate_data_fetch (or some other appropriate event).
Are there others out there who are using Zip+4 values in their tax rates? What kind of performance effects do you see and what have you done to make sure the store performs well despite these massive table sizes?