5

The table has around 17M rows and is MyISAM.

select DateModified,PELTM,PSLTM 
from financial_key_ratios 
where SecurityId = 4525 
  and DateModified >= '2015-01-07';

This simple query seems to take more than 1 second a lot of times.

The indexes seem to be fine. Here is the output from explain:

 explain 
 select DateModified,PELTM,PSLTM 
 from financial_key_ratios 
 where SecurityId = 4525 
   and DateModified >= '2015-01-07';
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
| id | select_type | table                | type  | possible_keys                                  | key        | key_len | ref  | rows | Extra       |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+
|  1 | SIMPLE      | financial_key_ratios | range | SecurityId,secid,DateModified_index,secid_date | secid_date | 7       | NULL |  156 | Using where |
+----+-------------+----------------------+-------+------------------------------------------------+------------+---------+------+------+-------------+

What could we be doing wrong?

CREATE TABLE `financial_key_ratios` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `SecurityId` int(11) NOT NULL,
  `PELTM` decimal(12,4) NOT NULL,
  `PSLTM` decimal(12,4) NOT NULL,
  `POCFLTM` decimal(12,4) NOT NULL,
  `PEG` decimal(12,4) NOT NULL,
  `PBV` decimal(12,4) NOT NULL,
  `EVEBTIDA` decimal(12,4) NOT NULL,
  `Evbyrevenue` decimal(12,4) NOT NULL,
  `Fcf_yeild` decimal(12,4) NOT NULL,
  `Earnings_yeild` decimal(12,4) NOT NULL,
  `Pe_annualized` decimal(12,4) NOT NULL,
  `Pbys_annualized` decimal(12,4) NOT NULL,
  `Pbybv_annualized` decimal(12,4) NOT NULL,
  `Evbyebitda_annualized` decimal(12,4) NOT NULL,
  `Evbyrevenue_annualized` decimal(12,4) NOT NULL,
  `Pe_ltm_adj` decimal(12,4) NOT NULL,
  `Enterprise_value` decimal(12,4) NOT NULL,
  `Marketcap` decimal(12,4) NOT NULL,
  `DividendYield` decimal(12,4) NOT NULL,
  `DateModified` date NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `SecurityId` (`SecurityId`,`DateModified`),
  KEY `secid` (`SecurityId`),
  KEY `DateModified_index` (`DateModified`),
  KEY `secid_date` (`SecurityId`,`DateModified`)
) ENGINE=MyISAM AUTO_INCREMENT=65138826 DEFAULT CHARSET=latin1 |

Update:

I already have all the indexes in place. It is NOT always slow. Out of 14K requests, it is reported in slow query log around 100 times.

Here is the update from slow query log

Time=1.22s (68s)  Lock=0.00s (0s)  Rows=249.2 (13955), 
select DateModified,PELTM,PSLTM from financial_key_ratios
where SecurityId = N and DateModified >= 'S'
7
  • 2
    That's quite generic without further details. What else is going on on the server? Is it a dedicated MySQL server? How big the database? Does it fit to RAM? What is the underlying disk subsystem? Are there other concurrent queries? Is the query always slow or only occasionally? Explain is only a first step and doesn't tell anything about how long a query could take to execute. Commented Jan 7, 2016 at 13:41
  • 1 sec for a range in 17 millions rows doesnt seem that much to me.
    – Mihai
    Commented Jan 7, 2016 at 16:56
  • Since you already have the SecurityId index, the sec_id index is redundant. You should drop it. You could probably get rid of the secid index as well. But that is unrelated to the question. Anyway, 100 out of 14K requests is less than 1%. And 1 sec is not that slow (how many rows are returned in the slow cases)? Commented Jan 8, 2016 at 7:07
  • 1
    @rational-space check the slow log entries - they should contain info about rows examined and lock times among others - the table is MyISAM so there are table locks on updates. A mix of few heavy selects and updates intertwined may cause parallelism to go out the window and the query just waiting to be executed for most of the time.
    – jkavalik
    Commented Jan 8, 2016 at 7:29
  • What are the N and 'S' from the log entry? Is that a real query with wrong values or some placeholders? Comparing a DATE column to 'S' would lead to a full table scan but probably no results..
    – jkavalik
    Commented Jan 11, 2016 at 7:31

1 Answer 1

1

I'm not an expert on MySQL, but it looks to me like it's doing a table scan by secid_date, which isn't even in the query at all. And scanning 17M rows is not what you want to be doing unless you really have to.

Check your indexes

mysql> SHOW INDEX FROM financial_key_ratios\G

I would make sure that you have something like

+-----------------------+------------+------------+--------------+---------------+ ... 
| Table                 | Non_unique | Key_name   | Seq_in_index | Column_name   | ... 
+-----------------------+------------+------------+--------------+---------------+ ... 
| financial_key_ratios  |          1 | ...        |            1 | SecurityId    | ... 

and if possible

| financial_key_ratios  |          1 | ...        |            2 | DateModified  | ... 

If you aren't indexing on at least SecurityId, you can add it with

CREATE INDEX IdxSecurityId ON financial_key_ratios (SecurityId)

You can take it a step further and include DateModified

CREATE INDEX IdxSecurityId_DateModified ON financial_key_ratios (SecurityId, DateModified)
3
  • 1
    secid_date suggest he already has the last index
    – Mihai
    Commented Jan 7, 2016 at 16:57
  • Indexing Cookbook -- that would come to the same conclusion.
    – Rick James
    Commented Jan 7, 2016 at 23:27
  • It was worth a shot as a potential solution since the original post was vague on what indexes existed as well as the intermittency of the issue.
    – Greg
    Commented Jan 8, 2016 at 13:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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