Search numbers

[dreamwest]

What is the fastest way to search a list of numbers without using LIKE

 

Search = 4

Row eg,    2,3,4,6

 

Ive tried using REGEXP but not getting anywhere

[trq]

LIKE is probably your best option. You shouldn't be storing your data as a comma delimtered string if you wanted efficient.

[dreamwest]

my problem is ive got a list of rows that associate a video id

 

select * from video where word_id IN(1,3)

 

Which returns

 

word_id      vid_id

 

1 864144

3 864144

3 864145

1 864146

 

Basically from this set i only want to return rows that have both numbers in this case vid_id 864144, im just not sure how to go about it

[Mr Hyde]

modify your REGEX statement to look like this:

"WHERE $column REGEXP '[[:<:]]" . $number . "[[:>:]]' = 1"

 

Using "[[:<:]]" makes the engine look for non alphanumeric characters as word boundaries. It's pretty darn fast, and it should tear into those comma delimited columns without a problem... especially if they are indexed.

[dreamwest]

Cool!  Its even a bit quicker than boolean

 

Thanks

[Mr Hyde]

Cool!  Its even a bit quicker than boolean

 

Thanks

 

Yeah, if you can use them, regular expressions are the way to go. I've given up on fulltext and %like% searches for the bulk of my searching for a while now.

[dreamwest]

One more quick question: Is there an alternative for IN() using REGEX

 

Currently i have:

select * from video WHERE id IN(2,7,18,19)

[Mr Hyde]

One more quick question: Is there an alternative for IN() using REGEX

 

Currently i have:

select * from video WHERE id IN(2,7,18,19)

 

Not to the best of my knowledge, but I do have a php query builder class that parses search strings like:

this_is_treated_as_an_OR_string "this is a quote" +this_is_something_that_has_to_be_in_the_results -i_dont_want_this wildca%rd -"I don't want this quote in my results"

 

and automatically converts them into a regex string. It's a module in what will be an open source searching class I'm building that I can send you. That would obviously have way more functionality than you require, but it's a nice little bit of code.

 

I just whipped up a really basic interface to it here:

http://fiftyoff.phpwebhosting.com/business/sql_search_query_builder.php?test

I can't guarantee that I'll have that up permanently.

 

I'd be happy to send you the code if you like. Otherwise, the style query that you are looking for is like this:

SELECT * FROM `table` WHERE `column` REGEXP '[[:<:]]or1[[:>:]]' = 1 OR `column` REGEXP '[[:<:]]or2[[:>:]]' = 1

[dreamwest]

OK, because REGEX is doing the actual search i dont need to replace IN() because i can limit the amount to 30 items at a time. Its pretty fast on 50 Million rows it returning a set in 0.0006 secs

 

This is equivilant to "some words" in boolean

select id from video WHERE word_id REGEXP '[[:<:]]567,765[[:>:]]' = 1 limit 0, 30

 

This is equivilant to find rows that contain at least one of the two words in boolean

select id from video WHERE word_id REGEXP '[[:<:]](567|765)[[:>:]]' = 1 limit 0, 30

 

The final one i need is '+some +words' boolean equivilant aka match both numbers within the string, any suggestions?

 

[fenway]

You'll need two such where conditions.

[Mr Hyde]

I must not be understanding the question right... is there any reason why the string returned by the link I submitted above wouldn't work?

 

just for reference:

REGEXP '[[:<:]]some[[:>:]]' = 1 AND `` REGEXP '[[:<:]]words[[:>:]]' = 1