Re: Killed by joins. 1 minute selects in Access, 15 minutes in mySQL

[Rick James]

> So the solution you provided works very well since the nested table aggregates to 6,000 rows.
In MySQL, subqueries are beneficial if and only if they end up with significantly fewer rows than they start with -- via GROUP BY, DISTINCT, LIMIT, etc. (There are exceptions.)

Here is a disasterous exception:
FROM ( SELECT ... ) a
JOIN ( SELECT ... ) b ON a.x = b.x
It is bad because neither a nor b will have an index on `x`. So, if there are more than a tiny number of rows in both a and b, it will be doing lots of scans of the temp tables.

> if I group on all categorical fields except month-end date...
Let's see the SELECT. PARTITIONing on date may or may not be helping; it probably does not hurt. Pushing dates into a dimension table is usually a performance disaster whenever you want to cover a date range.

I'll second Peter's comments.

> Can you tell me a bit more about why the original indexing didn't work?
Well, mostly because it simply had to scan both tables, gathering all the info. But also...

InnoDB really likes to have a PRIMARY KEY. You did not give it one, so it created a 6-byte (ugh! big) one for your. InnoDB orders the data on the PK (useless for you). Secondary keys are implemented as BTrees, and have the PK in the leaf node. A secondary key lookup leads to a PK lookup.

AUTO_INCREMENT is good, but it is not automatically the PK:

`SymbolID` smallint(6) NOT NULL AUTO_INCREMENT,
KEY `Index 1` (`SymbolID`)
-->
`SymbolID` smallint(6) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`SymbolID`)

You probably want UNIQUE(Symbol).

(BTW, there are more than 32K stock symbols in the world. SMALLINT SIGNED has a 32K limit; consider MEDIUMINT UNSIGNED -- 16M limit.)

In the Fact table, is this combo UNIQUE?
(`Exec_Date`, `SymbolID`)
If so, consider making it the PK. If not, consider adding an INT UNSIGNED AUTO_INCREMENT (4 billion limit) or BIGINT (really huge limit).

> DEFAULT NULL
Shouldn't most of those fields be NOT NULL ?