Extremely Slow Binary Search
Moderator: General Moderators
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
Extremely Slow Binary Search
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?
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
feyd | Please use
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]
[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]please putright before that code snippet and post the result.
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 */-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am
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] =>
)-
WanamakerStudios
- Forum Commoner
- Posts: 65
- Joined: Thu Nov 30, 2006 7:35 am