Where would you place indexes in this case?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jack_indigo
Forum Contributor
Posts: 186
Joined: Sun Jun 08, 2008 11:25 pm

Where would you place indexes in this case?

Post by jack_indigo »

I built a website for a client and it was running along zippy fast on keyword searches. But over time, it is now adding about 3 seconds on to every query.

(a) Can you tell me based on the information below where I should place my indexes? I mean, I fear adding too many indexes. The problem, once you see the WHERE clause, is that it appears to me I might need an index on every column practically in the table!

(b) Can you tell me if there's any bugs with adding indexes where I might lose data if I don't backup the database first?

Basically my search field WHERE clause looks like:

WHERE
company LIKE '%$keywords%'
OR keywords LIKE '%$keywords%'
OR description LIKE '%$keywords%'
OR addr1 LIKE '%$keywords%'
OR addr2 LIKE '%$keywords%'
OR town LIKE '%$keywords%'
OR region LIKE '%$keywords%'
OR postcode LIKE '%$keywords%'

Each of those fields is about a string that's anywhere from 50 to 100 characters long on average, with description being 500 characters long. None are TEXT fields -- they're all VARCHARs.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Where would you place indexes in this case?

Post by califdon »

I don't think there's any danger in creating indexes, and it would no doubt improve search performance, but what it will do is potentially slow down INSERTs, because it has to write to all the indexes, too.

Do you really need to search on such long strings? Of course, it depends on the nature of your data, but in many applications you could truncate the search terms at, say 10 bytes and probably get exactly the same results. I believe you can create an index on a substring of a field, but I wouldn't bet money on it.

On the VARCHAR fields, the manual says the length is limited to 255 characters.
jack_indigo
Forum Contributor
Posts: 186
Joined: Sun Jun 08, 2008 11:25 pm

Re: Where would you place indexes in this case?

Post by jack_indigo »

califdon wrote:On the VARCHAR fields, the manual says the length is limited to 255 characters.
0 to 65,535 in 5.0.3 and later versions

http://dev.mysql.com/doc/refman/5.0/en/char.html
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Where would you place indexes in this case?

Post by califdon »

jack_indigo wrote:
califdon wrote:On the VARCHAR fields, the manual says the length is limited to 255 characters.
0 to 65,535 in 5.0.3 and later versions

http://dev.mysql.com/doc/refman/5.0/en/char.html
Thanks. Ain't progress wunnerful?
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Where would you place indexes in this case?

Post by Ollie Saunders »

califdon can correct me if I'm way off here, because this might be a bad idea for performance, but you could shorten your query from...

Code: Select all

WHERE
company LIKE '%$keywords%'
OR keywords LIKE '%$keywords%'
OR description LIKE '%$keywords%'
OR addr1 LIKE '%$keywords%'
OR addr2 LIKE '%$keywords%'
OR town LIKE '%$keywords%'
OR region LIKE '%$keywords%'
OR postcode LIKE '%$keywords%
 
...to...

Code: Select all

WHERE CONCAT(company, keywords, description, addr1, addr2, town, region, postcode) LIKE '%keywords%';
As for indexes, I would say index them all, if they are being used as search terms. You can always remove indexes later if inserts are slow. Do a backup first though, that's never a bad idea.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: Where would you place indexes in this case?

Post by Eran »

LIKE is very bad for performance regardless. You might be better off switching to an indexing engine such as lucene (which has several implementations in PHP).
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Re: Where would you place indexes in this case?

Post by Ollie Saunders »

Yeah this is true. Consider Xapian, Solr, Sphinx or Zend Framework (Lucene)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Where would you place indexes in this case?

Post by Benjamin »

Looking through the manual yielded some interesting results. Notably:
LIKE won't use indexes if it begins with a wildcard character.

The MATCH() column list must match exactly the column list in some FULLTEXT index definition for the table, unless this MATCH() is IN BOOLEAN MODE. Boolean-mode searches can be done on non-indexed columns, although they are likely to be slow.

The argument to AGAINST() must be a constant string.
So it appears that adding indexes won't even help you with your current query.

I would index all the fields together as a SINGLE index, and then use the MATCH AGAINST syntax.

Also, based on the statement regarding AGAINST containing a constant string, you may not even be able to effectively use wildcards.

Code: Select all

 
 CREATE TABLE `test`.`full_text` (
`field1` VARCHAR( 255 ) NOT NULL ,
`field2` VARCHAR( 255 ) NOT NULL ,
`field3` VARCHAR( 255 ) NOT NULL ,
`field4` VARCHAR( 255 ) NOT NULL ,
`field5` VARCHAR( 255 ) NOT NULL
) ENGINE = MYISAM 
 
 ALTER TABLE `full_text` ADD FULLTEXT (
field1,field2,field3,field4,field5
) 
 
SELECT * FROM full_text WHERE MATCH(field1, field2, field3, field4, field5) AGAINST('string');
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: Where would you place indexes in this case?

Post by califdon »

I'm not going to warn you again, Astions -- you read the MANUAL??!!! :twisted: Hey, those are really interesting details that I sure didn't know about how LIKE and MATCH work.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: Where would you place indexes in this case?

Post by Benjamin »

Yeah I figure the developers who write an application tend to know more about it than their users. It doesn't seem like MySQL is the best solution for hi-performance full text searching unless your only searching for words.
jack_indigo
Forum Contributor
Posts: 186
Joined: Sun Jun 08, 2008 11:25 pm

Re: Where would you place indexes in this case?

Post by jack_indigo »

Wow. I'm learning stuff here that I didn't realize. I'm glad I kicked off this discussion.
Post Reply