SQL Server Performance Forum – Threads Archive

help in mysql query

Hi everybody I have a problem related to mysql querying.
Let me explain it with an example. I have two tables t1, and t2 and i want to retrieve values of three fields f1 f2 and f3 each belong to either t1 or t2 as follows t1 -> f1
t2 -> f2, f3 there are lots of other fields too in these two tables. i am given two words say ‘word1’ and ‘word2’. I want to search these two words in the above three fields. both t1 and t2 have a common index field. What i want is like this. select t1.f1, t2.f2, t2.f3 from t1, t2 where ( ( (t1.f1 like(‘%word1%’)) or (t2.f2 like(‘%word1%’)) or (t2.f3 like(‘%word1%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) ) and (t1.indx = t2.indx)); I tried this way. But what it does is, it retrieves all the records along with the correct record. i.e. it takes all records in the table along with t1.f1 like ‘word1’. SO it takes toooooooo much of time. and i am not good at sql writing. I am really desperate. Can there be a way to do this. I dont want the other records which come along with it . I want only the records where (t1.f1 is like ‘word1’ or t2.f2 is like ‘word1’ etc.)and same is the case with word2. Thx in advance Renjith

---

What you are missing is a JOIN between the 2 tables. This is part of the reason you are getting so many rows. A simple example. Suppose you have 2 tables:
t1 is a client table, and you have there the column clientID.
t2 is an order table, it has an orderID and the clientID to which this order refers. In this example, the column ClientID is common to both tables. To see all orders for all clients you will do SELECT t1.*, t2.*
FROM t1 INNER JOIN t2 on t1.ClientID = t2.ClientID In your case it would be something like SELECT t1.f1, t2.f2, t2.f3
FROM t1 INNET JOIN t2 ON t1.column_in_common = t2.column_in_common
WHERE ( ( (t1.f1 like(‘%word1%’)) or (t2.f2 like(‘%word1%’)) or (t2.f3 like(‘%word1%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) ) and (t1.indx = t2.indx)); Just change the column_in_common and it should work. Bambola.

---

U can write the query as below than using joins due to performance issue select a.f1,b.f2,b.f3 from
(select indx,t1.f1 from t1 where t1.f1 like ‘%word1%’) a ,
(select indx,t2.f2,t2.f3 from t2 where t2.f2 like ‘%word1%’ or t2.f3 like ‘%word1%’)b
where
a.indx=b.indx Thanks Rushendra

---

samrenj was talking about MySql database, and if I am not mistaken subqueries are not supported (at least until version 4.0.12).

---

Also it may be worth trying to restructure the query to simplify the criteria being evaluated at any given time select
t1.f1, t2.f2, t2.f3
from
t1
INNER JOIN t2 ON (t1.idx = t2.idx)
WHERE
(t1.f1 like ‘%word1%’) or (t2.f2 like ‘%word1%’) or (t2.f3 like ‘%word1%’)
UNION ALL
select
t1.f1, t2.f2, t2.f3
from
t1
INNER JOIN t2 ON (t1.idx = t2.idx)
WHERE
(t1.f1 like ‘%word2%’) or (t2.f2 like ‘%word2%’) or (t2.f3 like ‘%word2%’) This may or may not perform better, mysql optimiser is not as advanced as sql servers and so sometimes you need to approach queries a little differently. I have assumed t1.idx and t2.idx are your primary keys, if they arent then try bambola’s tip above first.

---

This query will lead to a table scan. So seperating it (unless it is optimized) will result 2 table scans.
I would take this approch when a column is indexed and param is sarg.
(By the way, also UNION [ALL] is available from a certain version (4 something) and above). Bambola.

---

Bambola, As far as I understand, the original post had the statement both t1 and t2 have a common index field. This means that the column _in_common that you are refderring to are t1.indx and t2.indx. Gaurav

---

Oh, I’ve missed that. So the qery should look like this: SELECT t1.f1, t2.f2, t2.f3
FROM t1 INNET JOIN t2 ON t1.indx = t2.indx
WHERE ( ( (t1.f1 like(‘%word1%’)) or (t2.f2 like(‘%word1%’)) or (t2.f3 like(‘%word1%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) or (t1.f1 like(‘%word2%’)) ) ); Bambola.

---

If I am not mistaken the original query had the same syntax. Just the join style has been changed to ANSI style. Will this make a difference in the resultset? Gaurav

---

Yes, I think you are right gaurav. The ANSI style join will not affect performance or the result set generated. But unless we have all missed something, I cant see why this query would return spurious results. I think we must wait for samrenj to respond with more details.

---

]]>