MySQL Forums
Forum List  »  Newbie

Slow select with sub queries
Posted by: Tom Clark
Date: December 01, 2010 03:00AM

The following query takes about 28 seconds to run, when the application really demands no more than 5 seconds or so.

I anticipate the database growing many times its current size, so this is a
problem that should be solved now.


SELECT DISTINCT ID FROM Main 
	WHERE ID IN (SELECT DISTINCT Main_ID FROM Coordinates
		WHERE   Lon BETWEEN -149.843812 AND -149.369494
		AND     Lat BETWEEN 61.013580 AND 61.129084) 
	AND 
	ID IN (SELECT Main.ID FROM Main 
		WHERE Main.Status = '1' 
		AND Main.Public = '1')

Here is the output from "EXPLAIN SELECT (above query)"
ID	Select Type	Table		type		possible_keys	key	key_len	ref	rows	Extra
1	PRIMARY		Main		range		NULL		PRIMARY	4	NULL	249	Using where; Using index for group-by
3	DEPENDENT SUBQY	Main		unique_subquery	PRIMARY		PRIMARY	4	func	1	Using where
2	DEPENDENT SUBQY	Coordinates	ALL		NULL		NULL	NULL	NULL	69695	Using where; Using temporary

I think this is telling me that it is processing 249*69695 = 17+ million records, which can be time consuming. So, I need to go about this in a different way?


Table "Main" = 249 rows
====== Main Table =================================
ID		int(11) 	NO PRI  auto_increment
Type 		int(2) 		NO
Name 		tinytext 	YES
Class 		int(2) 		YES
Elevation 	int(5) 		YES
Length 		decimal(6,1) 	YES
Region 		int(4) 		NO
Description 	text 		YES
Notes 		tinytext 	YES
Date_Modified 	timestamp 	NO  CURRENT_TIMESTAMP
NumberOfPoints 	int(8) 		YES
Status 		int(1) 		NO
Public 		int(1) 		NO
Author 		int(11) 	NO


Table "Coordinates" = 69695 rows
Data describes trails or routes with latitude and longitude points. Each row
has a unique ID (auto incremented), and anywhere from 1 to hundreds of points can be associated with the same Main_ID, which is
a foreign key relating to ID in table "Main".
=== Coordinates Table ========================
ID 	int(12) 	NO PRI  auto_increment
Main_ID int(11) 	NO
Type 	int(2) 		NO
Lat 	decimal(11,6) 	NO
Lon 	decimal(11,6) 	NO
Z 	decimal(15,6) 	NO

Here's some typical data from "Coordinates"
ID   Main_ID  Type  Lat       Lon         Z
3068 5        2     64.953995 -147.355129 244.312256
3069 5        2     64.954458 -147.354664 242.870239
3070 5        2     64.955024 -147.354323 244.792847



Edited 5 time(s). Last edit at 12/01/2010 06:04AM by Tom Clark.

Options: ReplyQuote


Subject
Written By
Posted
Slow select with sub queries
December 01, 2010 03:00AM
December 01, 2010 01:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.