[Solved]Help with mysql index - Printable Version

[Solved]Help with mysql index - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: [Solved]Help with mysql index (/showthread.php?tid=17761)

[Solved]Help with mysql index - El Forum - 04-15-2009

[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;-)

[Solved]Help with mysql index - El Forum - 04-15-2009

[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.

[Solved]Help with mysql index - El Forum - 04-15-2009

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

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

[Solved]Help with mysql index - El Forum - 04-15-2009

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

[Solved]Help with mysql index - El Forum - 04-15-2009

[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*

[Solved]Help with mysql index - El Forum - 04-15-2009

[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.

[Solved]Help with mysql index - El Forum - 04-15-2009

[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!