Page 1 of 1
Where would you place indexes in this case?
Posted: Thu Jul 03, 2008 7:10 pm
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.
Re: Where would you place indexes in this case?
Posted: Thu Jul 03, 2008 8:54 pm
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.
Re: Where would you place indexes in this case?
Posted: Thu Jul 03, 2008 10:10 pm
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
Re: Where would you place indexes in this case?
Posted: Thu Jul 03, 2008 10:12 pm
by califdon
Thanks. Ain't progress wunnerful?
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 12:55 pm
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.
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 1:06 pm
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).
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 1:54 pm
by Ollie Saunders
Yeah this is true. Consider Xapian, Solr, Sphinx or Zend Framework (Lucene)
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 1:58 pm
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');
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 3:09 pm
by califdon
I'm not going to warn you again, Astions -- you read the MANUAL??!!!

Hey, those are really interesting details that I sure didn't know about how LIKE and MATCH work.
Re: Where would you place indexes in this case?
Posted: Tue Jul 08, 2008 3:20 pm
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.
Re: Where would you place indexes in this case?
Posted: Fri Jul 11, 2008 11:17 am
by jack_indigo
Wow. I'm learning stuff here that I didn't realize. I'm glad I kicked off this discussion.