On Thu, 23 Dec 2004, Bryan Heitman wrote:
> My mistake! Here you go:
Ok, no prob :)
>
> CREATE TABLE `matrix` (
> `WordID` int(11) unsigned NOT NULL default '0',
> `LocationID` int(11) unsigned NOT NULL default '0',
> `Position` enum('Body','From','Subject','To','Mailbox','File') NOT NULL
> default 'Body',
> `times` int(11) unsigned NOT NULL default '0',
> `MyOrder` int(11) unsigned NOT NULL default '0',
> `AccountID` int(11) unsigned NOT NULL default '0',
> `date` timestamp(19) NOT NULL,
> KEY `myKey` (`AccountID`,`WordID`,`Position`,`date`),
> KEY `myKey2` (`LocationID`)
> ) TYPE=MyISAM MAX_ROWS=1000000000 AVG_ROW_LENGTH=300 DATA
> DIRECTORY='/home/imap/fuse3.disk2/SQL/search/'
>
Oops, I forgot to ask you to send the output of "show index from matrix".
But your index "myKey" looks goods, you could try changing the order of
the fields in your key. Try creating a index with your fields ordered by
the "Cardinality" value from the "show index from matrix" output
(in asceding order).
Also, what happens if you don't specify the "date" value in your query?
Check the time it takes and the explain output.
Another thing I would suggest is to create (or replace) your index,
trying all (or almost all) of the possible combinations regarding the
order of the keys in your index. It helped me in some situations, and
sometimes it's better for me to keep two indices with the same keys but
different order, because of my different selects.
Hope that helps!
[]s,
Sergio
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
