List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 25 2005 3:06pm
Subject:Re: SLOW Mysql Subquery
View as plain text  
(response NOT top-posted. see below...)

Dale Roddy <droddy@stripped> 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

Thread
SLOW Mysql SubqueryDale Roddy24 Feb
  • Re: SLOW Mysql SubquerySGreen25 Feb
    • Re: SLOW Mysql SubqueryDale Roddy25 Feb