Hello. On Tue 2002-07-16 at 07:47:33 -0500, [EMAIL PROTECTED] wrote: > >> Of the data? Or of the queries? > > Not the data. Just your query, the output from explain and a create > > table statement. > > Query: > > SELECT DISTINCT cards_crypt.* > FROM user_haves, cards_crypt WHERE > cards_crypt.name LIKE "%harrod%" AND > user_haves.card_name = cards_crypt.name AND > user_haves.total_have > 0 AND > user_haves.user != "joe_bob_briggs" > > Explain: > > *************************** 1. row *************************** > table: user_haves > type: ALL > possible_keys: card_name,total_have > key: NULL > key_len: NULL > ref: NULL > rows: 79370 > Extra: where used; Using temporary > *************************** 2. row *************************** > table: cards_crypt > type: eq_ref > possible_keys: name > key: name > key_len: 255 > ref: user_haves.card_name > rows: 1 > Extra: where used > 2 rows in set (0.00 sec)
This tells, that for the first table no index can be used, but all rows have to be read in. And that for each row, one row of the second table is read, using the index "name", which has a length of 255 chars. Reading only one row is good, an index having 255 chars is bad. The first table has to be read completely, because the beginning "%" in "%harrod%" prevents the use of any kex (see http://www.mysql.com/doc/M/y/MySQL_indexes.html). I suggest to try the following, in the listed order, until you are satisfied with the speed: - Use the numeric primary key to join the tables, i.e. replace user_haves.card_name with a user_haves.card_num, which refers to cards_crypt.record_num and then change the where clause accordingly SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE cards_crypt.name LIKE "%harrod%" AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have > 0 AND user_haves.user != "joe_bob_briggs" The will have the additional effect of making user_have smaller and more normalized. - If possible and reasonable for your application, create an FULLTEXT index over the card names and use its boolean(!) search instead of LIKE. The SELECT would look like (from mind): SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt WHERE MATCH(cards_crypt.name) AGAINST ("+harrod" IN BOOLEAN MODE) AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have > 0 AND user_haves.user != "joe_bob_briggs" - Alternatively, if using FULLTEXT indexes is not feasable, split up cards_crypt.name into words (like "harrod"), create a seperate table with these words and a second table which assigns the words to card names, i.e. CREATE TABLE card_words ( record_num int unsigned NOT NULL auto_increment PRIMARY KEY, word varchar(255), UNIQUE word (word) ); CREATE TABLE words_to_cards ( word_num int unsigned NOT NULL, card_num int unsigned NOT NULL, PRIMARY KEY (word_num, card_num) ); Then the SELECT would look like SELECT DISTINCT cards_crypt.* FROM user_haves, cards_crypt, card_words, words_to_cards WHERE card_words.word = "harrod" AND words_to_cards.word_num = card_words.record_num AND cards_crypt.record_num = words_to_cards.card_num AND user_haves.card_num = cards_crypt.record_num AND user_haves.total_have > 0 AND user_haves.user != "joe_bob_briggs" Greetings, Benjamin. [...] > CREATE TABLE user_haves ( > card_name varchar(100) NOT NULL default '', > total_have int(11) unsigned NOT NULL default '0', > user varchar(50) NOT NULL default '', > available int(11) unsigned NOT NULL default '0', > record_num int(11) unsigned NOT NULL auto_increment, > PRIMARY KEY (record_num), > KEY user (user), > KEY card_name (card_name), > KEY total_have (total_have), > KEY available (available) > ) TYPE=ISAM PACK_KEYS=1; > > > CREATE TABLE cards_crypt ( > record_num int(11) unsigned NOT NULL auto_increment, > name varchar(255) NOT NULL default '', > clan varchar(100) NOT NULL default '', > capacity int(10) NOT NULL default '0', > disciplines varchar(100) default '', > superior varchar(100) default '', > inferior varchar(100) default '', > title varchar(100) default '', > bonus_penalty text, > release_set varchar(100) NOT NULL default '', > rarity varchar(50) default '', > artist varchar(100) default '', > have int(10) default '0', > need int(10) default '0', > notes text, > alternate_name varchar(255) default '', > card_monger_url varchar(255) default '', > PRIMARY KEY (record_num), > UNIQUE KEY name (name), > KEY clan (clan), > KEY capacity (capacity), > KEY release_set (release_set) > ) TYPE=ISAM PACK_KEYS=1; > -- [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
