0

I have a pair of linked issues I need help with:

Issue #1: How to Index a Table to Optimize for a "Group By" SELECT

I have a table of stock price information with the following Create statement:

CREATE TABLE `price_daily` (
  `Symbol` varchar(20) NOT NULL,
  `Date` date NOT NULL,
  `Price` float DEFAULT NULL,
  `MarketCap` double DEFAULT NULL,
  `PriceToEarnings` double DEFAULT NULL,
  `PriceToSales` double DEFAULT NULL,
  PRIMARY KEY (`Symbol`,`Date`),
  KEY `Date_MCap` (`Date`,`MarketCap`,`PriceToSales`,`Symbol`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table is fairly expansive; it spans roughly 100 million rows. Thus, imprecise SELECT queries are likely to take a long time to resolve, which I would like to avoid.

I want to be able to search the database for stocks within a specified date range that fall within selected PriceToSales and/or PriceToEarnings ranges. The query below is typical of one I would perform:

SELECT Symbol, Date, MarketCap, Price
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31' 
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

I use GROUP BY because I only need to see one result per Symbol. The problem is, when I add the 'GROUP BY' clause at the end, the query suddenly takes many times longer to complete than it does without that clause. What am I doing wrong? I assume it has something to do with my indexing, but I have not yet 100% figured out the 'rules' for ordering columns in indexes. (A couple non-specific specifics: without GROUP BY the query has roughly 0.5s completion time but takes >200s to fetch, as there are potentially millions of results to display.)

EDIT 4/28/20 3pm: I have been told, in multiple locations, that the syntax of the above GROUP BY query is bad because the columns Date, MarketCap and Price are not aggregated. This is presumably a trivial issue to fix; for now, I leave Issue #1 alone to focus solely on the second issue.

Issue #2: SELECTing the Smallest Value From Within a GROUPed Query

A related question to the above: Suppose, rather than allow the 'GROUP BY Symbol' clause from the above to choose whichever date it wants, I want to guarantee that my query will always SELECT the earliest Date chronologically for each Symbol. How would I do that? I assume the correct query would look something like this:

SELECT Symbol, MIN(Date)
FROM price_daily
WHERE Date BETWEEN '2002-07-31'AND '2017-07-31' 
AND MarketCap >= 1000
AND PriceToSales BETWEEN 2 AND 5
GROUP BY Symbol
;

...but, due to the issue I am having with GROUP BY above, I cannot currently test this.

Any help given on either or both of these problems will be much appreciated. If you need me to provide more information, I'll edit this post tomorrow morning when I get back to the office.

EDIT 4/28/20 3:30pm: The EXPLAIN for the query given under Issue #2 is as follows:

+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
| id   | select_type | table       | type  | possible_keys      | key       | key_len | ref  | rows     | Extra                                                     |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
|    1 | SIMPLE      | price-daily | range | PRIMARY, date_mcap | date_mcap | 21      | NULL | 50622081 | Using where; using index; using temporary; using filesort |
+------+-------------+-------------+-------+--------------------+-----------+---------+------+----------+-----------------------------------------------------------+
1 row in set (0.00 sec)

The query took 331.1 secs to resolve, generating 2735 rows. An excerpt from the table:

Symbol  Date
------  ----------
A       2003-12-19
AABA    2007-07-26
AACC    2005-10-03
AAN     2010-02-11
AAOI    2017-03-07
AAON    2013-10-01
AAPL    2004-10-05
AAT     2012-07-27
...     ...
  • Please run the query with EXPLAIN and display the output – Jbeltran Apr 27 at 23:46
  • @Jbeltran EXPLAIN and output added – tharpin Apr 28 at 19:33
  • The query, as written, will find the first day on which that market cap is exceeded (and some other stuff). Is that really what you wanted? Or did you want info about tickers with a current cap over 1000? – Rick James May 1 at 21:36
0

It seems there are a lot of rows which fit the conditions and database server needs to sort them for grouping. It probably even needs to create a temp table on disk if your data doesn't fit into memory. In-memory temporary table size is limited by max_heap_table_size and tmp_table_size variables, but what is more important - MEMORY tables use a fixed-length row-storage format. Variable-length types such as VARCHAR are stored using a fixed length. It means every varchar(20) value will consume 20 bytes of RAM. Add 3 bytes for dates and 4 for doubles and your row size will be 31 bytes. If 5% of 100 million rows fit the conditions than you need 150 MB temporary table to group your data. You can start with increasing the max temporary table size and check if it helps.

But also you can try to use another approach:

SELECT Symbol
    ,(SELECT Date FROM price_daily
        WHERE Symbol = s.Symbol
            AND Date BETWEEN '2002-07-31'AND '2017-07-31' 
            AND MarketCap >= 1000
            AND PriceToSales BETWEEN 2 AND 5
        ORDER BY Date LIMIT 1) AS min_date
FROM symbols AS s;

I assumed you have a table with list of symbols (Symbol data type should be the same).

| improve this answer | |
  • I do have a secondary table with all the unique Symbols stored in it, spanning 33,589 rows. The secondary approach you listed almost works. The completion time for the query you quoted, however, is 3.6 seconds duration / 707.3 seconds fetch! Why does it take so long to load a result table with only 33,589 rows? – tharpin Apr 28 at 19:14
  • @tharpin it seems your original query works even faster (331 seconds vs 707). As you can see server needs to read and process 50 622 081 (estimated) of rows to filter, group them and send final result set with 2735 rows to you. What client do you use? do you run the query on local or remote server? Did you try to output result to the local file on the server? – NikitaSerbskiy Apr 28 at 22:44
  • I have been using MySQL Workbench v6.3.9 as my client of choice. I run the queries on my remote server (an Azure "General Purpose" setup with 4 vCores). I have not tried outputting results to a file on the server. Dumb question: Does that work on Azure? I haven't explored the file structure enough to know where to go to find stuff like that i.e. the results of a SELECT INTO OUTFILE command. – tharpin Apr 29 at 13:32
  • @tharpin, no, you can't use select into outfile in this case but you can try to insert data into new table an check how fast it works. – NikitaSerbskiy Apr 30 at 8:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.