Page 1 of 2

Extremely Slow Binary Search

Posted: Mon Jan 15, 2007 2:33 pm
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?

Posted: Mon Jan 15, 2007 3:44 pm
by Christopher
Is the column indexed? Have you rebuilt the index?

Posted: Mon Jan 15, 2007 3:47 pm
by timvw
Did you also create an INDEX on the (binary) column?

Posted: Mon Jan 15, 2007 4:03 pm
by WanamakerStudios
Yes. I have index the field and optimized the table ...

Posted: Mon Jan 15, 2007 5:45 pm
by feyd
What types are these fields being stored as?

Posted: Mon Jan 15, 2007 5:47 pm
by WanamakerStudios
CHAR and VARCHAR

Posted: Sat Jan 20, 2007 8:01 am
by WanamakerStudios
Any one with any ideas? I've tried a thousand different things ... and no luck!

Posted: Sat Jan 20, 2007 5:24 pm
by volka
What query do you use?

Posted: Sat Jan 20, 2007 5:25 pm
by WanamakerStudios
Im not sure I know what you mean ...

Posted: Sat Jan 20, 2007 5:26 pm
by volka
Please show me your sql statement.

Posted: Sat Jan 20, 2007 5:28 pm
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]

Posted: Sat Jan 20, 2007 5:35 pm
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.

Posted: Sat Jan 20, 2007 5:38 pm
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] => 
)

Posted: Sat Jan 20, 2007 6:00 pm
by volka
That seems ok. SOrry, I'm clueless then.

Posted: Sat Jan 20, 2007 6:01 pm
by WanamakerStudios
Thats what I said too ... thanks for the shot at it though!