Member Avatar for 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 ?

  • 2 Contributors
  • 4 Replies
  • 103 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by ShawnCplus

Dani AI

Generated

A single SELECT that scores each row and then orders by that score is simpler and usually faster than several UNIONs. The idea is: limit candidates to rows that contain the term, compute a small integer "rank" (exact, prefix, contains, suffix), then ORDER BY that rank and any tiebreaker (id or alphabetical). Example using a parameter :term:

SELECT *,
  CASE
    WHEN Word = :term THEN 1
    WHEN Word LIKE CONCAT(:term, '%') THEN 2
    WHEN Word LIKE CONCAT('%', :term, '%') THEN 3
    WHEN Word LIKE CONCAT('%', :term) THEN 4
    ELSE 5
  END AS match_rank
FROM dblist
WHERE Word LIKE CONCAT('%', :term, '%')
ORDER BY match_rank, id;

Performance notes and options:

  • For ~30k rows this approach is perfectly fine. If the table grows, avoid leading wildcards (%term%) because they force full scans. If you must support arbitrary substring searches at scale, use a full-text index or an external search engine (Sphinx/Elasticsearch). If you continue using UNION, prefer UNION ALL to avoid the duplicate-elimination overhead.
  • Full-text search (MATCH ... AGAINST) is much faster for natural-language-style searches, but it has limits (minimum word length, stopwords). If single-character or very short-term searches are required, you may need to adjust ft_min_word_len, use the n-gram parser, or choose an external engine. See the MySQL docs for details: Full-Text Search and ft_min_word_len system variable.
  • Keep WHERE clauses sargable: avoid wrapping the indexed column in functions. If case-insensitive matching is needed, use an appropriate collation on the column rather than functions in the query.

Tie-back: was right to highlight indexes; they help when patterns allow index use. The CASE method gives deterministic ordering without multiple UNIONs and is easy to maintain.

Recommended Answers

All 4 Replies

Member Avatar for saturngod

My dblist table has over 30000 rows

Member Avatar for 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.

Member Avatar for 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 ?

Member Avatar for 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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.