Large Search Index

[dr.p]

I need help, all you gurus. I'll try to give you as clear a picture as I can, of what I have.

Very large images table with several fields that contain keywords/phrases that are searchable (currently contains ~320K records). Each image has image_id auto_incremented field.

The words from the required fields in each image record are pulled and stored in a word list table (contains ~330K records), where each record is (word_id, word_text).

And a match table is created, where word_id and image_id are stored, and the field(s) (from the images table) contain a 0 or 1 depending on their match status. So, if image_field1 in image_table matches word_id, then match_table's image_field1=1. The match table contains ~6 million records.

The search script (in PHP) takes 5+ seconds to do a query like:

SELECT image_id FROM match_table WHERE word_id=###

I've racked my brains trying to get this one query sped up. match_table has a key for (word_id, image_id).

Any ideas at all as to how to speed up a query like this?

hi dr.p,

Is there any way your search results can be cached in the database to speed up popular searches?

You may wish to see if you can integrate your php search program with the phpCache program at:

http://0x00.org/php/phpCache/

I hope this helps.

[imported__sam_]

Hello Dr.P.

Are you using MySQL? If so, try examining the query via "EXPLAIN", to make sure the index is used. In your case, there should be even no access to the actual table, because image_id is also in the index. If that's the case, thre is nothing you can do to make it faster from an index design point of view. You have to dig into the MySQL configuration parameters, which perhaps are not optimized for large tables.

I wonder, however, why you store word/image combinations that do not match (match status 0). A straightforward implemetation would be to store only the matching pairs, which would make your table smaller and faster.

best regards

Sam

[dr.p]

Thanks for your replies. I've tried the explain command before, and it didn't explain much... yeah I'm using MySQL, btw. I will dig into the setup, though, as much as I can.

As for caching, it was caching before, but that wasn't helping the speed at all... tho, I'm sure caching in the right way will help.

Thanks again.

[dr.p]

I had a combined index/key image_word_id (word_id, image_id) on the table with 5million records. Removed that and added individual keys word_id and image_id, and the speed issue is gone! Is there a heads up in the MySQL docs that I missed about this?

Thanks again for your help.

[imported__sam_]

Originally Posted by dr.p

I had a combined index/key image_word_id (word_id, image_id) on the table with 5million records. Removed that and added individual keys word_id and image_id, and the speed issue is gone! Is there a heads up in the MySQL docs that I missed about this?

Nothing I'm aware of. On the contary, in the section "How MySQL Uses Indexes" it says:

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows.

which means, your original index should work fine, and I personally never had a problem with such indexes not being used.

At least, you got it working, best regards

Sam