MySQL Forums
Forum List  »  Partitioning

Re: (non-unique) index more efficient than partition pruning
Posted by: Miko M
Date: November 22, 2014 08:39AM

-------------------------------------------------------
> Here's some of what we have have discussed, plus
> more. In particular it includes the two SQLs that
> I promised you a few days ago.
> http://mysql.rjweb.org/doc.php/staging_table
> Please review it and let me know of any typos,
> goofs, missing things, wrong statements,
> whatever
[Miko] Oh my goodness! You've really made it!
Many, many thanks! I really appreciate it.
Having read:
http://mysql.rjweb.org/doc.php/staging_table together with:
http://mysql.rjweb.org/doc.php/datawarehouse
http://mysql.rjweb.org/doc.php/summarytables
I think that the staging table approach is the first thing I am going to test.

Just a couple of questions, if you don't mind.

What if there are many columns to normalize?
How running sql #1 and sql #2 one by one for every column may affect the inserting performance, I mean inserting into the Staging table?

How would the updates from sql #2 affect the inserting performance (into the staging table)?
To me it looks like more and more exclusive locks.
Does it make any sense to use the staging table for inserting only (exclusive locks here) and to select (shared locks here) and copy the records (non-normalised) into another staging table (for normalisation)?
I am thinking about the situation when there are many inserting threads into the the staging table.

Finally, I am not sure whether I understand: "folds SQL #2 into the INSERT INTO Fact."
To me, inserting into the Fact table must be allowed only when Staging and HostNorm tables are fully referenced to each other (in other words there are no NULLs in Staging.host_id and HostNorm.host_name contains all values from Staging.host_name).


>
> Was that partition pruning issue with MariaDB 5.2?
> There may have been improvements since then.
[Miko] With range partitioning the partition pruning feature works only if query contains strict condition such as 'partition_key=...' (i.e. col20='2014-11-23 00:00:00').
Partition pruning does not work with the 'between' or '>' condition (i.e. col20 between '2014-11-23 00:00:00' and '2014-11-23 01:00:00').
> > partitions = idx0,idx2
>
> That is an issue with 'bad' dates mapping to the
> 'first' partition. Hence it is better to have the
> first partition empty (bogus).
[Miko] You mean P1970010101? It is always empty. It is created with 'create table'. No records are inserted here.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: (non-unique) index more efficient than partition pruning
1103
November 22, 2014 08:39AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.