Hello Monty,
I downloaded and installed 4.0.1 for the SQL_CALC_FOUND_ROW option.
However, the query runs 600% longer, so the saving I get with FOUND_ROWS()
running in 0.0 secs does not serve me. Am I maybe doing something wrong?
Here is what I did;
I ran the query without SQL_CALC_FOUND_ROW and it runs in 2.61 secs.
mysql> select * from memberships,membershipstracking where
memberships.email=mem
bershipstracking.email order by membershipstracking.activitytimestamp desc
limit
1;
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
| EMAIL | NAME | MIDDLEINITIAL | SURNAME
| N
EWSLETTER | USERNAME | PASSWORD | COUNTRY | EMAIL
|
ACTIVITYTIMESTAMP | NAME | MIDDLEINITIAL | SURNAME |
NEWSLETTER
| USERNAME | PASSWORD | COUNTRY | ACTIVITY | BUTTON | REMOTEADDRESS |
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
| [EMAIL PROTECTED] | firstname94 | M | surname94
| Y
| v94 | v94 | country94 |
[EMAIL PROTECTED] |
2002/01/03 22:54:11:768 | firstname94 | M | surname94 | Y
| v94 | v94 | country94 | voltest94 | submit | 127.0.0.1 |
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
1 row in set (2.61 sec)
mysql>
Then I add SQL_CALC_FOUND_ROW, and the exact same query runs for 16.95 secs.
mysql> select sql_calc_found_rows * from memberships,membershipstracking
where m
emberships.email=membershipstracking.email order by
membershipstracking.activity
timestamp desc limit 1;
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
| EMAIL | NAME | MIDDLEINITIAL | SURNAME
| N
EWSLETTER | USERNAME | PASSWORD | COUNTRY | EMAIL
|
ACTIVITYTIMESTAMP | NAME | MIDDLEINITIAL | SURNAME |
NEWSLETTER
| USERNAME | PASSWORD | COUNTRY | ACTIVITY | BUTTON | REMOTEADDRESS |
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
| [EMAIL PROTECTED] | firstname94 | M | surname94
| Y
| v94 | v94 | country94 |
[EMAIL PROTECTED] |
2002/01/03 22:54:11:768 | firstname94 | M | surname94 | Y
| v94 | v94 | country94 | voltest94 | submit | 127.0.0.1 |
+----------------------------------+-------------+---------------+----------
-+--
----------+----------+----------+-----------+-------------------------------
---+
-------------------------+-------------+---------------+-----------+--------
----
+----------+----------+-----------+-----------+--------+---------------+
1 row in set (16.95 sec)
mysql>
Please advise?
Kind regards
Emmanuel
> -----Original Message-----
> From: Michael Widenius [mailto:[EMAIL PROTECTED]]
> Sent: 21 January 2002 00:44
> To: Emmanuel van der Meulen
> Cc: MySQL General List; MySQL Java List
> Subject: RE: Understanding throughput with JDBC
>
>
>
> Hi!
>
> >>>>> "Emmanuel" == Emmanuel van der Meulen <[EMAIL PROTECTED]> writes:
>
> Emmanuel> Hello Mark,
> Emmanuel> Thank you for the note and feedback. BTW, it was not
> over a network. Both
> Emmanuel> on local PC. So all the time went into building the
> resultset in memory.
> Emmanuel> I'm surprised at the time that takes.
>
> Emmanuel> Further to why I'm desirous to do this query, twofold.
> One I get the record
> Emmanuel> count (rows in the table); since realised there are
> other ways to get he
> Emmanuel> number of rows. For the second requirement I cannot
> see an alternative.
> Emmanuel> Not on this table, but on another where I keep a
> timestamp, I'd like to get
> Emmanuel> the 50 most recent inserts. So what I do is; (SELECT * FROM
> Emmanuel> MEMBERSHIPSTRACKING ORDER BY ACTIVITYTIMESTAMP DESC)
> thus get all the rows,
> Emmanuel> ordered desc on timestamp and then I have the most
> recent 50 inserts. If
> Emmanuel> anyone could assist me with this, I'd use other means,
> rather than
> Emmanuel> retrieving the full resultset.
>
> Why can't you use 'LIMIT 50' to get the latest 50 rows ?
> (As Mark asked, why do you have to use LIMIT 10000)
>
> You can also use SQL_CALC_FOUND_ROW option to avoid to do two queries,
> if you want to know how many rows the WHERE statement actually matched
> (see the MySQL manual for more information about this topic).
>
> Regards,
> Monty
>
> --
> For technical support contracts, goto https://order.mysql.com/
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]>
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
> /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
> <___/ www.mysql.com
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
