PHP + MySQL = Cache Problem

gotornot

Hi All

I have a site that is taking a bunch of feeds from other sites, normalizing the data into a product file. I also have a "Google" like search engine that a user can use to searh this data and get desired results.

I have nearly 1m products and it takes 1 user only approximately 10 seconds to search the data even though its thoroughly indexed. I heard putting the data in cache would be far quicker.

Is this true?

The size of the data is just under 600mb so i have plenty of RAM.

If this is true can someone please point me in the right direction to upload it in the cache and search on it.

Many thanks

NogDog

I'd try some explaining first before assuming it's the database's fault. (We have one table with almost 50,000,000 rows that we query regularly. Part of the optimization was to create an index across 5 of the columns that are used in the where clauses of the most common queries that access it [as opposed to 5 separate 1-column indexes].)

gotornot

We are only querying one field "Name" which is a varchar of 255 chars alphanumeric. We select data from the other fields but only searching the one.

NogDog

Are you doing a [FONT=Courier New]LIKE '%search text%'[/FONT] ? If so, that probably renders the index on that field useless, and so you have to do full table scans each time. If feasible, you might look into changing that to be a FULLTEXT index, and make use of the related FULLTEXT functions to do your searches.

gotornot

do you have a link where i can read up on fulltext and searches?

gotornot

the other problem is that a user will only search for "Nike Trainers" and that will only be part of the name: "Nike Cross Trainers made In the USA Size 6-14"

Would the FULLTEXT work in this scenario?

NogDog

Yes, that's the sort of thing that fulltext search will be good for.

http://dev.mysql.com/doc/refman/5.0/en/fulltext-search.html

(note that with the latest version of MySQL -- assuming that's what you are using? -- you can now use fulltext indexes on INNODB tables, not just MyISAM, in case that matters for you.)