Extremely Slow Binary Search

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Extremely Slow Binary Search

Post by WanamakerStudios »

I am not sure what might be going on ... hopefully someone could shed some light on it for me. I am currently running searchs on a DB with over 500,000 records. Since some of the fields have Ax and AX as two different IDs, I changed the type of field to BINARY. However, when I do a search against that field, it takes nearly 19 seconds to display any results. If I change it back, it works fine. I've also tried using the BINARY clause in my SELECT statement with the same results. Any ideas what might be causing this?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

Is the column indexed? Have you rebuilt the index?
(#10850)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Did you also create an INDEX on the (binary) column?
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Yes. I have index the field and optimized the table ...
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

What types are these fields being stored as?
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

CHAR and VARCHAR
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Any one with any ideas? I've tried a thousand different things ... and no luck!
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

What query do you use?
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Im not sure I know what you mean ...
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Please show me your sql statement.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

feyd | Please use

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]


[syntax="sql"]
		SELECT DISTINCT CVIN, CONCAT(CYEAR, ' ', CDESCRIPTION, ' ', CMODEL) AS VEHICLE, CONCAT(o1.DESCRIPTION, ', ', o2.DESCRIPTION, ', ', o3.DESCRIPTION, ', ', o4.DESCRIPTION, ', ', o5.DESCRIPTION, ', ', o6.DESCRIPTION) AS OPTIONS, CPHONE, CCOLOR, CMILEAGE, CPRICE, CPRICING, STATUS, round(69.1 * sqrt((POW((z.latitude-". $latitude ."),2)) + .6 * (POW((z.longitude-". $longitude ."),2)))) AS DISTANCE
		FROM `lotdata` l
		LEFT JOIN `vehicles` v
		ON v.CIDLOTD = l.CIDLOTD
		LEFT JOIN `premiumdealers` p
		ON p.lotid = v.CIDLOTD
		LEFT JOIN `zipcodes` z
		ON l.CZIP = z.zip_code
		LEFT JOIN `carparts` cp
		ON cp.CIDCARP = v.CMAKE
		LEFT JOIN `options` o1
		ON o1.CIDOPTI = v.O1
		LEFT JOIN `options` o2
		ON o2.CIDOPTI = v.O2
		LEFT JOIN `options` o3
		ON o3.CIDOPTI = v.O3
		LEFT JOIN `options` o4
		ON o4.CIDOPTI = v.O4	
		LEFT JOIN `options` o5
		ON o5.CIDOPTI = v.O5
		LEFT JOIN `options` o6
		ON o6.CIDOPTI = v.O6
		WHERE cp.CCATEGORY = 'MAKE'
		AND CDESCRIPTION = '" . $_GET['make'] . "'
		AND v.CPRICE <> '0'
		AND v.CYEAR BETWEEN '" . $_GET['minyear'] . "' AND '" . $_GET['maxyear'] . "'
		AND v.CMODEL LIKE '%" . $_GET['model'] . "%'
		HAVING DISTANCE <= '". $_GET['radius'] ."'
		ORDER BY STATUS DESC, ". $_GET['sortby'] ." ". $_GET['order'] ."";

feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

please put

Code: Select all

/** DEBUG - EXPLAIN - start */
$query = "EXPLAIN SELECT DISTINCT CVIN, CONCAT(CYEAR, ' ', CDESCRIPTION, ' ', CMODEL) AS VEHICLE, CONCAT(o1.DESCRIPTION, ', ', o2.DESCRIPTION, ', ', o3.DESCRIPTION, ', ', o4.DESCRIPTION, ', ', o5.DESCRIPTION, ', ', o6.DESCRIPTION) AS OPTIONS, CPHONE, CCOLOR, CMILEAGE, CPRICE, CPRICING, STATUS, round(69.1 * sqrt((POW((z.latitude-". $latitude ."),2)) + .6 * (POW((z.longitude-". $longitude ."),2)))) AS DISTANCE
	FROM `lotdata` l
	LEFT JOIN `vehicles` v
	ON v.CIDLOTD = l.CIDLOTD
	LEFT JOIN `premiumdealers` p
	ON p.lotid = v.CIDLOTD
	LEFT JOIN `zipcodes` z
	ON l.CZIP = z.zip_code
	LEFT JOIN `carparts` cp
	ON cp.CIDCARP = v.CMAKE
	LEFT JOIN `options` o1
	ON o1.CIDOPTI = v.O1
	LEFT JOIN `options` o2
	ON o2.CIDOPTI = v.O2
	LEFT JOIN `options` o3
	ON o3.CIDOPTI = v.O3
	LEFT JOIN `options` o4
	ON o4.CIDOPTI = v.O4   
	LEFT JOIN `options` o5
	ON o5.CIDOPTI = v.O5
	LEFT JOIN `options` o6
	ON o6.CIDOPTI = v.O6
	WHERE cp.CCATEGORY = 'MAKE'
	AND CDESCRIPTION = '" . $_GET['make'] . "'
	AND v.CPRICE <> '0'
	AND v.CYEAR BETWEEN '" . $_GET['minyear'] . "' AND '" . $_GET['maxyear'] . "'
	AND v.CMODEL LIKE '%" . $_GET['model'] . "%'
	HAVING DISTANCE <= '". $_GET['radius'] ."'
	ORDER BY STATUS DESC, ". $_GET['sortby'] ." ". $_GET['order'] ."";
$result = mysql_query($query) or die(mysql_error());
while( $row=mysql_fetch_array($result, MYSQL_ASSOC) ) {
	echo '<pre>'; print_r($row); echo "</pre>\n";
}
die();
/** DEBUG - EXPLAIN - end */
right before that code snippet and post the result.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Code: Select all

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => cp
    [type] => ref
    [possible_keys] => CIDCARP,CCATEGORY
    [key] => CCATEGORY
    [key_len] => 12
    [ref] => const
    [rows] => 232
    [Extra] => Using where; Using temporary; Using filesort
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => v
    [type] => ref
    [possible_keys] => CYEAR,CPRICE,CIDLOTD,CMAKE
    [key] => CMAKE
    [key_len] => 3
    [ref] => bestcarp_usedcars.cp.CIDCARP
    [rows] => 5067
    [Extra] => Using where
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => p
    [type] => ref
    [possible_keys] => LOTID
    [key] => LOTID
    [key_len] => 5
    [ref] => bestcarp_usedcars.v.CIDLOTD
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => l
    [type] => ref
    [possible_keys] => CIDLOTD
    [key] => CIDLOTD
    [key_len] => 5
    [ref] => bestcarp_usedcars.v.CIDLOTD
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o1
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O1
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o2
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O2
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o3
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O3
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o4
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O4
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o5
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O5
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => o6
    [type] => ref
    [possible_keys] => CIDOPTI
    [key] => CIDOPTI
    [key_len] => 2
    [ref] => bestcarp_usedcars.v.O6
    [rows] => 1
    [Extra] => 
)

Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => z
    [type] => ref
    [possible_keys] => zip_code
    [key] => zip_code
    [key_len] => 5
    [ref] => bestcarp_usedcars.l.CZIP
    [rows] => 2
    [Extra] => 
)
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

That seems ok. SOrry, I'm clueless then.
WanamakerStudios
Forum Commoner
Posts: 65
Joined: Thu Nov 30, 2006 7:35 am

Post by WanamakerStudios »

Thats what I said too ... thanks for the shot at it though!
Post Reply