mysql • View topic • searching across multiple fields

searching across multiple fields

3 posts • Page 1 of 1

Jump to:

Next

1. select .. where ..in TOO LONG
I need to perform a select based on a list of id's , it's fine when the number of id's is not too big, but when It can be very large.. what options could I have ? create a temporary table and match with this table ... each time I need to perform the select... ? no better option in term of performances ? thanks for yoru lights joss
2. Java connection to MySQL
After I have installed a program and wanted to start it, I got the error message "Can't create Java connection to MySQL". Any thoughts on what could cause this problem?
3. How to order SQL statements
Need help with the SQL statements. I am looking for a reference to the order of the SQL statements? e.g: SELECT followed by GROUP BY, followed by ORDER BY. TIA, Roy
4. Optimize this!
Please help me optimize this: I have a table with columns: headlineid, keyword. headlineid+keyword combination is unique. Relationship between headline and keyword is many-to-many. i.e., headlines can have many keywords. keywords can be associated with many headlines. Keywords in the same headline are considered "related". Here's the query to find out which keywords 'hello' is related to and sort desc on number of occurrences of the related keywords: select keyword, count(keyword) as keywordcount from tags where headlineid in (select distinct(headlineid) from tags where keyword = 'hello') AND keyword <> 'hello' group by keyword order by keywordcount desc limit 0, 5; This query takes a lot of time. Need help with optimization. All help is appreciated.

searching across multiple fields

by m.k.ball » Sun, 01 Oct 2006 08:41:57 GMT

I have a query designed to search multiple fields for any word that
starts with the characters entered in a search. Each field will
typically contain more than one word. The query takes the form:

SELECT
field1, field2
FROM table1 WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
field2 LIKE "a%") OR (field2 LIKE "% a%"))

Is there a more efficient way to search than this?

Michael

Re: searching across multiple fields

by peterloh » Thu, 05 Oct 2006 13:01:14 GMT

You can use regular expressions which would cut down your WHERE
conditions...

SELECT field1, field2
FROM table1
WHERE field1 REGEXP '(^|[[:space:]])a[[:alnum:]]+'
OR field2 REGEXP '(^|[[:space:]])a[[:alnum:]]+';

Re: searching across multiple fields

by Bill Karwin » Fri, 06 Oct 2006 01:29:51 GMT


 >> WHERE ((field1 LIKE "a%") OR (field1 LIKE "% a%") OR (
 >> field2 LIKE "a%") OR (field2 LIKE "% a%"))

Here's another regular expression alternative that matches the same cases:

   WHERE field1 REGEXP '[[:<:]]a' OR field2 REGEXP '[[:<:]]a'

Regards,
Bill K.

Similar Threads:

1.Search Multiple keywords across multiple fields

Hi,

I'm about halfway through building a search engine using ASP, SQL and
Access.
As part of that search engine I need to search multiple keywords across
multiple fields
So far I've figured out how to split a string into words. From here I could:

Construct an SQL statement of varying length that, using the LIKE and AND
operators. This seems damn hard!

Alternately I could concatenate the multiple fields into a variable or array
then search.

What is best - can anyone provide examples ?

Darren

2.Best way to search across multiple tables

I have a horizontally-partitioned database which contains multiple tables of
the same schema which contain messages. Each table has a FULLTEXT index on
(subject, body). Currently, the search script only allows searching one
table at a time, but I'd like to allow searching of all tables at once, or
at least more than one at once. Is there a good way to allow for this? Would
it involve using a view and/or union?

3.search across multiple records?

Hello,

I have an HTML file with <form etc in which the names of composers are
listed. Say Beethoven, Mozart,  etc.

In the MySQL database I have records

composer1     Beethoven
composer1work1            Symphony No.4

composer2     Mozart
composer2work1            Piano Sonata No.2

etc etc.

How can I form the SQL statement so that the search will find
composers and their works in different records?

Any pointers please?!

Cheers,

Geoff

4.Multiple fields with multiple search items

Hello everyone:  I am having a bit of a problem trying to wrap my head
around a particular select statement... I have a table that has
numerous stores.  Beyond the normal address, city, state, zip fields,
each store record has 5 fields, 1 for each for ice cream flavor
carried at that particular store.  I have a search box that lets you
search for ice cream flavors.  Right now searching for one flavor is
hard enough since my select statement select all records "where
flavor1 = "x" or flavor2 = 'x' or...", but I would like the ability to
search for multiple flavors, and have the returned results start with
those stores that match the most flavors searched upon.  Does anyone
have any way of doing this?  Also, the page is written in PHP so if
you have any php related way to handle this issue, I am more than
happy to try them as well.  Thank you!!!

5.Multiple Inserts across multiple tables

On 3 June, 15:34, David < XXXX@XXXXX.COM > wrote:
> Hi Guys,
>
> After a bit of advice really.
>
> We have a number of tables in our database, that when a new account is
> created it should insert into all of them (different information) get
> the insert ids from all and the update the first table with all the
> ids (probably not the best way to do it, so if anyone can point me in
> the right direction, feel free).

I would insert into 1 table and then use the ID from that one as the
primary key to use on inserting to all the rest.

And yes, if you want Commit/Syncpoint/Rollback functionality you need
to use InnoDB.

6. Full-text search across multiple tables

7. Full Text Search across multiple tabels in SQL 2005/2008

8. Full Text Search across multiple tables (Parent-Child)

3 posts • Page 1 of 1

Return to mysql

Who is online

Users browsing this forum: No registered users and 44 guest