On 1/17/11 9:52 AM, Jerry Schwartz wrote:
[JS] I don't understand how an index on a timestamp would help. Theoretically,
each record could have a unique value for the timestamp; so the index would
have an entry for each record. Would MySQL really use that in preference to,
or in combination with,
>>
>> SELECT COUNT(*) AS num FROM alerts WHERE stamp > DATE_SUB(NOW(),
>> interval 300 second) AND devid=244;
>>
>> With this query, MySQL will run DATE_SUB() once, and then use the
>> index on stamp (which I assume you have) to narrow down the result set.
>>
[JS] I don't understand how an index o
On 01/14/2011 08:19 AM, Steve Meyers wrote:
> On 1/14/11 3:52 AM, Bruce Ferrell wrote:
>> select count(*) as count
>> from alerts where (unix_timestamp(stamp)> (unix_timestamp(now()) -
>> '300' ) )
>> and devid = '244';
>>
>
> Bruce -
>
> The problem is that the index is useless, because you're
>-Original Message-
>From: Bruce Ferrell [mailto:bferr...@baywinds.org]
>Sent: Friday, January 14, 2011 5:53 AM
>To: mysql@lists.mysql.com
>Subject: Rewrite SQL to stop table scan
>
>How would you rewrite the following SQL so that is doesn't do a full
>table
On 1/14/11 3:52 AM, Bruce Ferrell wrote:
select count(*) as count
from alerts where (unix_timestamp(stamp)> (unix_timestamp(now()) -
'300' ) )
and devid = '244';
Bruce -
The problem is that the index is useless, because you're running a
function on the timestamp. What you want is this:
S
On 1/14/2011 4:52 AM, Bruce Ferrell wrote:
How would you rewrite the following SQL so that is doesn't do a full
table scan. It does in fact do a full scan in spite of the time
clause. It's been
making me nuts for months.
select count(*) as count
from alerts where (unix_times
Hey,
Try making `id` as primary key. That will keep data in sorted manner and
scan will look only the data required in-spite of full table scan.
On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell wrote:
> How would you rewrite the following SQL so that is doesn't do a full
> table scan
can you send DDL of the table?
On Fri, Jan 14, 2011 at 4:22 PM, Bruce Ferrell wrote:
> How would you rewrite the following SQL so that is doesn't do a full
> table scan. It does in fact do a full scan in spite of the time
> clause. It's been
> making me nuts for months.
ofill it with a
trigger from stamp, or now() if that is appropriate.
On Fri, Jan 14, 2011 at 11:52 AM, Bruce Ferrell wrote:
> How would you rewrite the following SQL so that is doesn't do a full
> table scan. It does in fact do a full scan in spite of the time
> clause. It'
How would you rewrite the following SQL so that is doesn't do a full
table scan. It does in fact do a full scan in spite of the time
clause. It's been
making me nuts for months.
select count(*) as count
from alerts where (unix_timestamp(stamp) > (unix_timestamp(now()) -
'
On Fri, Apr 4, 2008 at 6:30 AM, Shanmugam, Dhandapani <
[EMAIL PROTECTED]> wrote:
Hi,
Hi,
>
> Any idea wat replicate-rewrite-db does with example..
>
>
>
>
It takes statements for one database, and rewrites them into another.
An example of the syntax would be this li
Hi,
Any idea wat replicate-rewrite-db does with example..
It worked in 4.x but does not work in the new syntax. How should I
rewrite it to get the same result?
OK, that was a lie. It works in 5.x as well. I should learn to
describe my problem more accurately as well as RTFM :-(
The correct description of the query in question would have been:
select
ted <
messages_.created where messages_.id is null
It worked in 4.x but does not work in the new syntax. How should I
rewrite it to get the same result?
TIA,
Ed
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I'm not certain how MySQL handles the specific case where some columns
in a record covered by a multi-column index are updated; it may update
the whole index entry, or just part of it, not sure. In any case,
yes, there is some overhead associated with having an index on columns
that get updated.
I agree that individual fields have relatively few possible values -
hopefully, when those are combined in a multi-column index, he will
have a greater number of unique combinations, gaining more out of the
index. That's why I suggested putting stype and Is_id as the first
two fields in the in
re 3 updates. But updates are of type "update something
where ip=ipaddr" and ipaddr is unique key.
What can cause slowdown in NDB case? Table is small and is in memory
(5.0 cluster). Maybe I can rewrite it in some better form for such case?
MySQL setting are basically default. I d
6 1:28 PM
> To: mysql@lists.mysql.com
> Subject: RE: RE: How to rewrite query
>
> Would it not be best to have the field with the fewest
> repeats (i.e., the
> closest to unique) first, or is that what you meant.
> Bill
>
> On Tue, October 17, 2006 10:12, Jerry Schwartz said:
ilto:[EMAIL PROTECTED]
>> Sent: Tuesday, October 17, 2006 12:05 PM
>> To: Jerry Schwartz
>> Cc: mos; mysql@lists.mysql.com
>> Subject: Re: RE: How to rewrite query
>>
>> I agree that individual fields have relatively few possible values -
>> hopefully, when
Farmington, CT 06032
>
> 860.674.8796 / FAX: 860.674.8341
>
>
>> -Original Message-
>> From: Dan Buettner [mailto:[EMAIL PROTECTED]
>> Sent: Tuesday, October 17, 2006 12:05 PM
>> To: Jerry Schwartz
>> Cc: mos; mysql@lists.mysql.com
>> Subject: Re
Subject: Re: RE: How to rewrite query
>
> I agree that individual fields have relatively few possible values -
> hopefully, when those are combined in a multi-column index, he will
> have a greater number of unique combinations, gaining more out of the
> index. That's why I sug
6 / FAX: 860.674.8341
> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 17, 2006 10:46 AM
> To: mysql@lists.mysql.com
> Subject: Re: How to rewrite query
>
> At 08:34 AM 10/17/2006, you wrote:
>
> > Hello,
> >
>
ington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
> -Original Message-
> From: mos [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, October 17, 2006 10:46 AM
> To: mysql@lists.mysql.com
> Subject: Re: How to rewrite query
>
> At 08:34 AM 10/17/2006, you wr
lot of updates to ipaddr table too. For every
select there are 3 updates. But updates are of type "update something
where ip=ipaddr" and ipaddr is unique key.
What can cause slowdown in NDB case? Table is small and is in memory
(5.0 cluster). Maybe I can rewrite it in some better for
You should strongly consider adding an index on the fields you're
querying against. Right now, none of the fields in your query are
indexed in the table.
I would try something like this for starters: a multi-column index
against all the fields in the query you showed. If you have other
queries
B case? Table is small and is in memory (5.0
cluster). Maybe I can rewrite it in some better form for such case?
MySQL setting are basically default. I did not find something in
documentation about improving performance of NDB engine tables.
Maybe increase read_buffer_size which is currently the
. For every select
there are 3 updates. But updates are of type "update something where
ip=ipaddr" and ipaddr is unique key.
What can cause slowdown in NDB case? Table is small and is in memory (5.0
cluster). Maybe I can rewrite it in some better form for such case?
MySQL setting ar
gt; leave it this way, they therefore asked if it was possible to replicate to a
> different database
> name, e.g. livedb_backup.
>
> I suggested the use of the --replicate-rewrite-db option.
>
> When I set this up and tested it I decided to create a new table and then
>
the master and the customer wants
to
leave it this way, they therefore asked if it was possible to replicate to a
different database
name, e.g. livedb_backup.
I suggested the use of the --replicate-rewrite-db option.
When I set this up and tested it I decided to create a new table and then d
will have
- a row for each appointment in the given range
- a row for each patient who doesn't have an appointment
Then the where clause throws away the ones with appointments.
regards,
Bill
> From: "lorenzo.kh" <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED
Hi,
I have 2 tables.patientrecall_table
and appointment_table.The patientrecall_table used to store what is the
nextrecall date for the patient while the appointment_tablewill store
all the appointments made.Now, I'd like to get through this:"Patients
Due for Recall, but No Scheduled Appo
Would table names still be case sensitive if the connection is done through
odbc?
Diana Soares wrote:
Read the first item that appears in MySQL manual if you search for "case
sensitivity":
http://www.mysql.com/doc/en/Name_case_sensitivity.html
--
MySQL General Mailing List
For list archives: ht
On Thu, 2003-07-24 at 11:58, mixo wrote:
> How can I write this query so it works on mysql 3.54:
>
> select groupmembers.memberid,users.name from groupmembers,users where
> (not users.name='root')
> and groupmembers.groupid=(select groups.id from
> grou
How can I write this query so it works on mysql 3.54:
select groupmembers.memberid,users.name from groupmembers,users where
(not users.name='root')
and groupmembers.groupid=(select groups.id from
groups where groups.type='Privileged')
hi,
i have the following query working perfectly with Dbase 4, but it crashes in
mysql :
SELECT SUM(DISTINCT A.AMOUNT) AS AMT FROM table1 A
I'd like to get that amount in only one query if possible, even though i
could do it by using a temporary table, selecting into it all the distinct
amoun
I have the same db on master and slave and I want to use
replicate-rewrite-db to change the replicating name of this db on the
slave.
Slave my.cnf:
---
[mysqld]
datadir=/var/lib/mysql
socket=/tmp/mysql.sock
master-host=master.company.com
master-user=repl
master
David,
Tuesday, November 19, 2002, 5:20:23 PM, you wrote:
DN> (sorry if this is a duplicate -- I don't think the original made it)
DN> Running mysql 3.23.53-max binary on a RedHat 7.2 based server. Replication
DN> is working fine, but I'm running into errors with the repl
Hi All,
(sorry if this is a duplicate -- I don't think the original made it)
Running mysql 3.23.53-max binary on a RedHat 7.2 based server. Replication
is working fine, but I'm running into errors with the replicate-rewrite-db
directive. Here's a snippet from my my.cnf fi
Hi All,
Running mysql 3.23.52-max binary on a RedHat 7.2 based server. Replication
is working fine, but I'm running into errors with the replicate-rewrite-db
directive. Here's a snippet from my my.cnf file on the slave:
master-port = 3306
replicate-rewrite-db=mysql->maste
Hello (Post 2)
Has anyone got replicate-rewrite-db (a my.cnf entry) to work. I have replication
running fine, however the rewrite doesn't work.
I just want to know whether I should persist or if there is something special people
had to do to get it working. I have heard / read a few p
Hello
Has anyone got replicate-rewrite-db (a my.cnf entry) to work. I have replication
running fine, however the rewrite doesn't work.
I just want to know whether I should persist or if there is something special people
had to do to get it working. I have heard / read a few people saying
PROTECTED]
> Subject: Re: How to rewrite this statement.
>
>
> Ok, so I updated the statement like the past few posts have
> directed so the
> statement looks like this:
>
> SELECT FORUMCAT1.LASTPOST, USERS.USERNAME, FORUMCAT1.ID,
> FORUMCAT1.TOPIC,
> FORUMCAT1.REPL
: How to rewrite this statement.
Ok, so I updated the statement like the past few posts have directed so the
statement looks like this:
SELECT FORUMCAT1.LASTPOST, USERS.USERNAME, FORUMCAT1.ID, FORUMCAT1.TOPIC,
FORUMCAT1.REPLIES, FORUMCAT1.ORIGINATOR, FORUMCAT1.STATUS
FROM USERS INNER JOIN
ON
FORUMCAT1.ID = FORUMMSG1.POSTNUMBER)ON USERS' at line 1.
Thanks,
Mat
- Original Message -
From: "Nathan" <[EMAIL PROTECTED]>
To: "Mat Murdock" <[EMAIL PROTECTED]>
Sent: Friday, February 22, 2002 12:03 PM
Subject: Re: How to rewrite this statement.
From: Mat Murdock <[EMAIL PROTECTED]>
> ... Like "*java*"
Try:
Like "%java%"
I would bet the the aseriscs are some non-SQL extention of MS Access.
---
Rodney Broom
Programmer: Desert.Net
-
Before posting, please check:
I have this query that works just fine in msacess agains some linked mysql
tables, but if I run this statement in php or in mysqlfront it doesn't work.
I belive it's because there is two joins in it but i'm not sure. If that is
the problem then what is the work around? Here is the statement:
SE
ywords, kw_link
WHERE locations.locationid = kw_link.locationid AND keywords.kwid =
kw_link.kw_id
GROUP BY kw_link.kw_id;
...but it appears that I can't use that to UPDATE the Keyword table.
Ideally I'd simply update the LocationCount field in Keywords with
the appropriate cou
> This is how I'd like the query to work:
> insert into host (host_id, host_IP) values ((select from mail
> mail_id where
> mail_addr = "[EMAIL PROTECTED]"), '127.0.0.1')
Try:
insert into host (host_id, host_IP) select mail_id, '127.0.0.1' from mail
where mail_addr = '[EMAIL PROTECTED]';
-
As I've understood it mysql doesn't support sub-selects inside for example
insert statements
(apart from inset ... select).
Background-stuff:
What I'm working on is parsing logfiles and inserting them into a mysql
database through the C API,
to minimize traffic and to maximize time, I was hopin
49 matches
Mail list logo
