MySQL Forums :: General :: tables with 3,500,000 rows, indexed but still v slow


Advanced Search

tables with 3,500,000 rows, indexed but still v slow
Posted by: Adam Hardy ()
Date: June 13, 2010 11:13AM

I have a database for an app which now has large row counts for some tables and I'm experiencing severe performance problems with a new data-intensive part of the app that adds 20K to 30K records in a transaction.

I built up the database over 3 years now adding tables with primary key and foreign key indexing but without any other functionality that might help performance, so it looks like it's high time to do something about it.

I was alarmed when I ran a simple "select count(*) from TRADE" and it took 60 secs to count 3.5M rows.

Following the guidelines for posting performance issues, here's my best attempt to isolate a portion of the database.

This is just one of several selects, but I figured I already put enough in the message to choke an elephant.

Any advise massively appreciated!

mysql> explain extended select count(*) from DOLLAR_RETURN;
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table         | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | DOLLAR_RETURN | index | NULL          | PRIMARY | 4       | NULL | 3612947 | Using index | 
+----+-------------+---------------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select count(*) from TRADE;
+----------+
| count(*) |
+----------+
|  3641133 | 
+----------+
1 row in set (52.51 sec)

+----+-------------+-------+--------+-------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-------------+
| id | select_type | table | type   | possible_keys                                                     | key                                  | key_len | ref                         | rows | Extra       |
+----+-------------+-------+--------+-------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-------------+
|  1 | SIMPLE      | t0    | ref    | DOLLAR_RETURN_MARKET_SYSTEM_FK_MYSQL,DOLLAR_RETURN_TRADE_FK_MYSQL | DOLLAR_RETURN_MARKET_SYSTEM_FK_MYSQL | 5       | const                       |    1 | Using where | 
|  1 | SIMPLE      | t1    | eq_ref | PRIMARY,TRADE_TEST_RUN_FK_MYSQL                                   | PRIMARY                              | 4       | PATTERN_TEST.t0.TRADE_ID    |    1 |             | 
|  1 | SIMPLE      | t2    | eq_ref | PRIMARY,TEST_RUN_REPO_FK_MYSQL                                    | PRIMARY                              | 4       | PATTERN_TEST.t1.TEST_RUN_ID |    1 |             | 
|  1 | SIMPLE      | t3    | eq_ref | PRIMARY                                                           | PRIMARY                              | 4       | PATTERN_TEST.t2.MARKET_ID   |    1 |             | 
|  1 | SIMPLE      | t6    | eq_ref | PRIMARY                                                           | PRIMARY                              | 4       | PATTERN_TEST.t2.REPO_ID     |    1 |             | 
|  1 | SIMPLE      | t4    | eq_ref | PRIMARY                                                           | PRIMARY                              | 4       | PATTERN_TEST.t3.EXCHANGE_ID |    1 |             | 
|  1 | SIMPLE      | t5    | eq_ref | PRIMARY                                                           | PRIMARY                              | 4       | PATTERN_TEST.t4.ACCOUNT_ID  |    1 |             | 
+----+-------------+-------+--------+-------------------------------------------------------------------+--------------------------------------+---------+-----------------------------+------+-------------+
7 rows in set, 1 warning (0.51 sec)


drop database if exists PATTERN_TEST;

create database PATTERN_TEST;

use PATTERN_TEST;


create table KEY_SEQUENCE (
    TABLE_SEQ varchar(50) not null,
    LAST_KEY integer not null,
    constraint KEY_SEQUENCE_PK primary key (TABLE_SEQ)
);

alter table KEY_SEQUENCE type=innodb;

create table ACCOUNT (
    ID integer,
    TITLE varchar(255) not null,
    HOST varchar(32),
    PORT integer,
    IGNORABLE_ERRORS varchar(255),
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime not null,
    constraint ACCOUNT_PK primary key (ID),
    constraint ACCOUNT_TITLE_U unique (TITLE)
) ;

alter table ACCOUNT type=innodb;
alter table ACCOUNT add constraint ACCOUNT_TITLE_U_MYSQL unique (TITLE);

create table EXCHANGE (
    ID integer,
    TITLE varchar(50),
    ACCOUNT_ID integer not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime not null,
    constraint EXCHANGE_PK primary key (ID),
    constraint EXCHANGE_ACCOUNT_FK foreign key (ACCOUNT_ID) 
        references ACCOUNT (ID),
    constraint EXCHANGE_TITLE_U unique (TITLE)
);    

