Improve SQL for searching

[saturngod]

In this days, I am making dictionary site with PHP & Mysql.

I write SQL like this

select * from dblist
where `Word`='A'

Union

select * from dblist
where `Word`like 'A%'

Union

select * from dblist
where `Word`='%A%'

Union

select * from dblist
where `Word`='%A'

select * from dblist where `Word`='A' Union select * from dblist where `Word`like 'A%' Union select * from dblist where `Word`='%A%' Union select * from dblist where `Word`='%A'

I don't use


`Word`='A' OR `Word`like 'A%' OR `Word`like '%A%' OR `Word`like '%A'

because the result show it order by id.

I want to show

First Match with A
Second Start With A
Third A in the middle
Fourth End with A

When I use Or the result ordery is not like that.So, I am using Union. But I think,sql is long and it may be slow for searching. How should I change it ? Any advice for me ?

By the ways,dblist has over 5 thousand rows.

[Dave Lane]

I think your query could be shortened to this:


select * from dblist
where `Word`='A'

Union

select * from dblist
where `Word`like '%A%'
order by `Word`


'%A%' will also return matches for '%A' and 'A%' (as '%' matches to any character or no character), so ordering that will at least reduce the number of unions you have to use.

You also probably want to add the following condition to the second select:


and `Word` != 'A'


This should prevent an exact match from showing up in your list of similar results, which I think would be desirable if you're already displaying an exact match at the top of the list...

You could also limit the number of returned records to some reasonable number. If you're using the MyISAM engine, you may also want to look at the online documentation for MATCHING/AGAINST.

Hope that helps!

[NogDog]

Might be interesting to see if something like this is more efficient, as it would require only one scan of the DB table:

SELECT *, IF(`Word`='A', 1, IF(`Word` LIKE 'A%', 2, IF(`Word` LIKE '%A', 4, 3))) AS `sort`
FROM `dblist` WHERE `Word` LIKE '%A%' ORDER BY `sort`, `Word`

However, I must note that the question is raised in my mind as to where should the word APPALOOSA belong in this sort order, since it has an "A" at the start, the end, and in the middle? :)

[Dave Lane]

I like NogDog's solution. :) I'd say it is more efficient.

[saturngod]

Might be interesting to see if something like this is more efficient, as it would require only one scan of the DB table:

SELECT *, IF(`Word`='A', 1, IF(`Word` LIKE 'A%', 2, IF(`Word` LIKE '%A', 4, 3))) AS `sort`
FROM `dblist` WHERE `Word` LIKE '%A%' ORDER BY `sort`, `Word`

However, I must note that the question is raised in my mind as to where should the word APPALOOSA belong in this sort order, since it has an "A" at the start, the end, and in the middle? :)
wow... amazing... speed up 2x :)

Thank you so much

[saturngod]

I think your query could be shortened to this:


select * from dblist
where `Word`='A'

Union

select * from dblist
where `Word`like '%A%'
order by `Word`


'%A%' will also return matches for '%A' and 'A%' (as '%' matches to any character or no character), so ordering that will at least reduce the number of unions you have to use.

You also probably want to add the following condition to the second select:


and `Word` != 'A'


This should prevent an exact match from showing up in your list of similar results, which I think would be desirable if you're already displaying an exact match at the top of the list...

You could also limit the number of returned records to some reasonable number. If you're using the MyISAM engine, you may also want to look at the online documentation for MATCHING/AGAINST.

Hope that helps!
I don't like MATCHING/AGAINST method because it's design for millions of rows. It can't show 100% result. They says, we have 10 millions rows, but just 8 millions may be useful result some are not. So, they can search just 80%.

[saturngod]

Thank you all. I post about that in my blog http://en.saturngod.net/ornagai-speed-up