0

I have to find an element in a table that has many partitions, I do not know in which one of them will it be found or even if the element exists in any of them, so I have to query many partitions.

As there are many partitions, I usually specify which one I want to query to make the query faster, but in this case I do not now which one will be the one that contains the element I'm looking for.

Is it better to execute different queries, one for each partition, or execute a query that includes every possible partition?

I'm using MySQL.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user2256799
  • 229
  • 1
  • 3
  • 10
  • 1
    You don't need to specify the partition. The SQL engine will search all of them, if the partition key is not used for filtering the query. – Gordon Linoff Oct 20 '16 at 11:03
  • Nevertheless, I have found much more efficient to specify the partitions when you know where is going to be the element you are looking for. – user2256799 Oct 20 '16 at 11:11
  • I don't understand your comment. Your question is specifically about *not* knowing where the data is located. – Gordon Linoff Oct 20 '16 at 11:45
  • Right. The question is whether to specify every possible partition the data might be in a single query or just to execute one query for each partition. Event if I do not know where the data is, I can just add every possible partition to the query. – user2256799 Oct 20 '16 at 11:51
  • 1
    If you run queries one-at-a-time, then you can stop when you find what you are looking for (assuming what you want is only in one partition). That would be an efficiency you can implement. However, you could get the same effect using `limit`. – Gordon Linoff Oct 20 '16 at 11:57
  • It may well be better to remove the partitioning. – Rick James May 23 '17 at 19:42

0 Answers0