Welcome to WebmasterWorld Guest from 23.22.182.29

Forum Moderators: open

Message Too Old, No Replies

How to find related items in a no so similar list?

queries, match, mysql

     
4:41 am on Apr 30, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 29, 2008
posts: 65
votes: 0


Hi,

I have a question about searching in MySQL:

I have a table with 1.5+ million records in only one field plus the auto increment field. The records might seem similar some times, but some others they are not what kind of query I can run to try to find similar items?

Note: I was thinking about LIKE queries or FULL TEXT, but after testing them I found out they are not good.

Thank you
6:29 pm on Apr 30, 2010 (gmt 0)

Senior Member

WebmasterWorld Senior Member rocknbil is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Nov 28, 2004
posts:7999
votes: 0


what kind of query I can run to try to find similar items?


What aspects of the records make them "similar?"

Items that are the same color, size, or other attributes?

Some part of the actual record that is similar?

Mnemonic similarities, like "here" and "hear" or "to","two," and "too"? (I've always wished for a project in which I could use soundex(). :-)
6:34 pm on Apr 30, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Can you post some examples of what makes the data "similar" to others?

I was thinking about LIKE queries or FULL TEXT, but after testing them I found out they are not good.
What made them "not good?"
2:13 pm on June 13, 2010 (gmt 0)

Junior Member

5+ Year Member

joined:Dec 29, 2008
posts: 65
votes: 0


Hi,

Sorry for the long delay here is a sample:

FIP2XM8S
FIP30M20
FIP32A11X
FIP32B18Y
FIP3AM12
FIP3AM23
FIP3BM13D
FIP3BM14
FIP3BM22
FIP3BM23

I have been thinking in using PHP to cut the strings and get i.e. the first 3 letters or the last 3 and then run the query.
5:02 pm on June 13, 2010 (gmt 0)

Moderator from US 

WebmasterWorld Administrator lifeinasia is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Dec 10, 2005
posts:5550
votes: 24


Well, the following would work:
SELECT *
FROM MyTable
WHERE MyField LIKE 'FIP%'

As long as you have an index on MyField, it should run pretty quickly.

If not, then you probably have other more serious problems and you should do further investigation into what's going on with your server (especially if it is shared with other users).
 

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week

Featured Threads

Free SEO Tools

Hire Expert Members