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'

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 ?

[saturngod]

My dblist table has over 30000 rows

[ShawnCplus]

Make sure you have indices on the column you're searching, that's probably the easiest way to speed things up but it's a size vs. speed trade-off.

[saturngod]

Thank ... I also indices on the column. Can you explain about size vs speed trade-off ? what a mean that ?

You mean more size (rows) , slow speed ?

[ShawnCplus]

When an index is created it takes up space on the filesystem. So as long as you're fine with taken up more space to get more speed indices are the way to go.