Re: same query, different execution, different performance

[Marshall]

The 2 queries are quite different. You don't mention what $q starts out being, but let's say $q='abc'; Then you add a wild card to it, $q="abc%";

Prepare can be a very expensive critter and the DB might do a lot of thinking about what indicies, etc to use. In the first case, the DB knows that the string is going to start with "abc" at the time that the prepare method is called. That is a huge hint that it can take advantage of.

In the second case, the DB doesn't know that in key bit of info advance, so it is a lot more "stupid" about how it goes about the "execute" part. So the basic difference is that "more thinking" went into the first prepare vs the second prepare because the DB knew more about what you were going to do.

[punkish]

Thanks Marshall. Makes a lot of sense.

[Marshall]

Another idea occurred to me - don't know if it would work or not - but an experiment should be easy for you...

If you are always adding this % wildcard at the end, what happens if you put that wildcard into the version 2 prepare? i.e. LIKE ?% instead of LIKE ? In this case you would just put in the raw $q "abc" instead of "abc%". In my simple "untested code" brain, that might trigger the DB to use the index, knowing that it will start out the search with a constant term that you provide.

I personally do not know the answer. But on the surface it sounds plausible. Anyway I think it would be fun the hear the results of that test. This is likely to be DB specific, but never-the-less interesting. I think the chance of success is low, but this critter may be smarter than we think.

I am working on some complicated approximate matches in SQLite. This may not apply in your case, but consider that if case is not an issue: ABC% must all fall between x >="ABC" and x<"ABD" in a string comparison sense depending upon how the DB is indexed and other confounding factors...

[Anonymous Monk]

The 2 queries are quite different.

No, they're identical

[Marshall]

Gee, at the moment I don't know how to explain it better - sorry if my English is confusing. Look again and see what is known when the prepare method is called. Read tye's post and look at the link which gives exactly the parameter needed to cause more to happen at execute() time rather than using the pre-prepared not so good default strategy - when preparing the statement, the DB just does a lot better if it knows "like something starting with abc" rather than "like something". The default is to use the info and strategy decided upon when the prepare statement was executed, but that behavior can be over-ridden.

[Anonymous Monk]

Gee, at the moment I don't know how to explain it better - sorry if my English is confusing. ...

That is understandable. The sql is the query, sql, structured query language, its literally the same string :)