Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

w2ksp4.exe, WwwxxXdbf, procmail "FROM_MAILER" patch, Use of assignment to $[ is deprecated at /usr/local/sbin/apxs line 86. , wwwxxx vim, mysql closing table and opening table, 800c5000, setgid operation not permitted, pciehp: acpi_pciehprm on IBM, WWWXXX.DBF

Links

XODOX
Impressum

#1: query optimization

Posted on 2011-09-22 20:44:29 by supr_star

--1529059597-868144110-1316717069=:93572
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

=A0I have a table with 24 million rows, I need to figure out how to o=
ptimize a query. =A0It has to do with mac addresses and radius packets - I =
want to see the # of connections and the min/max date. So I basically want =
all this data: =A0 select cpe_mac,count(*) c,min(r3_dt) mindt,max(r3_d=
t) maxdt, max(rec_num) recn  =A0 from radiuscap  =A0 where r3_dt>=
=3DSUBDATE(NOW(),INTERVAL 30 DAY)  =A0 =A0 and r3_type=3D'Access'  =
=A0 group by cpe_mac order by cpe_mac=0A; This piece of the query take=
s 30 seconds to run and produces 3500 rows. =A0I have r3_dt indexed. =A0I a=
lso want a status field of the row with the highest r3_dt: select rec_=
num,cpe_mac,req_status  from rad_r3cap=0Awhere r3_type=3D'Access'  =
and (cpe_mac,r3_dt) in=A0(select cpe_mac,max(r3_dt) from rad_r3cap)=0A;=0A=
=0AThis piece of the query takes forever,  I let it run for an hour and=
it still didn't finish, it's obviously not using indexes. =A0I have no ide=
a how far along it got. =A0I wrote a php script to run the 1st query, then =
do 3500 individual lookups for the status using the max(rec_num) field in t=
he 1st query, and I can get the data in 31 seconds. =A0So I CAN produce thi=
s data, but very slowly, and not in 1 sql query. =A0I want to consolidate t=
his into 1 sql so I can make a view. If anyone can point me in the rig=
ht direction, I'd appreciate it! mysql> desc rad_r3cap;=0A+-----=
--------+-------------+------+-----+---------+-------------- --+=0A| Field =
=A0 =A0 =A0 | Type =A0 =A0 =A0 =A0| Null | Key | Default | Extra =A0 =A0 =
=A0 =A0 =A0|=0A+-------------+-------------+------+-----+---------+- -------=
--------+=0A| rec_num =A0 =A0 | int(11) =A0 =A0 | NO =A0 | PRI | NULL =A0 =
=A0| auto_increment |=0A| r3_dt =A0 =A0 =A0 | datetime =A0 =A0| YES =A0| MU=
L | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| r3_micros =A0 | int(=
11) =A0 =A0 | YES =A0| =A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =
=A0|=0A| r3_type =A0 =A0 | varchar(16) | YES =A0| =A0 =A0 | NULL =A0 =A0| =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| req_status =A0| varchar(16) | YES =A0|=
=A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| req_comment |=
varchar(64) | YES =A0| =A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0=
=A0|=0A| asn_ip =A0 =A0 =A0| varchar(16) | YES =A0| MUL | NULL =A0 =A0| =
=A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| asn_name =A0 =A0| varchar(16) | YES =
=A0| =A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| bsid =A0 =
=A0 =A0 =A0| varchar(12) | YES =A0| MUL | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0=
=A0 =A0 =A0|=0A| cpe_ip =A0 =A0 =A0| varchar(16) | YES =A0| =A0 =A0 | NULL=
=A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| cpe_mac =A0 =A0 | varchar(12=
) | YES =A0| MUL | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| filen=
ame =A0 =A0| varchar(32) | YES =A0| =A0 =A0 | NULL =A0 =A0| =A0 =A0 =A0 =A0=
=A0 =A0 =A0 =A0|=0A| linenum =A0 =A0 | int(11) =A0 =A0 | YES =A0| =A0 =A0 =
| NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=0A| r3_hour =A0 =A0 | date=
time =A0 =A0| YES =A0| MUL | NULL =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0|=
=0A| user_name =A0 | varchar(64) | YES =A0| =A0 =A0 | NULL =A0 =A0| =A0 =A0=
=A0 =A0 =A0 =A0 =A0 =A0|=0A+-------------+-------------+------+-----+-----=
----+----------------+ mysql> show indexes in rad_r3cap;=0A+----------=
-+------------+--------------+--------------+-------------+- ----------+----=
---------+----------+--------+------+------------+---------+ =0A| Table =A0 =
=A0 | Non_unique | Key_name =A0 =A0 | Seq_in_index | Column_name | Collatio=
n | Cardinality | Sub_part | Packed | Null | Index_type | Comment |=0A+----=
-------+------------+--------------+--------------+--------- ----+----------=
-+-------------+----------+--------+------+------------+---- -----+=0A| rad_=
r3cap | =A0 =A0 =A0 =A0 =A00 | PRIMARY =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =A0=
1 | rec_num =A0 =A0 | A =A0 =A0 =A0 =A0 | =A0 =A023877677 | =A0 =A0 NULL | =
NULL =A0 | =A0 =A0 =A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap =
| =A0 =A0 =A0 =A0 =A00 | r3cap_dt =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A01 | r3_d=
t =A0 =A0 =A0 | A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0NULL | =A0 =A0 NULL | NU=
LL =A0 | YES =A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap | =A0 =
=A0 =A0 =A0 =A00 | r3cap_dt =A0 =A0 | =A0 =A0 =A0 =A0 =A0 =A02 | r3_micros =
=A0 | A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0NULL | =A0 =A0 NULL | NULL =A0 | Y=
ES =A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap | =A0 =A0 =A0 =
=A0 =A01 | r3cap_bsid =A0 | =A0 =A0 =A0 =A0 =A0 =A01 | bsid =A0 =A0 =A0 =A0=
| A =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 346 | =A0 =A0 NULL | NULL =A0 | YES =
=A0| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap | =A0 =A0 =A0 =A0 =
=A01 | r3cap_asnip =A0| =A0 =A0 =A0 =A0 =A0 =A01 | asn_ip =A0 =A0 =A0| A =
=A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =A055 | =A0 =A0 NULL | NULL =A0 | YES =A0=
| BTREE =A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap | =A0 =A0 =A0 =A0 =A01=
| r3cap_cpemac | =A0 =A0 =A0 =A0 =A0 =A01 | cpe_mac =A0 =A0 | A =A0 =A0 =
=A0 =A0 | =A0 =A0 =A0 =A04758 | =A0 =A0 NULL | NULL =A0 | YES =A0| BTREE =
=A0 =A0 =A0| =A0 =A0 =A0 =A0 |=0A| rad_r3cap | =A0 =A0 =A0 =A0 =A01 | r3cap=
_date =A0 | =A0 =A0 =A0 =A0 =A0 =A01 | r3_hour =A0 =A0 | A =A0 =A0 =A0 =A0 =
| =A0 =A0 =A0 =A01548 | =A0 =A0 NULL | NULL =A0 | YES =A0| BTREE =A0 =A0 =
=A0| =A0 =A0 =A0 =A0 |=0A+-----------+------------+--------------+---------=
-----+-------------+-----------+-------------+----------+--- -----+------+--=
----------+---------+=0A7 rows in set (0.00 sec)=0A
--1529059597-868144110-1316717069=:93572--

Report this message

#2: Re: query optimization

Posted on 2011-09-22 20:53:50 by Ananda Kumar

--20cf30563c37aa674704ad8c3695
Content-Type: text/plain; charset=ISO-8859-1

Your outer query "select cpe_mac,max(r3_dt) from rad_r3cap", is doing a full
table scan, you might want to check on this and use a "WHERE" condition to
use indexed column

On Fri, Sep 23, 2011 at 12:14 AM, supr_star <suprstar1072@yahoo.com> wrote:

>
>
> I have a table with 24 million rows, I need to figure out how to optimize
> a query. It has to do with mac addresses and radius packets - I want to see
> the # of connections and the min/max date. So I basically want all this
> data:
>
> select cpe_mac,count(*) c,min(r3_dt) mindt,max(r3_dt) maxdt, max(rec_num)
> recn
> from radiuscap
> where r3_dt>=SUBDATE(NOW(),INTERVAL 30 DAY)
> and r3_type='Access'
> group by cpe_mac order by cpe_mac
> ;
>
> This piece of the query takes 30 seconds to run and produces 3500 rows. I
> have r3_dt indexed. I also want a status field of the row with the highest
> r3_dt:
>
> select rec_num,cpe_mac,req_status
> from rad_r3cap
> where r3_type='Access'
> and (cpe_mac,r3_dt) in (select cpe_mac,max(r3_dt) from rad_r3cap)
> ;
>
> This piece of the query takes forever, I let it run for an hour and it
> still didn't finish, it's obviously not using indexes. I have no idea how
> far along it got. I wrote a php script to run the 1st query, then do 3500
> individual lookups for the status using the max(rec_num) field in the 1st
> query, and I can get the data in 31 seconds. So I CAN produce this data,
> but very slowly, and not in 1 sql query. I want to consolidate this into 1
> sql so I can make a view.
>
> If anyone can point me in the right direction, I'd appreciate it!
>
>
>
> mysql> desc rad_r3cap;
> +-------------+-------------+------+-----+---------+-------- --------+
> | Field | Type | Null | Key | Default | Extra |
> +-------------+-------------+------+-----+---------+-------- --------+
> | rec_num | int(11) | NO | PRI | NULL | auto_increment |
> | r3_dt | datetime | YES | MUL | NULL | |
> | r3_micros | int(11) | YES | | NULL | |
> | r3_type | varchar(16) | YES | | NULL | |
> | req_status | varchar(16) | YES | | NULL | |
> | req_comment | varchar(64) | YES | | NULL | |
> | asn_ip | varchar(16) | YES | MUL | NULL | |
> | asn_name | varchar(16) | YES | | NULL | |
> | bsid | varchar(12) | YES | MUL | NULL | |
> | cpe_ip | varchar(16) | YES | | NULL | |
> | cpe_mac | varchar(12) | YES | MUL | NULL | |
> | filename | varchar(32) | YES | | NULL | |
> | linenum | int(11) | YES | | NULL | |
> | r3_hour | datetime | YES | MUL | NULL | |
> | user_name | varchar(64) | YES | | NULL | |
> +-------------+-------------+------+-----+---------+-------- --------+
>
> mysql> show indexes in rad_r3cap;
>
> +-----------+------------+--------------+--------------+---- ---------+-----------+-------------+----------+--------+---- --+------------+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +-----------+------------+--------------+--------------+---- ---------+-----------+-------------+----------+--------+---- --+------------+---------+
> | rad_r3cap | 0 | PRIMARY | 1 | rec_num | A
> | 23877677 | NULL | NULL | | BTREE | |
> | rad_r3cap | 0 | r3cap_dt | 1 | r3_dt | A
> | NULL | NULL | NULL | YES | BTREE | |
> | rad_r3cap | 0 | r3cap_dt | 2 | r3_micros | A
> | NULL | NULL | NULL | YES | BTREE | |
> | rad_r3cap | 1 | r3cap_bsid | 1 | bsid | A
> | 346 | NULL | NULL | YES | BTREE | |
> | rad_r3cap | 1 | r3cap_asnip | 1 | asn_ip | A
> | 55 | NULL | NULL | YES | BTREE | |
> | rad_r3cap | 1 | r3cap_cpemac | 1 | cpe_mac | A
> | 4758 | NULL | NULL | YES | BTREE | |
> | rad_r3cap | 1 | r3cap_date | 1 | r3_hour | A
> | 1548 | NULL | NULL | YES | BTREE | |
>
> +-----------+------------+--------------+--------------+---- ---------+-----------+-------------+----------+--------+---- --+------------+---------+
> 7 rows in set (0.00 sec)
>

--20cf30563c37aa674704ad8c3695--

Report this message