CODEWITHSUNDEEP
mysqlquery-optimizationstock-data
Rajib Deb
Rajib Deb

Reputation: 51

How to optimize a large mysql table (stock data)

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

Upvotes: 0

Views: 221

Answers (3)

Rick James
Rick James

Reputation: 142518

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.

Upvotes: 0

Rajib Deb
Rajib Deb

Reputation: 51

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

Upvotes: 1

Jacob
Jacob

Reputation: 1936

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.

Upvotes: 0

Related Questions