I'm sorry, I guess I should make sure the query syntax is correct when asking a question... Here it is with the correct field names:
SELECT main.* FROM kw_table, kw_table AS kw_table1, main WHERE kw_table.keyword LIKE 'first word%' AND kw_table1.keyword LIKE '2nd word%' AND main.id = kw_table.id AND kw_table1.id = kw_table.id GROUP BY main.id Limit 0,11 Yes, there can be duplicate id's in the keyword table (but not the "main" table).. For instance: kw_table -------- id keyword __ _______ 1 Rich 1 Richard 1 Rick 1 Bob 2 Leonard 3 Bob 3 Larry 3 Mike Thanks Gabe Gerald Clark wrote: > That is not valid SQL. > a table can't be like something. > I think you ommitted the field name. > I also don't see why you need a join , unless you have duplicate ids. > > Gabe wrote: > > >First off, please excuse me if this is the wrong list to post this sort of question >to (and please direct me to the > >appropriate list) > > > >My problem: > > > >I have a database of approx. 170,000 records and 1.2 million keywords. Most of my >searches happen fairly quickly using > >the format below > > > >However, the "group by" is causing a temporary table when I "explain" my statement, >which significantly lengthens query > >time. > > > >If I eliminate the group by, queries are substantially sped up and a temp table is >not being generated. Only problem > >with not using "group by" is that sometimes the same ID is duplicated in the >results due to some of the keywords being > >very similar (i.e. "Richard", "Rich", "Rick") for the same ID. > > > >Can anyone help with a different Select strategy or something else to eliminate the >temp table being generated for this > >query. > > > >--------------------------------------------- > >Tables: > >main - has unique ID (primary) column and main data columns > > > >kw_table - has ID column and a keyword column > > > >Indexes are created on the ID columns and the keyword column. > > > >Query: > > > >SELECT main.* > >FROM kw_table, kw_table AS kw_table1, main > >WHERE kw_table LIKE 'first word%' AND kw_table1 LIKE '2nd word%' AND main.id = >kw_table.id AND kw_table1.id = > >kw_table.id > >GROUP BY main.id > >Limit 0,11 > >-------------------------------------------- > > > >Thanks > >Gabe > > > > > > > >--------------------------------------------------------------------- > >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 > > > > > > --------------------------------------------------------------------- > 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 --------------------------------------------------------------------- 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
