(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
