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 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. ...<br>Please visit <a
> > href="http://www.careersite.
> > com/perl/vaui/Search/top/job/9F064-1B68B?pid=295&matches.page=5"><b>THIS
> > LINK </b></a>for more information and to apply.<br> | 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 |
> > +----+-------------+---------+-------+---------------+-------------
> > +---------+------+-------+--------------------------+
> >
> >
> >
> > Explain for Query 2:
> > MODE) AS score FROM listings WHERE MATCH(title, descr_part) AGAINST
> > ("manager" IN BOOLEAN MODE ) limit 5;
> > +----+-------------+----------+----------+---------------------
> > +---------------------+---------+------+------+-------------+
> > | id | select_type | table | type | possible_keys | key
> > | key_len | ref | rows | Extra |
> > +----+-------------+----------+----------+---------------------
> > +---------------------+---------+------+------+-------------+
> > | 1 | SIMPLE | listings | fulltext | title_desc_part_txt |
> > title_desc_part_txt | 0 | | 1 | Using where |
> > +----+-------------+----------+----------+---------------------
> > +---------------------+---------+------+------+-------------+
> > 1 row in set (0.02 sec)
> >
> >
> > Explaine for Full(SLOW) Query.....
> > mysql> explain 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;
> > +----+--------------------+----------+----------
> > +---------------------+---------------------+---------+------
> > +-------+--------------------------+
> > | id | select_type | table | type | possible_keys
> > | key | key_len | ref | rows | Extra
> > |
> > +----+--------------------+----------+----------
> > +---------------------+---------------------+---------+------
> > +-------+--------------------------+
> > | 1 | PRIMARY | listings | fulltext | title_desc_part_txt
> > | title_desc_part_txt | 0 | | 1 | Using where
> > |
> > | 2 | DEPENDENT SUBQUERY | zipData | index | zipcode,zip_lat_lon
> > | zip_lat_lon | 21 | NULL | 42037 | Using where; Using
> > index |
> > +----+--------------------+----------+----------
> > +---------------------+---------------------+---------+------
> > +-------+--------------------------+
> >
>
> I would separate your query into two steps and use a JOIN rather than a
> subquery.
>
> CREATE TEMPORARY TABLE tmpZips
> 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);
>
> SELECT *,MATCH(title, descr_part) AGAINST ("Project Manager" IN BOOLEAN
> MODE) AS score
> FROM listings
> INNER JOIN tmpZips
> ON tmpZips.zipcode = listings.zip
> WHERE MATCH(title, descr_part) AGAINST ("Project Manager" IN BOOLEAN MODE );
>
> DROP TABLE tmpZips;
>
> See how well that performs over your other query.
>
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
