Dom
Member
Registered: 13th Sep 03
User status: Offline
|
How would you go about doing a multiple search on a number of tables and cols and get x, y, z data?
At the moment, i split the words and then do a query through each one, but it seems to be the slowest method going. How would i go about querying the SQL DB directly to do this? (as im guessing that would be the quickest method?). Or is there a better way?
Cheers for any help 
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
mysql(SELECT * FROM column1.table1, column2.table2, column3.table3
WHERE blah blah blah
do an inner join if you want also, make sure your tables have a correct index name that tallies in with your query
[Edited on 03-05-2007 by Steve]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
yea i can do single searches thats fine, but i want to search on a string - like: frank bob chocolate finger cat.
If you just query that, it matches just that, but im after a search that will go through each word and pull out matches - then display the best ones, relevance etc etc
like i said, at the moment i loop through each word, but there must be a quicker way and its important that its done as quickly as possible as it isn't a little site.
cheers anyways steve.
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
tried using LIKE? with an OR
[Edited on 03-05-2007 by Steve]
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
mysql(SELECT * FROM column1.table1, column2.table2, column3.table3
WHERE column2.word1 = "' . $word1 ."'
OR blah blah
[Edited on 03-05-2007 by Steve]
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
yep used them, alright for basic searches, but im having to search through a few 1000 products, so need something stupidly quick and able to pull out anything that matches. As well as having stats based on that, so they can be sorted - relevance, price etc
|
Steve
Premium Member
Registered: 30th Mar 02
Location: Worcestershire Drives: Defender
User status: Offline
|
i think then you need to look at your indexes on your tables to see if they are correct, properly indexed tables should do that kind of search ultra fast
|
Tim
Site Administrator
Registered: 21st Apr 00
User status: Offline
|
Just create a full-text index on the column(s), then query using MATCH (column) AGAINST (query) (does relevance like the CS search).
Just Google for fulltext mysql...
[Edited on 03-05-2007 by Tim]
|
Ian
Site Administrator
Registered: 28th Aug 99
Location: Liverpool
User status: Online
|
Would be helpful to know table structure and exactly what you want the results to look like, then build some queries.
When that is done perhaps some indexes to speed things up, in addition to the fulltext one if you're using that as Tim suggests.
There are limitations inbuilt in to fulltext the most problematic being the minimum word length but that can be altered. Its far superior to LIKE, especially as you don't need to split the words up so they're not treated as a phrase as you would have to with LIKE.
|
Dom
Member
Registered: 13th Sep 03
User status: Offline
|
cheers tim and ian, will give it a go
|
