Portal Home > Knowledgebase > Articles Database > A Little MySQL Help Please..
A Little MySQL Help Please..
| Posted by Xeentech, 12-08-2007, 06:01 PM |
| I'm probably just missing something really basic here, been working on this too long today.. so please lend me your fresh eyes..
I have a table of files, the index is just an int. I also have a table of "tags". I use the below query to find files in a "tag based search."
I need a query to select files which match all the tags supplied. I tried replacing the IN with ALL and "= ALL" but it ends with a MySQL error.
Any help would be much appreciated.
|
| Posted by Xeentech, 12-08-2007, 06:22 PM |
| I've been able to get the right results using a Temp Table.
Does any one have any ting more elegant?
|
| Posted by jstanden, 12-08-2007, 07:15 PM |
| How compatible does this need to be?
MySQL 3.23, 4.1, 5.0+?
|
| Posted by Steve_Arm, 12-08-2007, 07:46 PM |
| Post the tables' structure. Does the field table have a tagID field or something or
a filed with comma separated tags?
|
| Posted by jmichalicek, 12-08-2007, 10:59 PM |
| Try this:
|
| Posted by Xeentech, 12-09-2007, 05:01 AM |
| Works, that's awesome. Thanks Justin.
|
| Posted by Xeentech, 12-09-2007, 05:05 AM |
| MySQL 5.0+ only solutions would be fine.
|
| Posted by holmesa, 12-10-2007, 10:58 AM |
| Did you resolve it?
1. Determine if IN is working slow by removing it.
2. Determine if indexes are used for join and order by operators.
|
| Posted by Xeentech, 12-10-2007, 07:31 PM |
| What do you mean if it was working slow, this was never about execution time.
The solution JMichalicek posted works. If you're interested it executed in under 0.250 seconds when selecting 1000+ rows of the ~15000 rows in the db based on requiring 5 tags to match against the input. This is fine for my usage.
Your second point, it didn't need to be in any order, and the JOINs are of course using indexs I setup.
|
Add to Favourites 
Print this Article
Also Read
Shared Hosting
Shared Hosting
