Re^6: Does Search::InvertedIndex module live up to billing?

Sure thing:

CREATE TABLE `theTable` ( `response_id` smallint(5) unsigned NOT NULL 
+default '0', `respondent_id` smallint(5) unsigned NOT NULL default '0
+', PRIMARY KEY (`response_id`,`respondent_id`) ) TYPE=MyISAM
[download]

I'm afraid I'm not familiar with this infamous query analyzer - so I'll read up on it on the web.

Comment on Re^6: Does Search::InvertedIndex module live up to billing? Download Code

Replies are listed 'Best First'.
Re^7: Does Search::InvertedIndex module live up to billing? by perrin (Chancellor) on Oct 21, 2004 at 21:26 UTC
You are joining on respondent_id and searching on response_id so you need indexes on both of those. KEY (response_id), KEY (respondent_id)	[reply]
Re^8: Does Search::InvertedIndex module live up to billing? by punch_card_don (Curate) on Oct 21, 2004 at 21:31 UTC
OK - I'll try the individual indexes instead of the compound primary key, and changing order of declaration...... Of course, it take about two hours to repopulate the database, it'll be tomorrow before I have results. Thanks for the help! It really is much appreciated.	[reply]
Re^9: Does Search::InvertedIndex module live up to billing? by perrin (Chancellor) on Oct 21, 2004 at 22:26 UTC
The indexes are in addition to the primary key, not instead of it. Loading goes fastest if you write it out to a tab-delimited file and use LOAD DATA INFILE, then enable the keys. (You can either do DISABLE KEYS, load, ENABLE KEYS, or you can just wait to add the indexes with ALTER TABLE commands after the data load.)	[reply]
Re^7: Does Search::InvertedIndex module live up to billing? by punch_card_don (Curate) on Oct 21, 2004 at 21:26 UTC
Well, I found and tried the EXPLAIN command on the two-column query. Here's what it gave me: +-------+--------+---------------+---------+---------+---------------- +----+------+--------------------------+ \| table \| type \| possible_keys \| key \| key_len \| ref + \| rows \| Extra \| +-------+--------+---------------+---------+---------+---------------- +----+------+--------------------------+ \| B \| ref \| PRIMARY \| PRIMARY \| 2 \| const + \| 3007 \| Using where; Using index \| \| A \| eq_ref \| PRIMARY \| PRIMARY \| 4 \| const,B.respond +ent \| 1 \| Using where; Using index \| +-------+--------+---------------+---------+---------+---------------- +----+------+--------------------------+ [download] Unfortunately I have no idea what this means. More reading... Edit by castaway - swapped pre tags for code tags	[reply] [d/l]