Hello,
I have a problem with poor performance when I execute select
from 50.000 records. I have a test that retrieve page by page
(20 records per page) all data from a table. It means I'm executing
2500 selects in the cycle for retrieving each page and I'm retrieving
also time duration of some of this selects. When I retrieve records
that were inserted into the table as the first, there is better
performance as for records retrieved from the last time inserted
records. Results of performance tests are following:
INFO: - Total retrieved pages from first to last: 2500,
number of items for page: 20
- The best performance has page 13 with duration 109 ms.
- The worst performance has page 2174 with duration 3.656 seconds.
- Average duration for page is 1.179 seconds
I think it's poor performance for 50.000 items and it can be
something speed up...
There is log file for time durations attached below (***).
I have following tables:
------------------------
CREATE TABLE BF_DOMAIN
(
ID INTEGER NOT NULL AUTO_INCREMENT,
NAME VARCHAR(100) NOT NULL,
DESCRIPTION TEXT NULL,
MODIFICATION_DATE TIMESTAMP NOT NULL,
CREATION_DATE TIMESTAMP NOT NULL DEFAULT 0,
CONSTRAINT BF_DOM_UQ UNIQUE (NAME),
CONSTRAINT BF_DOM_PK PRIMARY KEY (ID)
) TYPE=INNODB
CREATE TABLE BF_USER
(
ID INTEGER NOT NULL AUTO_INCREMENT,
DOMAIN_ID INTEGER NOT NULL,
FIRST_NAME VARCHAR(50) NOT NULL,
LAST_NAME VARCHAR(50) NOT NULL,
PHONE VARCHAR(20) NOT NULL,
FAX VARCHAR(20) NOT NULL,
ADDRESS TEXT NOT NULL,
EMAIL VARCHAR(50) NOT NULL,
LOGIN_NAME VARCHAR(30) NOT NULL,
PASSWORD VARCHAR(30) NOT NULL,
LOGIN_ENABLED INTEGER DEFAULT 0 NOT NULL,
GUEST_ACCESS_ENABLED INTEGER DEFAULT 0 NOT NULL,
SUPER_USER INTEGER DEFAULT 0 NOT NULL,
INTERNAL_USER INTEGER DEFAULT 0 NOT NULL,
MODIFICATION_DATE TIMESTAMP NOT NULL,
CREATION_DATE TIMESTAMP NOT NULL DEFAULT 0,
CONSTRAINT BF_USR_PK PRIMARY KEY (ID),
CONSTRAINT BF_USR_LOGIN_UQ UNIQUE (LOGIN_NAME),
CONSTRAINT BF_USR_EMAIL_UQ UNIQUE (EMAIL),
INDEX IND_USER_DOMAIN_ID (DOMAIN_ID),
CONSTRAINT BF_USR_FK FOREIGN KEY (DOMAIN_ID)
REFERENCES BF_DOMAIN (ID) ON DELETE CASCADE
) TYPE=INNODB
CREATE INDEX LST_BF_USER_LOGIN_NAME ON BF_USER (DOMAIN_ID, LOGIN_NAME);
CREATE INDEX LST_BF_USER_FIRST_NAME ON BF_USER (DOMAIN_ID, FIRST_NAME);
CREATE INDEX LST_BF_USER_LAST_NAME ON BF_USER (DOMAIN_ID, LAST_NAME);
CREATE INDEX LST_BF_USER_EMAIL ON BF_USER (DOMAIN_ID, EMAIL);
CREATE INDEX LST_BF_USER_PHONE ON BF_USER (DOMAIN_ID, PHONE);
CREATE INDEX LST_BF_USER_LOGIN_ENABLED ON BF_USER (DOMAIN_ID,
LOGIN_ENABLED);
CREATE INDEX LST_BF_USER_MODIFICATION_DATE ON BF_USER (DOMAIN_ID,
MODIFICATION_DATE);
-----------------------------------------------------------------------------------------
a.) First I insert 50.000 records into the DB.
b.) Then I retrieve page by page all records from the DB.
I'm executing 2 commands:
1. Command retrieve number of all items that I want to retrieve
page by page:
select count(*) from BF_USER
where BF_USER.DOMAIN_ID=19 and BF_USER.ID NOT IN(280)
- in the log file is possible to see time duration of this
select - it is time for "Duration for executing count
statement".
2. Final query for retrieving particular records specified within
the LIMIT clause. There is used explicitely defined index
LST_BF_USER_LOGIN_NAME for better performance.
select BF_USER.LOGIN_NAME,
BF_USER.EMAIL,BF_USER.ID,
BF_USER.MODIFICATION_DATE,
BF_USER.SUPER_USER,
BF_USER.GUEST_ACCESS_ENABLED
from BF_USER use index (LST_BF_USER_LOGIN_NAME)
where BF_USER.DOMAIN_ID=19 and
BF_USER.ID NOT IN(280)
order by BF_USER.LOGIN_NAME asc
limit 0, 20
First I execute "select COUNT(*) ..." query for retrieving number
of all items that I will retrieve and then when I know this number,
I can retrieve specified records (used LIMIT for this).
Is this performance for mySQL normal or it can be more optimized?
If there is possible to speed up these selects, please let me
know, how can I do it.
Thank you for your advance,
with best regards,
Julian Legeny
================================================================================
(***) Time duration log file:
-----------------------------
INFO: Duration for executing count statement for page 1 (at position 1) = 485 ms
INFO: Duration for executing final query = 16 ms
INFO: Total duration = 516 ms
INFO: Duration for executing count statement for page 2 (at position 21) = 109
ms
INFO: Duration for executing final query = 16 ms
INFO: Total duration = 141 ms
INFO: Duration for executing count statement for page 3 (at position 41) = 109
ms
INFO: Duration for executing final query = 16 ms
INFO: Total duration = 125 ms
INFO: Duration for executing count statement for page 4 (at position 61) = 125
ms
INFO: Duration for executing final query = 31 ms
INFO: Total duration = 156 ms
INFO: Duration for executing count statement for page 5 (at position 81) = 109
ms
INFO: Duration for executing final query = 31 ms
INFO: Total duration = 156 ms
INFO: Duration for executing count statement for page 1000 (at position 19981)
= 109 ms
INFO: Duration for executing final query = 172 ms
INFO: Total duration = 281 ms
INFO: Duration for executing count statement for page 1001 (at position 20001)
= 125 ms
INFO: Duration for executing final query = 187 ms
INFO: Total duration = 312 ms
INFO: Duration for executing count statement for page 1002 (at position 20021)
= 110 ms
INFO: Duration for executing final query = 172 ms
INFO: Total duration = 297 ms
INFO: Duration for executing count statement for page 1003 (at position 20041)
= 125 ms
INFO: Duration for executing final query = 172 ms
INFO: Total duration = 297 ms
INFO: Duration for executing count statement for page 1004 (at position 20061)
= 125 ms
INFO: Duration for executing final query = 172 ms
INFO: Total duration = 313 ms
INFO: Duration for executing count statement for page 1005 (at position 20081)
= 109 ms
INFO: Duration for executing final query = 187 ms
INFO: Total duration = 296 ms
INFO: Duration for executing count statement for page 1300 (at position 25981)
= 609 ms
INFO: Duration for executing final query = 1.172 seconds
INFO: Total duration = 1.781 seconds
INFO: Duration for executing count statement for page 1301 (at position 26001)
= 578 ms
INFO: Duration for executing final query = 1.313 seconds
INFO: Total duration = 1.891 seconds
INFO: Duration for executing count statement for page 1302 (at position 26021)
= 578 ms
INFO: Duration for executing final query = 1.234 seconds
INFO: Total duration = 1.812 seconds
INFO: Duration for executing count statement for page 1303 (at position 26041)
= 578 ms
INFO: Duration for executing final query = 1.282 seconds
INFO: Total duration = 1.860 seconds
INFO: Duration for executing count statement for page 1304 (at position 26061)
= 562 ms
INFO: Duration for executing final query = 1.172 seconds
INFO: Total duration = 1.734 seconds
INFO: Duration for executing count statement for page 1305 (at position 26081)
= 609 ms
INFO: Duration for executing final query = 1.250 seconds
INFO: Total duration = 1.859 seconds
INFO: Duration for executing count statement for page 1700 (at position 33981)
= 594 ms
INFO: Duration for executing final query = 1.484 seconds
INFO: Total duration = 2.78 seconds
INFO: Duration for executing count statement for page 1701 (at position 34001)
= 609 ms
INFO: Duration for executing final query = 1.360 seconds
INFO: Total duration = 1.969 seconds
INFO: Duration for executing count statement for page 1702 (at position 34021)
= 594 ms
INFO: Duration for executing final query = 1.453 seconds
INFO: Total duration = 2.47 seconds
INFO: Duration for executing count statement for page 1703 (at position 34041)
= 625 ms
INFO: Duration for executing final query = 1.407 seconds
INFO: Total duration = 2.47 seconds
INFO: Duration for executing count statement for page 1704 (at position 34061)
= 610 ms
INFO: Duration for executing final query = 1.296 seconds
INFO: Total duration = 1.906 seconds
INFO: Duration for executing count statement for page 1705 (at position 34081)
= 610 ms
INFO: Duration for executing final query = 1.390 seconds
INFO: Total duration = 2.0 seconds
INFO: Duration for executing count statement for page 2200 (at position 43981)
= 610 ms
INFO: Duration for executing final query = 1.562 seconds
INFO: Total duration = 2.188 seconds
INFO: Duration for executing count statement for page 2201 (at position 44001)
= 641 ms
INFO: Duration for executing final query = 1.671 seconds
INFO: Total duration = 2.312 seconds
INFO: Duration for executing count statement for page 2202 (at position 44021)
= 594 ms
INFO: Duration for executing final query = 1.500 seconds
INFO: Total duration = 2.94 seconds
INFO: Duration for executing count statement for page 2203 (at position 44041)
= 641 ms
INFO: Duration for executing final query = 1.734 seconds
INFO: Total duration = 2.375 seconds
INFO: Duration for executing count statement for page 2204 (at position 44061)
= 656 ms
INFO: Duration for executing final query = 1.610 seconds
INFO: Total duration = 2.266 seconds
INFO: Duration for executing count statement for page 2205 (at position 44081)
= 625 ms
INFO: Duration for executing final query = 1.562 seconds
INFO: Total duration = 2.187 seconds
INFO: Duration for executing count statement for page 2400 (at position 47981)
= 609 ms
INFO: Duration for executing final query = 1.844 seconds
INFO: Total duration = 2.468 seconds
INFO: Duration for executing count statement for page 2401 (at position 48001)
= 610 ms
INFO: Duration for executing final query = 1.719 seconds
INFO: Total duration = 2.344 seconds
INFO: Duration for executing count statement for page 2402 (at position 48021)
= 594 ms
INFO: Duration for executing final query = 1.640 seconds
INFO: Total duration = 2.234 seconds
INFO: Duration for executing count statement for page 2403 (at position 48041)
= 594 ms
INFO: Duration for executing final query = 1.781 seconds
INFO: Total duration = 2.375 seconds
INFO: Duration for executing count statement for page 2404 (at position 48061)
= 594 ms
INFO: Duration for executing final query = 1.797 seconds
INFO: Total duration = 2.391 seconds
INFO: Duration for executing count statement for page 2405 (at position 48081)
= 625 ms
INFO: Duration for executing final query = 1.688 seconds
INFO: Total duration = 2.328 seconds
INFO: Duration for executing count statement for page 2500 (at position 49981)
= 609 ms
INFO: Duration for executing final query = 1.969 seconds
INFO: Total duration = 2.578 seconds
INFO: Total retrieved pages from first to last: 2500, number of items for page:
20
The best performance has page 13 with duration 109 ms.
The worst performance has page 2174 with duration 3.656 seconds.
Average duration for page is 1.179 seconds
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
