Mike Connell wrote:
> Interesting possibility. What constitutes an
> "exact" match with MyISAM?
Not MyISAM, but all engines. It's the query cache that is doing the matching, not the storage engine. An exact match is essentially the exact same SQL SELECT statement issued against the server, including capitalization, variables, everything. The query cache will return the result set of such a query without the storage engine (and thus the key cache) ever being involved if a) the rows contained in the result have not been modified since the last retrieval, and b) you aren't using prepared statements.
So, query cache is out for you, since you're using prepared statements through JDBC.
> For example, 2 cases below and associated pseudo
> code to process it:
>
> <begin
>
> case 1
> -------
> select token_id, amount from token where
> account_id = ':variable';
> set :variable = '1234', execute, fetch results,
> and process.
>
> select token_id, amount from token where
> account_id = ':variable';
> set :variable = '5678', execute, fetch results,
> and process.
>
> case 2
> -------
> select token_id, amount from token where
> account_id = '1234';
> execute, fetch results, and process.
>
> select token_id, amount from token where
> account_id = '5678'
> execute, fetch results, and process.
>
> end>
>
> in case 1 the JDBC ":variable" is substituted with
> the value so the statement doesn't have to
> be reprepared. I do not think with MyISAM there is
> savings using case1 vs case2.
>
> Is this a correct understanding?
My understanding, and Ingo please correct me if I'm wrong, is that it doesn't matter whether you're using MyISAM or InnoDB with prepared statements. The savings of prepared statements when issuing multiple repeated SQL statements are most evident when issuing repeated INSERT and UPDATE commands, not necessarily SELECT statements. There are certainly much better ways to make the above scenarios more efficient for the SELECT statements.
Issuing multiple SELECT statements, like the ones above, compared to retrieving a single larger resultset and then processing the results in local memory after releasing the server connection is much more efficient than the above method. If a single SELECT statement returns too big of a result set, then cut the result into smaller batches, but with multiple account_id's returned at once.
I am a little confused, however. You said above that you have a PHP program doing the load, but here you're using JDBC?
Jay Pipes
Community Relations Manager, North America, MySQL Inc.
Got Cluster?
http://www.mysql.com/cluster
Personal:
http://jpipes.com