0

Say you have:

CREATE TABLE  demo (
    amount ,
    year ,
    cycle ,
    otherStuff ,
    PRIMARY KEY ( id , year , cycle )
) ENGINE = INNODB
PARTITION BY RANGE ( year )
SUBPARTITION BY KEY ( cycle ) 
SUBPARTITIONS 12 (
    PARTITION p2020 VALUES LESS THAN (2021) ,
    PARTITION p2021 VALUES LESS THAN (2022) ,
    PARTITION p2022 VALUES LESS THAN (2023) ,
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

What's the best SELECT to run on that table?

A:

SELECT otherStuff FROM demo WHERE amount > 10 AND year = 2022 AND cycle = 1;

B:

SELECT otherStuff FROM demo (p2022, p1) WHERE amount > 10;

or

C:

SELECT otherStuff FROM demo (p2022, p1) WHERE amount > 10 AND year = 2022 AND cycle = 1;

I'm sure that there is some extra overhead in pruning—some preliminary step for the storage engine to take to figure out which partitions match the WHERE clause. But, where only one partition and subpartition match the WHERE clause and the pruning WHERE clause contains only simple equals comparisons, what I'm trying to figure out is whether the extra overhead is nominal for performance. The reason I want to figure that out is because I want to know if I can get away with pruning, which offers an advantage in design: if I ever wanted to, I could get rid of my partitions and have no queries to change. In other words, explicit partition selection introduces a dependency I'd rather avoid.

Thanks.

3

2 Answers 2

1

Re "binding":

Unlike other DBs, MySQL does the binding in the client. Hence, whether bound or not, the server has the same amount of work, and goes through the same decisions about how to optimize the query.

Binding in the client is desirable as a way to combat "SQL injection" and to get proper escaping of strings.

I have seen as many as 6 different query plans (via EXPLAIN) for a single query. The differences were triggered by different values being feed for binding.

9
  • You have saved me a fair amount of overhaul and at least an afternoon, if I understand you correctly. You're saying that for the purpose of query caching: WHERE amount > :amount AND year = 2022 AND cycle = 1 is the same as WHERE amount > :amount AND year = :year AND cycle = :cycle where year and cycle (as they are) are the same continuously for a regular period? (Or maybe you're even saying that whether they are the same doesn't even matter.) Sep 25, 2021 at 16:41
  • @JackAdder999 - Terminology issue... The "Query cache" is a deprecated technique that is probably not what you are talking about. "prepare+bind+execute" is probably what you are referring to. year = :year is just a string; turning that into ("binding") year = 2022 is a relatively trivial process. You should not worry about its performance. A more complex step comes next (in MySQL): deciding which index to use, etc. (Other vendors decide on the index before binding.)
    – Rick James
    Sep 25, 2021 at 17:10
  • So, you recommend year = :year but in MySQL wouldn't expect it to either help or hurt performance? Sep 25, 2021 at 17:13
  • @JackAdder999 - It is "safer" (cf "SQL Injection") to let some package do the "binding" (that is, replacing :year with 2020). For that reason, I will insist on letting the package help you build queries. I am saying that "binding" has only a tiny performance hit compared to everything else that goes on. Having the optimal index is easily 100 times as important.
    – Rick James
    Sep 25, 2021 at 17:20
  • @JackAdder999 - And I am saying that PARTITIONing does not necessarily provide any performance gain. Avoid partitioning until you put a hundred SELECTs into production code.
    – Rick James
    Sep 25, 2021 at 17:21
0

All three approaches should produce the same query plan so there shouldn't be a difference from that standpoint. You'd obviously need to verify that in your environment when the queries get a little more complicated to ensure that partition pruning is actually happening when you expect it to happen.

Using just conditions in the where clause is vastly easier to implement in a secure, reusable way in applications since you're just using bind variables rather than trying to dynamically build out the partition clause when the predicates change. That makes it more likely that a query plan is already cached and can be reused when a query is run which has an impact on performance.

5
  • Thanks. I do dynamically build out the WHERE clause inasmuch as the queries are dynamic. (The above is pseudocode, obviously.) For some reason, perhaps laziness, I do not bind values in the part of the WHERE clause that prunes the partitions. I justified this because there are no third-party values there. Is this a mistake? Am I risking losing caching unless, in the example above, year and cycle are bound values via PDO? Sorry if this question is weird and I missed your point. Sep 25, 2021 at 5:00
  • edit: I think I should be more descriptive. The cycle and year variables are static for a time. They're time-series variables. If that helps . . . . So, for some time, all the queries will have the same cycle and year. I understand your point about the query plan. I'm picking up on your point about the caching with my follow-up question. Sep 25, 2021 at 5:14
  • @JackAdder999 - Yes, using bind variables generally facilitates the reuse of plans. The performance impact may or may not be large depending on the relative cost of parsing/ optimization and execution. If your queries are returning millions of rows, execution likely dominates. If your queries are returning a handful of rows, the parse time may be significant. Even if the cost is minimal in a single-user environment, it may become significant if you try to scale out the system and have lots of sessions trying to run ad hoc SQL simultaneously since that probably creates blocking issues. Sep 25, 2021 at 6:44
  • Helpful. Thanks. For the record, I found this (stackoverflow.com/questions/4174524/…) describing in the first answer how easy it seems to be to bind values in a dynamic query clause even without using placeholders. Out of curiosity, are explicit partition selections involved in caching at all? (I say "out of curiosity" as I'm leaning heavily toward the WHERE clause anyway.) Sep 25, 2021 at 14:49
  • @JustinCave - MySQL, unlike Oracle and SQL Server, picks a query plan after binding variables. (I do not know whether this applies inside Stored Procedures, but I am pretty sure of what I am saying for Jack's situation.)
    – Rick James
    Sep 25, 2021 at 17:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.