Re: SQL question

[James Traynor]

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


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


> CREATE TABLE `message_header` (
> `header_id` int(10) unsigned NOT NULL default '0',
> `date_entered` datetime NOT NULL default '0000-00-00 00:00:00',
> `xap_version` int(11) NOT NULL default '0',
> `hop_count` int(10) unsigned NOT NULL default '0',
> `unique_identifier` varchar(8) NOT NULL default '',
> `class` varchar(255) NOT NULL default '',
> `source` varchar(255) NOT NULL default '',
> `target` varchar(255) default NULL,
> PRIMARY KEY (`header_id`),
> FULLTEXT KEY `SOURCE` (`source`)
> ) TYPE=MyISAM

Server is RH9 btw, client is php in apache


James