Hello Brent,
Thanks for your answer, I have try to use clause SQL_CALC_FOUND_ROWS
within my SELECT query (and I also didn't use SELECT COUNT(*) ... query).
But when I used SQL_CALC_FOUND_ROWS and then I retrieved number of
all columns using SELECT FOUND_ROWS(), the performance was worse than
before (with SELECT COUNT(*) + SELECT ... LIMIT query).
I have try to use also following construction (used FOUND_ROWS() as
the output from the query):
select SQL_CALC_FOUND_ROWS
BF_USER.LOGIN_NAME,
BF_USER.EMAIL,BF_USER.ID,
BF_USER.MODIFICATION_DATE,
BF_USER.SUPER_USER,
BF_USER.GUEST_ACCESS_ENABLED,
FOUND_ROWS()
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
But when I executed this command from JAVA, I have retrieved from
the column FOUND_ROWS() value = 1 ... but there were 1000 items within
the particular table and value has to be also 1000.
But when I executed the same command from a DB client, I have
retrieved from the column FOUND_ROWS() value = 1000.
It means, the same command returns me different values for
FOUND_ROWS() and it is conditioned by executing this command from JAVA
or from DB client (I have used SQuireL SQL Client).
Can you advice me, if there is possible somehow retrieve
FOUND_ROWS() directly from the query, that is just executed?
Thank you for your answer,
with best regards,
Julian Legeny
Monday, November 22, 2004, 6:15:49 PM, you wrote:
BB> First you are using two distinct queries where you should be using two
BB> "related". Specifically, add SQL_CALC_FOUND_ROWS to your first query
BB> that retrieves data, then issue a query to see how many rows you would
BB> have had if you hadn't specified a LIMIT:
BB> SELECT FOUND_ROWS()
BB> The second query should be instantaneous since MySQL figured out the
BB> result during the first query. From your log, it looks like you keep
BB> reissuing the count(*) query. Why? You know how many rows you are going
BB> to get since you specify a LIMIT. The exception would be the last
BB> query, which may fall short, but you can calculate that from the
BB> original FOUND_ROWS() query.
BB> You also may try adding SQL_CACHE to the data query. This may or may
BB> not help. Your queries are exactly the same, except for the LIMIT. I'm
BB> not sure if MySQL will consider these queries "identical". Technically,
BB> they are not. It's something to try.
BB> Make sure your sort_buffer in the php.ini file is set high enough.
BB> Sometimes changing that one variable makes all the difference.
BB> Alternatively, since your data is not going to change between queries,
BB> do an initial select without the limit into a HEAP table. Then run the
BB> queries off of the HEAP table. All the most time consuming work,
BB> sorting and filtering, would already been done. It's just the LIMIT
BB> that would need to be calculated. If you have the memory, this would
BB> probably be your fastest solution.
BB> On Nov 22, 2004, at 11:32 AM, Julian Legeny wrote:
>> 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]
