@ypercubeᵀᴹ "COUNT(dt.c_date)" - yes, of course! I went down a rabbit-hole when I saw the other answer "SUM(t1.data IS NOT NULL)," and basically shoe-horned the SUM() function to do what I wanted - thanks for pointing out my schoolboy error! I also went down another hole with regular expressions (coming to a screen near you!)!
@ypercubeᵀᴹ And that ORDER BY 1 hurts my eyes ;) - Sorry ducky! :-)
@ypercubeᵀᴹ "Good answer nonetheless." - thanks - I put a bit of time into it, but I learned a good bit, so it was worth it! "Don't get offended by my late night comments ..." - it takes a lot to offend me! :-)
@mustaccio Re. Guinness outside of Ireland - I used to drink Guinness regularly, and the best I had was in Paris - I'm pretty sure it came from Dublin - but because it was considered a "premium export" product, it was considerably stronger than its Dublin counterpart - and much more expensive!
@Vérace The query that goes by the Query_ID of 3 in your profile chart has more aggregations in it than the other queries. I mean it's performing the SUM and the COUNT twice. Maybe internally they are run once each since they are just instances of identical aggregations, but maybe not. I don't know how much of a factor it can be if not, but since we are talking less than milliseconds, it might matter some.
@AndriyM - you mean the query where the schema doesn't change? Yes, I know it's longer - I kept it in because the OP may not be able to make any mods to the schema.
@AndriyM - God alone knows what MySQL does! The one that calculates the percentage is also the only one where there's a SELF-JOIN - that join is performed already in the VIEW or the TABLE schema adjustments, so no double table-scan - at least AIUI?
But the one over the view is effectively also done over the self-join, no? I mean if we resolve the view. But as you say, we don't know what MySQL does, so maybe there's a difference.
I'm looking at this mostly from a SQL Server perspective, where it would be little to no difference in querying over a join vs querying over a view that's defined as the exact same join.
I mentioned that I went down a rabbit hole about regular expressions - here is PostgreSQL's and MySQL's response to the same REGEXP_REPLACE query - I was trying to find out which was the most efficient for converting a string in DD-MM-YYYY format to ISO YYYY-MM-DD format - I wanted a "universal" solution - but I ended up going back to SUBSTRING and the || operator - you have to use a SQL sql_mode = ANSI; hack to get MySQL to do the right thing - so I could CAST the string as a date.
Here is a side-by-side - PostgreSQL does (what I think) is the right thing - MySQL apparently does nothing AND doesn't even throw an error - report a bug or shrug the shoulders?...
@AndriyM - the fiddle with the regexes has nothing to do with the solution - it's just something I noticed while working towards my solution - one of many rabbit-holes! The final final solution here now has 4 queries all profiled and explained (thanks to @ypercubeᵀᴹ).
It now runs consistently with the third fastest result, but queries 1 and 2 sometimes trade places. Look like this profiling thingy is best done in a different environment than an online publicly available resource
feel like i've asked this before but... @MaxVernon, @jcolebrand - when "no-longer-needed" flagging a comment that's part of a discussion, it's preferable to just flag one, right?
given that a mod is gonna go to the comment thread to clean it up anyway, i assume you just use your judgement from there
rather than flagging all the individual comments that could apply, i mean
FWIW, when I want to flag an entire discussion as no longer needed and don't feel like flagging every comment individually, I flag the last one with a custom message. It's worked with the intended result on DBA.SE so far. I've recently done that on SO and they removed only the comment I flagged. I then flagged the other comments individually as "no longer needed".
Different communities, different flagging cultures
Different everything, really. Each network is really self-contained IMO. I don't feel like there's not a whole lot that's portable between SE communities in terms of social expectation.
rather than saying "lolwat" and downvoting... anyone know what he's talking about?
pretty sure it's just straight up wrong in the context of that thread but I'm wondering what I'm missing that he's interpreting from it as a storage admin?
no idea, really. if i had to make up a post-facto justification for it right now i'd say that it's cause it "keeps the data together", like a cluster of hosts keeps them together on the network or something 🤷♂️
@AndriyM From IBM: A clustering index determines how rows are physically ordered (clustered) in a table space. Clustering indexes provide significant performance advantages in some operations, particularly those that involve many records.
So as per Zardosht Kasheff answer, there are products that can have mulitple clusted indexs, which is basically duplicating the data, because a custerd index is "the way/order" that data is stored in a table.
@JohnK.N. I mean, I can probably find other similar definitions. I'm trying to understand the choice of the word to describe it, though. What is "clustering" about it? It's also sometimes called "clustered", which grammatically speaking shouldn't be the same thing, but in this case the two words are used synonymously
A non-clustered (non-clustering?) index also provides a way to order rows, albeit indirectly
If you create multiple indexes on all the columns but in different combinations thereof, that would effectively give you multiple clustered indexes, right?
In SQL Server, row-oriented storage both clustered and nonclustered indexes are organized as B trees.
(Image Source)
The key difference between clustered indexes and non clustered indexes is that the leaf level of the clustered index is the table. This has two implications.
The rows on the clus...
But again, as the answer linked by Peter argues, that may just be the implementation choice by SQL Server. Maybe a clustered index doesn't have to be the table, it could be one of multiple copies of the table.
coming from SQL Server, the way I usually explain it to people is that the clustered index (assuming it exists) is the table
so saying "multiple clustered indexes", sounds to me like "multiple tables" and i assume there's some bizarro (to me) trigger-type storage mechanism that enforces concurrent updates between them
Martin actually falls short of actually explaining the difference between the clustered index and a non-clustered index with all the columns in SQL Server. He gives this example:
CREATE TABLE T
(
A INT,
B INT,
C INT,
D INT
)
CREATE UNIQUE CLUSTERED INDEX ci ON T(A, B)
CREATE UNIQUE NONCLUSTERED INDEX nci ON T(A, B) INCLUDE (C, D)
and follows it by:
> The two indexes above will be nearly identical. With the upper-level index pages containing values for the key columns A, B and the leaf level pages containing A, B, C, D
I guess it's the "nearly" part that would be interesting in the context of this discussion
The mechanism (I assume) is no more different than updating a non-clustered index in SQL Server that has all the rest columns as INCLUDEd (like Andriy just shows ^^).
@AndriyM No idea about the actual details that differ between the two. Possibly different overheads on rows and pages metadata.
One actual difference is that if we add a column to the table, it will be added only in the clustered index.
Perhaps NC indexes always have a reference to the actual table (clustered index or heap) regardless of whether you've included all the columns and (maybe?) regardless of whether it's also a unique index with all the columns. A clustered index, of course, doesn't need to have such references.
In an environment with multiple clustered indexes, it would probably make sense to have the concept of a primary copy, though, at least if the environment supports non-clustered indexes as well, because those would need to reference something in case the index merely helps to quickly point to a row but you need other columns from it that aren't covered by the (non-clustered) index.
Which makes me think that it wouldn't be very beneficial – and instead could be somewhat confusing – to support both non-clustered indexes and multiple clustered indexes. It's either a single clustered index and multiple supporting non-clustered ones, or just multiple indexes all of which are clustered
The multi-clustered-index products will probably copy the data multiple times. Hence the disk space overhead mentioned. However, multi-clustered-index solutions have a speed advantage, because no additional lookup required. The clustered index is the data.
@AndriyM I don't think there is any reference needed. All the columns of the table are included in the index and that's all you need to access the table/main-clustered-index if needed.
But I agree, a main/primary-copy clustered index makes sense.
That's what TokuDB does, as far as I know.
So you can have non-clustered indexes as well, that work exactly as in InnoDB / SQL Server.
@ypercubeᵀᴹ But as you said, when a new column is added, the non-clustered index won't have it, only the clustered one will, so that's probably why there reference still needs to be there.
@ypercubeᵀᴹ The fiddle's failure is that MySQL lower result doesn't change 07-07-2027 into 2027-07-07 as the PostgreSQL upper result does - so that the string can then be CASTto a DATE - which is what I wanted to do - I ended up using a SUBSTRING so that the SQL would be multi-server compatible!
@ypercubeᵀᴹ That's the detail that I don't really know much about, hence my saying "perhaps" at the beginning. If SQL Server recognises that the key of a non-clustered index matches the primary key exactly, then maybe there's no additional reference needed, but I just don't know
When you add a column E, it's added to the clustered index only. And if a query happens to use the non-clustered index to find a row but suddenly realises it needs to pull data from E, which isn't in the non-clustered index, what then?
but the NCI still has the columns of the PK/CI index, right? So, a plan that gets data from a NCI index first, it can then traverse the CI index and find any row it needs and the wanted extra column data
@AndriyM - thanks for your interest - on my first 3 runs of your fiddle (corrected the comma), the 3rd query was the 2nd slowest - you have to look at multiple runs - you don't know what's happening on the dbfiddle.uk server at any given time, so a single run snapshot may not be representative?
@ypercubeᵀᴹ Right, which is where my knowledge gives way. If the keys match, then of course that it makes sense that the key can be used as a way of referencing, particularly if the key is unique.
@Vérace A single run should certainly not be relied on when it's a publicly available resource that may be running you don't know how many other user queries.
Well then that's exactly the kind of referencing overhead I'm talking about.
The kind that a non-clustered index needs and a clustered index doesn't. Note that you may still want to pull from column F, which the NC index doesn't have at that point
@AndriyM - strangely the SUM() query with the (already constructed) table (2nd query in the fiddle) is fastest of all!... and not the apparently simpler COUNT() one (table also) ... go figure... I think a dive into PG source would be in order to figure that out - above my pay grade... :-)
@ypercubeᵀᴹ - ah, you changed the order of the queries - I thought I'd mentioned that in the answer somewhere - caches &c. will obviously come into play! If you've stored the table in memory in the cache, queries lower down will speed up.
I could use PG's generate_series to generate millions of records (I did search for a CLEAR CACHE command - tried something that didn't work (forgot what). Then use pg_dump to generate generic SQL and load it into MySQL...
Is there any way of telling MySQL on dbfiddle to clear its cache - or PostgreSQL for that matter?
You could probably try a bigger table (not necessarily in the millions, don't kill dbfiddle ;) say 5-10k rows and repeat the query. If you can ignore the 1st run even better.
@ypercubeᵀᴹ - of course, as you suggest, profiling a query with a tiny number of records is not a definitive performance analysis... I might look at that this evening... I can run it on my local machine - and clear down the caches... Another problem is that we don't know the OP's data distribution - I would imagine that the vast majority of garments are collected? PG's partial indexes would be good for this - but not with MySQL...
And why on earth would one be interested in the colour of non-collected garments? Do Goths not pick up their stuff?
I’m voting to close this question because it is related to Alembic which is explained as "Alembic is a lightweight database migration tool for usage with the SQLAlchemy Database Toolkit for Python." I'd suggest this question be asked on Stack Overflow where there is an alembic tag and one for sqlalchemy. Not off-topic but better suited on SO. — John K. N.Dec 24 '20 at 7:52
However the question was asked on SO apparently and then on DBA.SE.
yah, I looked at everything already - mostly I was wondering if my own biases are preventing me from seeing something that others can see. Like for instance, the OP states they are trying to use this on their production database, but all I see is version control and hence I think client side. I mean maybe if they didn't keep talking about their developers, I wouldn't have a problem with it. It's not like as DBAs we're inherently anti-source control.
@PeterVandivier my personal preference would be to have you flag everything that you think can go as "no longer needed".
For reasons of performance, I need to run in parallel multiple stored procedures (actually, the same SP, with different parameters) and combine the results in code.
It is not possible to read all the data right away.
It works perfectly with a small number of concurrents, but with 19, I receive a ...
The term "clustering index" was already in use by the IBM researchers working on System R as early as 1981 (before Sybase and MS SQL Server, that is): "An index is considered to have the clustering property if the key order of the index corresponds closely to the ordering of records in physical storage". Example: dsf.berkeley.edu/cs262/SystemR-annotated.pdf
In the IBM implementation row data are not stored in the index pages -- a table is always a "heap". If there is an index declared as clustering, the engine will try to place the row near other rows with the same/similar key upon insert or key update. There is no guarantee though.
I guess that's why it's "clustering" -- it tries to achieve clustering of data.
@mustaccio IIRC Oracle and IBM use the terms "index-organized" since the data is still in a heap, but there's still a page/row pointer used in the B-tree (as opposed to having the leaf level be the table itself).
Oracle does use the term "index-organized table", but Db2 has always used "clustering index" (sometimes conflated with "clustered", but "clustering" is the primary term).
I think SQL Server development would benefit on a whole if people understood that the physical implementation of "nonclustered indexes" are no different that tables clustered by the indexed column(s) with the original PK and any included columns as the data.
The term Clustered Index is probably the worst thing when dealing with explaining things to people like execution plans and index design and you have to keep using the term, and always clarifying with statements like (which isn't an index, it's just the table, meaning not a heap, and on and on). Sometimes when I see clustered index scan in the plan and I'm showing it to something, I just say "table scan", because fuck it.
And yeah, NCI is really just another clustered index. Because all indexes are clustered!