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 |
+----+--------------------+----------+----------+---------------------+---------------------+---------+------+-------+--------------------------+
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
