What would cause performance issues in query A but not query B?

Question

I have a table (MYSQL 8) with apx. 100M records storing various bits of stock data (price, date, etc.), and query A below runs in < 1s, but query B takes over 2mins. Among other indices, I've got an index on the date, and the primary key for the table is (symbol, date). What would cause such a significant difference between the two queries, and what might speed up the poor performer?

Query A:

SELECT symbol, MIN(date)
FROM Stocks
WHERE date BETWEEN '2015-01-01' AND '2020-01-01'
GROUP BY symbol

Query B

SELECT symbol, MIN(date)
FROM Stocks
WHERE date BETWEEN '2015-01-01' AND '2020-01-01' AND market_cap > 20
GROUP BY symbol

The other challenge I'm facing is that at times I want to filter by market_cap, but other times by other numerical fields (gross_profit, total_assets, etc.). The query is being generated by a form with a number of optional inputs that are tied to params.

Table schema

CREATE TABLE IF NOT EXISTS cri_v0_995 (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  company_id MEDIUMINT UNSIGNED NOT NULL, 
  dt DATE NOT NULL,
  price DECIMAL(18, 2),
  market_cap DECIMAL(12, 4),
  div_yield DECIMAL(4,2), -- this is always 0
  price_to_nte DOUBLE,
  price_to_mte DOUBLE,
  nte_to_price DECIMAL(16, 10),
  ante_to_price DECIMAL(16, 10),
  ate_to_price DECIMAL(18, 10),
  price_to_sales DOUBLE,
  price_to_earnings DOUBLE,
  cur_liq_to_mcap DECIMAL(4, 2), -- this is always 0
  net_liq_to_mcap DOUBLE,
  g3_rev_growth_and_trend DECIMAL(14, 10),
  p_cri_score DECIMAL(14, 10),
  f_cri_score DECIMAL(10, 7),
  cri_score DECIMAL(14, 10),
  PRIMARY KEY (id),
  FOREIGN KEY (company_id) REFERENCES companies (id),
  UNIQUE KEY (company_id, dt)
);

Note that there are a couple cols that I'm unsure about. They've always been zeros but I don't know what the intent may be behind them atm.

(edit 1 to address missing GROUP BYs) (edit 2 adding table schema)

Answer 1

The first query could simply hop through the table:

• For each symbol (which is conveniently the start of the PK)
• Find the first row ("first" because of the second part of the index is date) that is also >= start date
• Toss the result if that date is not <= the end date

The second query needs to look at each row to check market_cap; it can't jump through the table.

If, instead, you have current_market_cap in the Symbols table you could filter on market_cap before JOINing to this table.

Two ranges in the WHERE clause makes it very difficult to optimize. INDEXes are one-dimensional.

Using PARTITION BY TODAYS(date) requires a major structural change to the table. It may (or may not!) help your query run faster -- by using 'partition pruning' to limit how many rows need to be checked. (I say "may not" because the query is looking at a 5-year range, which might be a significant fraction of the entire data.)

More discussion of partitioning: http://mysql.rjweb.org/doc.php/partitionmaint and http://mysql.rjweb.org/doc.php/find_nearest_in_mysql -- The latter link discusses a different 2D problem (geographical 'find nearest'); it is something of a stretch to apply it to your query.

Since you have lots of columns that the end-user might filter on, and 100M rows, let's approach from another direction: Minimizing table size. This is especially important if the table cannot be fully cached in the buffer_pool -- leading to being I/O-bound. Show us SHOW CREATE TABLE; let's discuss each column, and whether it can be shrunk.

More

• Changing symbol VARCHAR... to company_id MEDIUMINT UNSIGNED may have saved 1GB between the data and the index.
• Get rid of id and promote UNIQUE(company_id, dt) to be the PK. That will save a few GB by eliminating the only secondary index. (Your change was probably beneficial.
• Most of those DOUBLEs are overkill? FLOAT would save 4 bytes each and still give you 6-7 significant digits.
• You may want INDEX(dt) for some other queries.
• The filter on market_cap probably gets in the way of groupwise max optimization.
• Depending on disk space and other queries, it may be beneficial to PARTITION BY RANGE(TO_DAYS(dt)), but group by years. The (5 year + 1 day) span would hit 6 partitions. (cf "partition pruning") This would not actually change performance much.
• (About 18 years ago, I worked with a dataset like this.)
• price DECIMAL(18, 2) takes 9 bytes. It allows for a zillion dollars, which has not [yet] been reached. It has only 2 decimal places, so it won't precisely hold amounts before they switched to decimal (from /2, /4, /8, /16, etc).
• market_cap DECIMAL(12, 4) (6 bytes) may not be big enough for some companies, and certainly not for indexes. And the 4 decimal places is probably a waste.
• Suggest running SELECT MAX(market_cap), MAX(price), ... to see how big the numbers are now.

Answer 2

For both queries, you probably should be aggregating by the symbol. So, the second currently non performant query should be:

SELECT symbol, MIN(date)
FROM Stocks
WHERE date BETWEEN '2015-01-01' AND '2020-01-01' AND market_cap > 20
GROUP BY symbol;

The index you want here should at least cover the entire WHERE clause:

CREATE INDEX ON Stocks (date, market_cap);

If you run EXPLAIN on both queries, after adding GROUP BY, you might find that your current single column index on date isn't even being used in the second query.

Answer 3

maybe you need to force using index in the sql Query B.

SELECT symbol, MIN(date)
FROM Stocks use index (`indexNameOfDate`)
WHERE date BETWEEN '2015-01-01' AND '2020-01-01' AND market_cap > 20
GROUP BY symbol

or you can force to use primaryKey index.

doing that may save times for sql engine choosing index itself. and you can find which is faster as well.

what's more, if u are using date and market_cap to filter data usually, maybe you need to create a index cover them.

like @Tim Biegeleisen said.

CREATE INDEX ON Stocks (date, market_cap);