List:General Discussion« Previous MessageNext Message »
From:Dale Roddy Date:February 25 2005 3:47pm
Subject:Re: SLOW Mysql Subquery
View as plain text  
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, SGreen@stripped <SGreen@stripped> wrote:
> 
> (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