How to optimize a large mysql table (stock data)

Question

I have a table with following structure,

    `trading_daily_price` (
    `id` int(11) NOT NULL PRAMARY AUTO_INCREMENT,
    `date` date DEFAULT NULL,
    `Symbol` varchar(20) DEFAULT NULL,
    `Market` varchar(12) DEFAULT NULL,
    `QuoteName` text,
    `Price` float DEFAULT NULL,
    `PriceChange` float DEFAULT NULL,
    `PriceChangePct` float DEFAULT NULL,
    `Volume` float DEFAULT NULL,
    `DayLow` float DEFAULT NULL,
    `DayHigh` float DEFAULT NULL,
    `Week52Low` float DEFAULT NULL,
    `Week52High` float DEFAULT NULL,
    `Open` float DEFAULT NULL,
    `High` float DEFAULT NULL,
    `Bid` float DEFAULT NULL,
    `BidSize` float DEFAULT NULL,
    `Beta` float DEFAULT NULL,
    `PrevClose` float DEFAULT NULL,
    `Low` float DEFAULT NULL,
    `Ask` float DEFAULT NULL,
    `AskSize` float DEFAULT NULL,
    `VWAP` float DEFAULT NULL,
    `Yield` float DEFAULT NULL,
    `Dividend` char(12) DEFAULT NULL,
    `DivFrequency` varchar(24) DEFAULT NULL,
    `SharesOut` float DEFAULT NULL,
    `PERatio` float DEFAULT NULL,
    `EPS` float DEFAULT NULL,
    `ExDivDate` date DEFAULT NULL,
    `MarketCap` float DEFAULT NULL,
    `PBRatio` float DEFAULT NULL,
    `Exchange` varchar(32) DEFAULT NULL,
    `NewsTitle` varchar(1024) DEFAULT NULL,
    `NewsSource` varchar(32) DEFAULT NULL,
    `NewsPublicationDate` date DEFAULT NULL,
    `NewsURL` varchar(256) DEFAULT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I didn't find an idea to break down it, in frontend presentation, I need all these columns to display. I am writing a query like,

    SELECT * FROM trading_daily_price WHERE date='SOME_DATE' AND Symbol='%search_key%' ORDER BY 'column' LIMIT 10

The table has millions of records, and every day new records are added. Now the problem is every query takin so much time to generate the output. In a 4GB VPS with DigitalOcean with some configuration, it's running nicely. But, in Godaddy business hosting it's running very slowly.

I want to know is it a better idea to break the columns into multiple tables, and using JOIN statements. Will it increase performance? or I need to follow other optimization logic.

As suggested by Madhur, I have added INDEX to date, symbol, and Market. It improves the above query speed, but the following query still taking much time.

    SELECT `date`,`Price` FROM trading_daily_price WHERE `Symbol` = 'GNCP:US' ORDER BY date ASC

Thanks in advance, Rajib

Thank you so much, the speed really increased now, but still, I have a question, breaking the table is suggested or not? — Rajib Deb, Aug 07 '18 at 11:46
You are performing a `SELECT *` therefore breaking the table into multiple tables would definitely not cause faster READ speeds. Furthermore, your table is `trading daily price` and all these seem like reasonable attributes of that object, so normalizing this would be unnecessary. — JNevill, Aug 07 '18 at 14:14
Thank you, this is the point I was thinking but was not sure. I also think if I use multiple tables, then multiple JOINING operations may put an additional load to the server. — Rajib Deb, Aug 07 '18 at 14:27

score 1 · Answer 1 · answered Aug 07 '18 at 14:35

As suggested by Madhur and JNevill, I found the only solution is to create multiple INDEX as required.

for first SQL,

    SELECT * FROM trading_daily_price WHERE date='SOME_DATE' AND Symbol='%search_key%' ORDER BY 'column' LIMIT 10

we need to create index as below,

    CREATE INDEX index_DCS ON trading_daily_price (`date`,column, symbol);

and for the second SQL,

    SELECT `date`,`Price` FROM trading_daily_price WHERE `Symbol` = 'GNCP:US' ORDER BY date ASC

we need to create index as below,

    CREATE INDEX index_DPS ON trading_daily_price (`date`,Price, symbol);

Thanks

score 0 · Answer 2 · answered Aug 07 '18 at 14:23

You shouldn't need date, symbol and column index for your first query because you are searching symbol by %text% and MySql can only use the date part of the index. An index with date and column should be better because MySQL can utilize two columns from the index

For your new query, you will need index on Symbol, date and price. By this index, your query won't need go back to clustered index for data.

Whether splitting the table depends on your use case: how will you handle old data. If old data won't be frequently accessed, you can consider to split. But your application need cater for it.

score 0 · Answer 3 · answered Sep 30 '18 at 04:11

Split up that table.

One table has the open/high/low/close/volume, indexed by stock and date.

Another table provides static information about each stock.

Perhaps another has statistics derived from the raw data.

Make changes like those, then come back for more advice/abuse.

How to optimize a large mysql table (stock data)

3 Answers3