Re: SQL question

[Stuart Grimshaw]

On Sun, May 09, 2004 at 01:33:40PM +0100, James Traynor wrote:
>
> They both come up with :
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+
>  table  type  possible_keys  key  key_len  ref
> rows  Extra
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+
>  message_header  index  [NULL]  PRIMARY  4  [NULL]
> 251004  where used
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+

Then it must be that mysql is doing something different with the quoted
version.
PHP for example treats " different to ', it will look through the
" string and
look for any variable names to substitute etc, eprhaps MySQL does the same?

>
> I then added a full text index to the source and both queries came
down
> to 0.05 seconds :-)
> I looked on the server variables and couldn't find anything that
> referred to ansi_quotes so not sure on that one. I'm happy now the
> queries are reliably quick. Is a full text index the best here?

Was "source" indexed at all before? If you look at the
"type" of join above it's
listed as "index" the 2nd worst one it could choose :-)

What does the explain look like now you have added the index?

Can you post the results of "show create table message_header"
too, it'll help
understand what the explain means...

--

-S