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
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.
Mindaugas, can you post the output of
SHOW CREATE TABLE ipaddr;
and
EXPLAIN select ip from ipaddr
where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
limit 1;
When you say it's too slow, how slow is it? And how fast when it is a
memory table?
Also, which specific version
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
At 08:34 AM 10/17/2006, you wrote:
Hello,
For the Radius server we're using MySQL cluster and the following query
looks too slow:
select ip from ipaddr
where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
limit 1;
Table ipaddr is small (~6MB, 38000 records). Fields in
.
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 wrote:
Hello,
For the Radius
-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,
For the Radius server we're using MySQL cluster and the
following query
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 wrote:
Hello,
For the Radius
-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: RE: How to rewrite query
I agree that individual fields have relatively few possible values -
hopefully, when those
@lists.mysql.com
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 suggested
@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:
I didn't think of that (combinations). You
Hi!
Try:
set engine_condition_pushdown = on;
explain select ip from ipaddr
where pool='INTERNET' and stype='S' and ls_id=3 and allocated is null
limit 1;
to see if you can push the predicates to improve performance.
BR
-- Martin
Mindaugas wrote:
Hello,
For the Radius server we're using
12 matches
Mail list logo