Where would you place indexes in this case?
Moderator: General Moderators
-
jack_indigo
- Forum Contributor
- Posts: 186
- Joined: Sun Jun 08, 2008 11:25 pm
Where would you place indexes in this case?
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.
(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?
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.
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?
0 to 65,535 in 5.0.3 and later versionscalifdon wrote:On the VARCHAR fields, the manual says the length is limited to 255 characters.
http://dev.mysql.com/doc/refman/5.0/en/char.html
Re: Where would you place indexes in this case?
Thanks. Ain't progress wunnerful?jack_indigo wrote:0 to 65,535 in 5.0.3 and later versionscalifdon wrote:On the VARCHAR fields, the manual says the length is limited to 255 characters.
http://dev.mysql.com/doc/refman/5.0/en/char.html
- 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?
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......to...
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.
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%
Code: Select all
WHERE CONCAT(company, keywords, description, addr1, addr2, town, region, postcode) LIKE '%keywords%';Re: Where would you place indexes in this case?
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).
- 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?
Yeah this is true. Consider Xapian, Solr, Sphinx or Zend Framework (Lucene)
Re: Where would you place indexes in this case?
Looking through the manual yielded some interesting results. Notably:
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.
So it appears that adding indexes won't even help you with your current query.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.
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?
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?
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?
Wow. I'm learning stuff here that I didn't realize. I'm glad I kicked off this discussion.