alter table EXCHANGE type=innodb;
alter table EXCHANGE add constraint EXCHANGE_TITLE_U_MYSQL unique (TITLE);

create table MARKET (
    ID integer,
    EXCHANGE_ID integer not null,
    INSTRUMENT_TYPE integer not null,
    TITLE varchar(50) not null,
    CURRENCY varchar(3) not null,
    MARGIN decimal(15,6) not null,
    VOL_OI_URL varchar(1024),
    HOURS_URL varchar(1024),
    UNIT_VALUE decimal(15,2) not null,
    COMMISSION decimal(15,6) not null,
    MAX_SLIPPAGE decimal(15,6) not null,
    AVG_TRUE_RANGE decimal(15,6) not null,
    AVG_SPREAD decimal(15,6) not null,
    AVG_SLIPPAGE decimal(15,6) not null,
    MONTHS_TRADED varchar(12) not null,
    OPENING varchar(5) not null,
    CLOSING varchar(5) not null,
    LISTED date not null,
    DELISTED date,
    NUMERIC_DISPLAY varchar(50) not null,
    TIME_ZONE_ID varchar(100) not null,
    EXAMPLE_PRICE decimal(15,6) not null,
    TICK_SIZE decimal(15,6) not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime not null,
    constraint MARKET_PK primary key (ID),
    constraint MARKET_TITLE_U unique (TITLE),
    constraint MARKET_EXCHANGE_FK foreign key (EXCHANGE_ID) 
        references EXCHANGE (ID)
) ;

alter table MARKET type=innodb;
alter table MARKET add constraint MARKET_TITLE_U_MYSQL unique (TITLE);
alter table MARKET add constraint MARKET_EXCHANGE_FK_MYSQL 
    foreign key (EXCHANGE_ID) references EXCHANGE (ID);


create table REPO (
    ID integer ,
    TITLE varchar(255) not null,
    CONTENT varchar(25000),
    TRADE_COMBINATION integer not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime not null,
    constraint REPO_PK primary key (ID),
    constraint REPO_TITLE_U unique (TITLE)
) ;

alter table REPO type=innodb;
alter table REPO add constraint REPO_TITLE_U_MYSQL unique (TITLE);

create table TEST_RUN (
    ID integer,
    REPO_ID integer not null,
    MARKET_ID integer not null,
    BEGIN_RUN datetime null default null,
    END_RUN datetime null default null,
    TIME_FRAME varchar(16) not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime null default null,
    constraint TEST_RUN_PK primary key (ID),
    constraint TEST_RUN_REPO_FK foreign key (REPO_ID) references REPO (ID),
    constraint TEST_RUN_MARKET_FK foreign key (MARKET_ID) 
        references MARKET (ID)    
) ;

alter table TEST_RUN type=innodb;
alter table TEST_RUN add constraint TEST_RUN_REPO_FK_MYSQL
    foreign key (REPO_ID) references REPO (ID);
alter table TEST_RUN add constraint TEST_RUN_MARKET_FK_MYSQL
    foreign key (MARKET_ID) references MARKET (ID);

create table TRADE (
    ID integer,
    TEST_RUN_ID integer not null,
    DIRECTION integer not null, 
    ENTRY_DATE datetime null default null,
    ENTRY_POINT decimal(15,6) not null,
    EXIT_DATE datetime null default null,
    EXIT_POINT decimal(15,6) not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime null default null,
    constraint TRADE_PK primary key (ID),
    constraint TRADE_TEST_RUN_FK foreign key (TEST_RUN_ID)
        references TEST_RUN (ID)
) ;

alter table TRADE type=innodb;
alter table TRADE add constraint TRADE_TEST_RUN_FK_MYSQL
    foreign key (TEST_RUN_ID) references TEST_RUN (ID);

create table MARKET_SYSTEM (
    ID integer,
    TEST_RUN_ID integer not null,
    AVG_TRUE_RANGE decimal(15,6),
    UNIT_VALUE decimal(15,6),
    COMMISSION decimal(15,6),
    AVG_SPREAD decimal(15,6) not null,
    AVG_SLIPPAGE decimal(15,6) not null,
    MAX_SLIPPAGE decimal(15,6),
    CURRENCY varchar(3) not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime null default null,
    constraint MARKET_SYSTEM_PK primary key (ID),
    constraint MARKET_SYSTEM_TEST_RUN_FK foreign key (TEST_RUN_ID)
        references TEST_RUN (ID)
);

