Re: SQL question

[James Traynor]

Stuart Grimshaw wrote:
> On Sun, May 09, 2004 at 09:27:51AM +0000, Stuart Grimshaw wrote:
>
>>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.
>>
>
>
> If you prepend "explain" to the query, it will tell you what
it's doing and
what indexes it's using etc.
>
> Prepend explain to both those queries & mail it to me, or the
list if you
prefer.
>
>

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
+----------------+-------+---------------+---------+---------+--------+--------+\
------------+

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?

Thanks for the pointers

James