Find exact matches first

Hi folks!

I am building an ajax like (jQuery + JSON) search function here and I have a few issues / questions.

I have a MySQL table with around 160'000 rows with a field named "CODE" with the following values for example:

- ACAO
- ACA
- AC
- AC
- ACAT
- BOOA
...

There is also a "NAME" field in which I want to perform the search, but I don't really expect people to input an exact match as the search string.

1. If my search string is AC, I want it to return all the "AC" rows first (exact match), then all others that contain AC.

2. I need to return the results in JSON format and I need to know on the return (on the javascript side) which results are "exact matches" to the search string.


I found 2 solutions:

Solution 1 works quite well but involves 2 queries to the db and returns the exact matches twice (once in each query...)


// Select exact matches first
$sql = "select * from nav where code='" . $search . "' or name='" . $search . "' order by code asc limit 0,300";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

// Add param to JSON output for exact match

$r['exact'] = "1";
$po[] = $r;
}

// Select like matches
$sql = "select * from nav where code like '%" . $search . "%' or name like '%" . $search . "%' order by code asc limit 0,300";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

$po[] = $r;
}

print json_encode($po);



Solution 2 achieves what I want with only one query but is painfully slow!


$sql = "SELECT * from nav WHERE code like '" . $search . "' or name like '" . $search . "' union all SELECT * from nav WHERE code LIKE '%" . $search . "%' or name LIKE '%" . $search . "%' and code not like '" . $search . "' and name not like '" . $search . "'";
$req = mysql_query($sql) or die('Erreur SQL !<br>' . $sql . '<br>' . mysql_error());

while ($r = mysql_fetch_assoc($req)) {

// If exact match, add param to JSON output for exact match

if ($r['code'] == strtoupper($search)) {

$r['exact'] = "1";
}

$po[] = $r;
}

print json_encode($po);


Hence my questions:

1) Why is number 2 so slow? (I mean it's reeeeally slow...)

2) Would you see any better way to modify the JSON return so that I know which results are "exact matches"?

3) Any other way to achieve what I want with less code and have something that works faster? It's an ajax search, you know...

Thanks in advance for any help on that!

A.

Number 2 is slow because you are using UNION ALL and because of the NOT LIKE.

How about this?

SELECT IF(name='$search' OR code='$search',1,0) AS exact, name, code
FROM nav
WHERE name LIKE '%$search%' OR code LIKE '%$search%'
ORDER BY exact DESC, code ASC
LIMIT 300

And now you already have the value of exact ready to use?

How about this doesn't only look perfect but even works perfectly?

You answered my 3 questions in no time.
Working and fast code, information, what else could I ask for?

Thank you!

:thumbsup: