MySQL query issue with combining two tables

Question

I have two tables:

`search_chat` (
  `pubchatid` varchar(255) NOT NULL,
  `profile` varchar(255) DEFAULT NULL,
  `prefs` varchar(255) DEFAULT NULL,
  `init` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `session` varchar(255) DEFAULT NULL,
  `device` varchar(255) DEFAULT NULL,
  `uid` int(10) DEFAULT NULL,
  PRIMARY KEY (`pubchatid`)

and

`chats` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `chatlog` varchar(255) DEFAULT NULL,
  `block` varchar(2) DEFAULT '',
  `whenadded` datetime DEFAULT NULL,
  `pubchatid1` varchar(255) DEFAULT NULL,
  `pubchatid2` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)

So basically people chat with each other through a search system based on prefrences. The further they are apart, the worse it is. So the query I have is simple:

SELECT * 
  FROM search_chat 
 WHERE levenshtein(profile, "[user_prefs]") < 20
       AND pubchatid <> "[user_pubchatid]"
ORDER BY 
       levenshtein(profile, "[user_prefs]") 
 LIMIT 1

It is a shitty query in itself, but it does the job (everything between "[]" is a variable I put in, just to make it clear).

As you can see this query only makes a selection between two peoples preferences (prefs) and how they are (profile). So far so good.

I have been bugging around some to make this query also check if they have had previous chats. That is where "chats" comes in. I can not get the query to check for a proper user and see if they have an open chat.

In chats, the "search_chat.pubchatid" can be either "chats.pubchatid1" or "chats.pubchatid2"

So somehow I have got to make these two work, making chats rule out options in search_chat.

Levenshtein distance complexity is O(mn) so it can became slow query if you have lots of data. — pero, Mar 21 '14 at 12:46
Single quotes, I do. This is just for show. In PHP it's "'.$val.'". That is not the problem. The levenshtein doesn't have that much data to go through. Everytime somebody found a chat partner, both users get deleted from that table. So not meany records there. And to make the search even shorter i need to involve the chats table. So the problem is not that it's not working, I just cant get them to work together. — Matt, Mar 21 '14 at 12:50

score 1 · Accepted Answer · answered Mar 21 '14 at 13:11

Do you want something like this:

-- ... ( start of query as per your question )
and not exists ( 
  select * 
  from chats
  where ( ( chats.pubchatid1 = search_chat.pubchatid ) 
       or ( chats.pubchatid2 = search_chat.pubchatid ) ) 
  and -- ... add any restriction on how recent the chat was 
)

MySQL query issue with combining two tables

1 Answers1