Difference between performance of two sql queries?

Question

I have a field in my table that has a text data type.

Is there any performance difference for the following two sql queries:

 select * from tablename where fieldname="xyz%";
 select * from tablename where fieldname="%zyx";

If we execute these queries, this is what I think we will need to do:

We must match two regular expressions (xyz * and * zyx).

We will need to check string characters starting from the beginning.

For the first query, we will need to read the first three characters to see if there is a match, but for the second we will need to read until we get the end of the line to determine if a match has occurred. But if we have the length of the string stored somewhere, we can directly read the last three characters, giving similar performance, as in the first case.

My question is whether commercial databases like mysql and oracle have any performance difference when querying.

Answer 1

I select from your comment: "I just want to know if the beginning with a match is different from the ends with a match."

First, remember that we are not looking for the best algorithm to match the string. We are looking for the best algorithm to search for all matching rows in a set of N rows. We want to do better than "Do the algorithm X, N times."

, - SQL 3 3 , .

IS , , N .

. "xyz%" .

, , , "peter". "peter" - "samantha" .., , "xyz".

"% xyz" , , , .

.

/ . ( , , ..).

- B-Tree B * Tree.

Answer 2

. , .

, "" (, ).

, ,

• , .
• , :) ( )

:

 select * from tablename where fieldname_rev="xyz%";

, .

Answer 3

fieldname,

select * from tablename where fieldname>="xyz" and fieldname<"xy{"

.

Answer 4

, :

select * from tablename where fieldname LIKE "xyz%";
select * from tablename where fieldname LIKE "%zyx";

• equals ( "=" ) SQL - LIKE
• • "xyz%" , "xyz"
  • "% xyz" , "xyz"
• , fieldname, "% xyz" , "xyz%" , , .

The fastest way to find substrings in the text is to use full-text search (FTS) - both Oracle and MySQL have their own functionality, and there are third-party tools like Sphinx and Solr.