It's your MySQL client that's run out of memory, not the server. I don't know how many rows MySQL is trying to return. Probably an
enormous amount since you're not sepcifying a join criteria on the data table. The number of records in the event table between
'2006-05-01' AND '2006-05-15' times the number of records in the data table.
SELECT event.cid, event.timestamp
FROM event
JOIN data ON ???
WHERE event.timestamp BETWEEN '2006-05-01' AND '2006-05-15
----- Original Message -----
From: "Jacob, Raymond A Jr" <[EMAIL PROTECTED]>
To: <mysql@lists.mysql.com>
Sent: Tuesday, July 11, 2006 6:13 PM
Subject: Trying to understand why Select running out of memory if table not used
When I try to retrieve all rows in the event table that are in a
particular range and then
try to determine if the cid is in the data table the join takes
forever.
So I tried just tried adding the table to see if the where clause was
slowing things down. Below is a portion on my session.
mysql> select cid, timestamp from event where timestamp between
'2006-05-01' AND '2006-05-15';
....
| 7753544 | 2006-05-14 23:59:58 |
| 7753545 | 2006-05-15 00:00:00 |
| 7753546 | 2006-05-15 00:00:00 |
+---------+---------------------+
1336344 rows in set (32.55 sec)
mysql> select event.cid, event.timestamp from event, data where
mysql> event.timestamp between '2006-05-01' AND '2006-05-15';
mysql: Out of memory (Needed 1477684 bytes) ERROR 2008 (HY000): MySQL
client ran out of memory
Obviously, that is a bad idea. I just can not figure
Out how to speed the select clause up.
I was using the query:
Select event.cid, event.timestamp from event, data
Where ( event.timestamp between '2006-05-01' AND '2006-05-15' ) and
event.cid=data.cid;
But the query never completed i.e. I aborted the query after a few
hours.
Thank you,
Raymond
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
