Re: SQL question

[Stuart Grimshaw]

On Sat, May 08, 2004 at 11:53:45PM +0100, James Traynor wrote:
> I am running some queries on the tables created by the xapdblogger
> (mysql) and have come across an oddity that there is probably a really
> simple explanation to.
>
>
> select header_id from messageheader where
source="mi4.weather.egll"
> order by header_id desc limit 1;
>
> takes 9 seconds

How much data is there in the table? I assume header_id & source
are both
indexed? 9 seconds is a hell of a long time for a query.

>
> select header_id from messageheader where source='mi4.weather.egll'
> order by header_id desc limit 1;
>
> takes .05 seconds
>
> what does the " do that the ' doesn't ??

The manual states that both forms are valid, with the rider that
ANSO_QUOTES
must be on for " to be valid.

"A string is a sequence of characters, surrounded by either single
quote (`'')
or double quote (`"') characters. Examples:

'a string'
"another string"

If the server SQL mode has ANSI_QUOTES enabled, string literals can be
quoted
only with single quotes. A string quoted with double quotes will be
interpreted
as an identifier."

However if ANSI_QUOTES is disabled, it would treat strings in " as an
identifier, and the results of the query would be different.

--

-S