Re: same query, different execution, different performance

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.

Comment on Re: same query, different execution, different performance

Replies are listed 'Best First'.
Re^2: same query, different execution, different performance by punkish (Priest) on Feb 14, 2012 at 02:37 UTC
Thanks Marshall. Makes a lot of sense. when small people start casting long shadows, it is time to go to bed	[reply]
Re^3: same query, different execution, different performance by Marshall (Canon) on Feb 14, 2012 at 04:33 UTC
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...	[reply]
Re^2: same query, different execution, different performance by Anonymous Monk on Feb 14, 2012 at 01:40 UTC
The 2 queries are quite different. No, they're identical	[reply]
Re^3: same query, different execution, different performance by Marshall (Canon) on Feb 14, 2012 at 01:58 UTC
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.	[reply]
Re^4: same query, different execution, different performance by Anonymous Monk on Feb 14, 2012 at 10:20 UTC
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 :)	[reply]