[Solved]Help with mysql index

[eluser]rogierb[/eluser]
Hi guys n gals,

I've got a query like

Code:

SELECT * FROM user where (lastname like 'myself%' OR firstname like 'myself%')
ORDER BY lastname,  firstname

to search for either lastname or firstname.

Somehow I don't seem to be able to get a working index for this query. I've played around with fulltext and a sh*tload of other possibilities but i can only get it working with just one 'like'. As soon as I add the second one, *poof* the index doesn't work anymore.

Anyone out there that can kick me in the right direction?

Thanx;-)

[eluser]hudar[/eluser]
I'm not really sure what were you mean with working index, but when I simply tried the query, I seems to work, and returning appropriate result.

[eluser]rogierb[/eluser]
Getting results is not the problem, getting them fast is.

Look at http://dev.mysql.com/doc/refman/5.0/en/m...dexes.html for more info

[eluser]TheFuzzy0ne[/eluser]
Please could you post your table schema dump.

[eluser]rogierb[/eluser]
Hi Fuzzy,

Due to security restrictions I can only show part of the table.

Code:

`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`company_id` int(10) unsigned NOT NULL,
`lastname` varchar(100) NOT NULL,
`firstname` varchar(50) NOT NULL,

PRIMARY KEY (`id`,`company_id`,`active`),
KEY `company_id` (`company_id`,`active`),
KEY `search` (`company_id`,`lastname`(10),`firstname`(10)),
KEY `search 3` (`lastname`(10),`firstname` (10)),

ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT

I tried al sorts of combinations, normal, fulltext etc.

*sigh*

[eluser]TheFuzzy0ne[/eluser]
With the limited data you supplied, I've come up with the following. I don't understand how aliases for keys work, so I've left them out, Also, I've not put the index across multiple columns, but rather individual columns. I suspect that this should work as you expect it to, although I have no idea what the problem is in your case. I think it might just be the index aliases, perhaps removing them will fix the problem?

Code:

CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `company_id` int(10) unsigned NOT NULL,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  PRIMARY KEY  (`id`),
  KEY `first_name` (`first_name`),
  KEY `last_name` (`last_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT;

Full text indexes on VARCHAR fields probably won't work, or if they did, they'd probably be overkill.

[eluser]rogierb[/eluser]
@fuzzy, dude u rock!

I never thought of using an index on a single field because I dindnt know Mysql could use multiple indexes one one query.

Thanx a million!