List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:August 30 2002 7:42pm
Subject:Re: query optimization
View as plain text  
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 <mysql-thread118440@stripped>
>To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
>Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>


Thread
query optimizationGabe30 Aug
Re: query optimizationGerald Clark30 Aug
Re: query optimizationGabe30 Aug