alter table MARKET_SYSTEM type=innodb;
alter table MARKET_SYSTEM add constraint MARKET_SYSTEM_TEST_RUN_FK_MYSQL
    foreign key (TEST_RUN_ID) references TEST_RUN (ID);

create table DOLLAR_RETURN (
    ID integer,
    MARKET_SYSTEM_ID integer,
    TRADE_ID integer not null,
    PROFIT decimal(15,6) not null,
    OWNER_ID integer not null,
    VERSION integer not null,
    CREATED datetime not null,
    MODIFIED datetime null default null,
    constraint DOLLAR_RETURN_PK primary key (ID),
    constraint DOLLAR_RETURN_MARKET_SYSTEM_FK 
        foreign key (MARKET_SYSTEM_ID) references MARKET_SYSTEM (ID),
    constraint DOLLAR_RETURN_TRADE_FK 
        foreign key (TRADE_ID) references TRADE (ID)
) ;

alter table DOLLAR_RETURN type=innodb;
alter table DOLLAR_RETURN add constraint DOLLAR_RETURN_MARKET_SYSTEM_FK_MYSQL 
    foreign key (MARKET_SYSTEM_ID) references MARKET_SYSTEM (ID);
alter table DOLLAR_RETURN add constraint DOLLAR_RETURN_TRADE_FK_MYSQL
    foreign key (TRADE_ID) references TRADE (ID);

use PATTERN_TEST;
insert into ACCOUNT (ID, TITLE, HOST, PORT, IGNORABLE_ERRORS, OWNER_ID, VERSION, CREATED, MODIFIED) values (3, 'Exch Def', '', 0, '', 1, 1,'2009-05-17 00:00:00.0', '2009-05-17 00:00:00.0');
insert into EXCHANGE (ID, TITLE, ACCOUNT_ID, OWNER_ID, VERSION, CREATED, MODIFIED) values ( 1,'CME GLOBEX', 3, 1, 52, '2001-01-01 00:00:00.0', '2001-01-01 00:00:00.0');
insert into MARKET (ID, EXCHANGE_ID, INSTRUMENT_TYPE, TITLE, CURRENCY, MARGIN, VOL_OI_URL, HOURS_URL, UNIT_VALUE, COMMISSION, MAX_SLIPPAGE, AVG_TRUE_RANGE, AVG_SPREAD, AVG_SLIPPAGE, MONTHS_TRADED, OPENING, CLOSING, LISTED, NUMERIC_DISPLAY, TIME_ZONE_ID, EXAMPLE_PRICE, TICK_SIZE, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 1, 4, 'Aussie Dollar', 'USD', 6345.000000,'http://www.cmegroup.com/trading/fx/fx/australian-dollar.html';, 'http://www.cmegroup.com/trading/fx/fx/australian-dollar_contract_specifications.html';, 100000.00, 5.700000, 0.033600, 0.010000, 0.0002, 0.000200, 'HMUZ', '17:00','16:00', '1976-01-01', 'D_0_0001', 'America/Chicago', 0.795300, 0.000100, 1, 187,'2001-01-01 00:00:00.0','2008-12-13 00:00:00.0');
insert into REPO (ID, TITLE, CONTENT, TRADE_COMBINATION, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 'Test Portfolio', 'doCode()', 0, 1, 1, '2001-01-01 00:00:00.0', '2001-01-01 00:00:00.0');
insert into TEST_RUN (ID, REPO_ID, MARKET_ID, BEGIN_RUN, END_RUN, TIME_FRAME, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 1, 1, '1997-01-01 00:00:00.0', '2001-12-31 00:00:00.0', 'DAILY', 1, 26, '2001-01-01 00:00:00.0', '2001-01-01 00:00:00.0');
insert into TRADE (ID, TEST_RUN_ID, DIRECTION, ENTRY_DATE, ENTRY_POINT, EXIT_DATE, EXIT_POINT, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 1, 1, '1999-01-02 00:00:00.0', 1.155500, '1999-01-02 00:00:00.0', 155500, 1, 1, '2001-01-01 00:00:00.0','2001-01-01 00:00:00.0');
insert into MARKET_SYSTEM (ID, TEST_RUN_ID, AVG_TRUE_RANGE, UNIT_VALUE, COMMISSION, AVG_SPREAD, AVG_SLIPPAGE, MAX_SLIPPAGE, CURRENCY, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 1, 0.000000, 0.000000, 10.000000, 0.010000, 1.000000, 0.000000, 'USD', 1, 1, '2001-01-01 00:00:00.0', '2001-01-01 00:00:00.0');
insert into DOLLAR_RETURN (ID, MARKET_SYSTEM_ID, TRADE_ID, PROFIT, OWNER_ID, VERSION, CREATED, MODIFIED) values (1, 1, 1, 123, 1, 1, '2001-01-01 00:00:00.0', '2001-01-01 00:00:00.0');

