Re: temp table or query for API
> API Response Time
So far you are lucky. Your tables are not very big; as they grow, performance will get worse.
> obscene
I like that characterization.
> I only created maybe 20 of these tables the rest was created by drupal.
Versus 382. Perhaps only 40 would suffice.
> | cache_form | InnoDB | 10 | Compact | 2686 | 12949 | 34783232 | 0 | 1064960 | 1722810368 | NULL | 2013-04-29 23:14:14 | NULL | NULL | utf8_general_ci | NULL | | Cache table for the form system to store recently built... |
seems to be the largest table, at 3K rows & 34MB.
What are all the cache_% tables? Something that Drupal built? (I wonder if it is an attempt at covering for other obscenities.)
> some mysql web front ends. Any advice would be greatly appreciated.
Sorry, I have no advice. This is partially because I find fault with many of them. (This one may be the worst!)
Back to your original question(s)...
> Currently it is being accessed by API around 40-60 times a day, but there are changes that are happening that could increase this to 100's of times an hour.
That is _extremely_ low traffic. There is probably no need for any optimizations such as you mentioned. Anyway, if the queries are different, or new data may come in, what is the use for trying to cache things in a temp table?
> API is being accessed through multiple clients around the world
No problem. Distant clients may suffer 250ms delay (per query) simply due to distance. The US is about 100ms wide. US to Asia is at least 200ms. The monster query we have been discussing is "one" query, hence one such delay.
> 36 rows in set (0.02 sec)
36 rows in an EXPLAIN! That may be the biggest I have ever seen. It is a testament to MySQL's optimizer that if figured out how to optimize that query in 0.02 sec. That included opening dozens of tables, studying each index, deciding which index (if any) to use, probing for cardinality, etc.
Often (but not always), an estimate of the effort to run the query is found by multiplying all the numbers in EXPLAIN's "rows" column. In this case, I suspect that is a gross overestimate. Still, any numbers >1 are worrisome.