Re: SLOW Mysql Subquery
Shawn-
Works like a champ! Thanks so much. If your ever in the Silicon
Valley area drop me a line, and I will be happy to pick up lunch or
cocktails.
It's great that you are so active on this list and are helping so many
folks on here (myself included).
-Dale
On Fri, 25 Feb 2005 10:06:36 -0500, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
>
> (response NOT top-posted. see below...)
>
> Dale Roddy <[EMAIL PROTECTED]> wrote on 02/24/2005 05:40:22 PM:
>
>
> > I am new to MySql. I have a query with a subselect that is running
> > very slow (28 seconds).
> >
> > SELECT *,MATCH(title, descr_part) AGAINST ("Project Manager" IN
> > BOOLEAN MODE) AS score
> > FROM listings
> > WHERE MATCH(title, descr_part) AGAINST ("Project Manager" IN BOOLEAN MODE
> )
> > and zip in
> > (SELECT zipcode FROM zipData
> > WHERE (POW((69.1*(lon-37.57348)*cos(122.3225/57.3)),2)+POW((69.
> > 1*(lat-122.3225)),2))<(10*10)
> > )
> > limit 10;
> >
> >
> > Both tables do have a lot of Records. When I run them seperatly, they
> > are both very fast (see below). As stated above, I am very new to
> > this, so please be gentel. ;-) Any sugestions on how to do this
> > better/different would be greatlfuly appreciated.
> >
> >
> >
> >
> > Table 1 (listings);
> > +-+--+--+-+-++
> > | Field | Type | Null | Key | Default | Extra |
> > +-+--+--+-+-++
> > | lst_id | bigint(20) | | PRI | NULL| auto_increment |
> > | cat_id | bigint(20) | | | 0 ||
> > | sub_cat_id | bigint(20) | | | 0 ||
> > | mem_id | bigint(20) | | | 0 ||
> > | trb_id | bigint(20) | | | 0 ||
> > | added | bigint(20) | | | 0 ||
> > | title | varchar(250) | | MUL | ||
> > | description | text | | | ||
> > | descr_part | varchar(250) | | MUL | ||
> > | photo | varchar(250) | | | ||
> > | privacy | char(1) | | | n ||
> > | anonim | char(1) | | | n ||
> > | zip | int(10) | | MUL | 0 ||
> > | show_deg| char(3) | | | any ||
> > | stat| char(1) | | | p ||
> > | live| bigint(20) | | | 0 ||
> > +-+--+--+-+-++
> >
> > Table 2 (zipdata);
> > +-+--+--+-+-+---+
> > | Field | Type | Null | Key | Default | Extra |
> > +-+--+--+-+-+---+
> > | country | bigint(20) | | | 0 | |
> > | zipcode | varchar(5) | | PRI | | |
> > | lon | varchar(8) | | MUL | | |
> > | lat | varchar(8) | | MUL | | |
> > | city| varchar(250) | | MUL | | |
> > | state | char(3) | | MUL | | |
> > +-+--+--+-+-+---+
> >
> >
> > Query 1 : SELECT zipcode FROM zipData WHERE
> > (POW((69.1*(lon-"37.57348")*cos(122.3225/57.3)),"2")+POW((69.
> > 1*(lat-"122.3225")),"2"))<(5*5);
> > +-+
> > | zipcode |
> > +-+
> > | 94002 |
> > | 94010 |
> > | 94070 |
> > | 94401 |
> > | 94402 |
> > | 94403 |
> > | 94404 |
> > | 94497 |
> > +-+
> > 8 rows in set (0.33 sec)
> >
> > Query 2 SELECT *, MATCH(title, descr_part) AGAINST ("manager" IN
> > BOOLEAN MODE) AS score FROM listings WHERE MATCH(title, descr_part)
> > AGAINST ("manager" IN BOOLEAN MODE ) limit 5;
> >
> > Won't put all the output h
SLOW Mysql Subquery
I am new to MySql. I have a query with a subselect that is running
very slow (28 seconds).
SELECT *,MATCH(title, descr_part) AGAINST ("Project Manager" IN
BOOLEAN MODE) AS score
FROM listings
WHERE MATCH(title, descr_part) AGAINST ("Project Manager" IN BOOLEAN MODE )
and zip in
(SELECT zipcode FROM zipData
WHERE
(POW((69.1*(lon-37.57348)*cos(122.3225/57.3)),2)+POW((69.1*(lat-122.3225)),2))<(10*10)
)
limit 10;
Both tables do have a lot of Records. When I run them seperatly, they
are both very fast (see below). As stated above, I am very new to
this, so please be gentel. ;-) Any sugestions on how to do this
better/different would be greatlfuly appreciated.
Table 1 (listings);
+-+--+--+-+-++
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-++
| lst_id | bigint(20) | | PRI | NULL| auto_increment |
| cat_id | bigint(20) | | | 0 ||
| sub_cat_id | bigint(20) | | | 0 ||
| mem_id | bigint(20) | | | 0 ||
| trb_id | bigint(20) | | | 0 ||
| added | bigint(20) | | | 0 ||
| title | varchar(250) | | MUL | ||
| description | text | | | ||
| descr_part | varchar(250) | | MUL | ||
| photo | varchar(250) | | | ||
| privacy | char(1) | | | n ||
| anonim | char(1) | | | n ||
| zip | int(10) | | MUL | 0 ||
| show_deg| char(3) | | | any ||
| stat| char(1) | | | p ||
| live| bigint(20) | | | 0 ||
+-+--+--+-+-++
Table 2 (zipdata);
+-+--+--+-+-+---+
| Field | Type | Null | Key | Default | Extra |
+-+--+--+-+-+---+
| country | bigint(20) | | | 0 | |
| zipcode | varchar(5) | | PRI | | |
| lon | varchar(8) | | MUL | | |
| lat | varchar(8) | | MUL | | |
| city| varchar(250) | | MUL | | |
| state | char(3) | | MUL | | |
+-+--+--+-+-+---+
Query 1 : SELECT zipcode FROM zipData WHERE
(POW((69.1*(lon-"37.57348")*cos(122.3225/57.3)),"2")+POW((69.1*(lat-"122.3225")),"2"))<(5*5);
+-+
| zipcode |
+-+
| 94002 |
| 94010 |
| 94070 |
| 94401 |
| 94402 |
| 94403 |
| 94404 |
| 94497 |
+-+
8 rows in set (0.33 sec)
Query 2 SELECT *, MATCH(title, descr_part) AGAINST ("manager" IN
BOOLEAN MODE) AS score FROM listings WHERE MATCH(title, descr_part)
AGAINST ("manager" IN BOOLEAN MODE ) limit 5;
Won't put all the output here but here is an example
| 93 | 9000 | 9001 | 63 | 0 | 1109027856 |
Architect project manager | ARCHITECTS Do you fearlessly create? Do
you intelligently realize? Do you collaboratively develop? We do. Come
join us! Looking for designers (Architects & Interiors) that want to
make a difference. ...Please visit http://www.careersite.com/perl/vaui/Search/top/job/9F064-1B68B?pid=295&matches.page=5";>THIS
LINK for more information and to apply. | ARCHITECTS Do
you fe | no| n | n | 95401 | any | a| 2592000
| 1 |
+++++++---++--+---+-++---+--+--+-+---+
5 rows in set (0.00 sec)
Explain for query 1:
++-+-+---+---+-+-+--+---+--+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra|
++-+-+---+---+-+-+--+---+--+
| 1 | SIMPLE | zipData | index | NULL | zip_lat_lon |
21 | NULL | 42037 | Using where; Using index |
++-+-+---+---+-+-+--+---+---