SELECT t0.ID, t0.VERSION, t0.CREATED, t0.MODIFIED, t0.OWNER_ID, t0.PROFIT, t1.ID, t1.VERSION, t1.CREATED, t1.MODIFIED, t1.OWNER_ID, t1.DIRECTION, t1.ENTRY_DATE, t1.ENTRY_POINT, t1.EXIT_DATE, t1.EXIT_POINT, t2.ID, t2.VERSION, t2.CREATED, t2.MODIFIED, t2.OWNER_ID, t2.BEGIN_RUN, t2.END_RUN, t3.ID, t3.VERSION, t3.CREATED, t3.MODIFIED, t3.OWNER_ID, t3.AVG_SLIPPAGE, t3.AVG_SPREAD, t3.AVG_TRUE_RANGE, t3.COMMISSION, t3.CURRENCY, t3.DELISTED, t3.EXAMPLE_PRICE, t4.ID, t4.VERSION, t4.CREATED, t4.MODIFIED, t4.OWNER_ID, t5.ID, t5.VERSION, t5.CREATED, t5.MODIFIED, t5.OWNER_ID, t5.IGNORABLE_ERRORS, t5.HOST, t5.PORT, t5.TITLE, t4.TITLE, t3.INSTRUMENT_TYPE, t3.LISTED, t3.CLOSING, t3.OPENING, t3.MARGIN, t3.MAX_SLIPPAGE, 
t3.MONTHS_TRADED, t3.NUMERIC_DISPLAY, t3.HOURS_URL, t3.TICK_SIZE, t3.TIME_ZONE_ID, t3.TITLE, t3.UNIT_VALUE, t3.VOL_OI_URL, t2.TIME_FRAME, t6.ID, t6.VERSION, t6.CREATED, t6.MODIFIED, t6.OWNER_ID, t6.CONTENT, t6.TITLE, t6.TRADE_COMBINATION 
FROM DOLLAR_RETURN t0 
INNER JOIN TRADE t1 ON t0.TRADE_ID = t1.ID 
INNER JOIN TEST_RUN t2 ON t1.TEST_RUN_ID = t2.ID 
LEFT OUTER JOIN MARKET t3 ON t2.MARKET_ID = t3.ID 
INNER JOIN REPO t6 ON t2.REPO_ID = t6.ID 
LEFT OUTER JOIN EXCHANGE t4 ON t3.EXCHANGE_ID = t4.ID 
LEFT OUTER JOIN ACCOUNT t5 ON t4.ACCOUNT_ID = t5.ID 
WHERE t0.MARKET_SYSTEM_ID = 11400;

Options: ReplyQuote


Subject Written By Posted
tables with 3,500,000 rows, indexed but still v slow Adam Hardy 06/13/2010 11:13AM
Re: tables with 3,500,000 rows, indexed but still v slow Rick James 06/14/2010 08:25PM
Re: tables with 3,500,000 rows, indexed but still v slow Adam Hardy 06/17/2010 11:05AM
Re: tables with 3,500,000 rows, indexed but still v slow Rick James 06/17/2010 10:28PM
Re: tables with 3,500,000 rows, indexed but still v slow Adam Hardy 06/18/2010 05:37AM
Re: tables with 3,500,000 rows, indexed but still v slow Rick James 06/18/2010 10:40AM
Re: tables with 3,500,000 rows, indexed but still v slow Adam Hardy 06/18/2010 06:00PM
Re: tables with 3,500,000 rows, indexed but still v slow Adam Hardy 06/19/2010 05:31AM
Re: tables with 3,500,000 rows, indexed but still v slow Rick James 06/19/2010 01:34PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.