Re: SQL question

[Stuart Grimshaw]

On Sun, May 09, 2004 at 03:43:10PM +0100, James Traynor wrote:
> Stuart Grimshaw wrote:
> > 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 :-)
> There was a primary key on header_id, thats all.
> >
> > What does the explain look like now you have added the index?
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+
>  table  type  possible_keys  key  key_len  ref
> rows  Extra
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+
>  message_header  index  SOURCE  PRIMARY  4  [NULL]
> 251908  where used
>
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+
>
>

Now it's seeing SOURCE as a possible key, but it's still using
"index" as it's
join type. I would suggest dropping the FULLTEXT key, and just creating it
with
a normal index instead. Fulltext searches are done using the Match keyword,
and
as such FULLTEXT indexes have no effect in a query such as this.

In a simple query like this you should be able to acheive "const"
join types,
which is the fastest available on non-system tables.

--

-S