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
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
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!