leon
leon

Reputation: 151

How can I optimize this query?

SELECT * 
FROM tfdata,linkdata,gndata,serdata 
WHERE tfdata.web = linkdata.web 
  AND tfdata.web = gndata.web 
  AND tfdata.web = serdata.web 
  AND tfdata.web = '"mysql_real_escape_string($uri)"'"

I feel this method is slow and I want to know will LEFT JOIN work on this? If yes how do I add the same, or is there any other way around?

Upvotes: 0

Views: 67

Answers (3)

Mark Byers
Mark Byers

Reputation: 839114

You can rewrite your query using joins. It will make it more readable, but not faster.

SELECT *
FROM tfdata
JOIN linkdata ON tfdata.web = linkdata.web
JOIN gndata ON tfdata.web = gndata.web
JOIN serdata ON tfdata.web = serdata.web
WHERE tfdata.web = 'some_uri'

One advantage (but not the only one) of this syntax is that it makes it easier to change from using an inner join to an outer join.

To optimize I'd first start by running EXPLAIN SELECT ... to check that you have an index on the column web on each table and that this index is actually being used by the optimizer. If the index is missing then adding it will most likely improve the speed of your query.

If the query returns a lot of rows or columns you can improve the performance by only returning the columns you need and by only returning the first few rows and implementing paging if you want to see some more rows.

Upvotes: 1

Maximilian Ehlers
Maximilian Ehlers

Reputation: 328

you could name the cells you are looking fore instead of "*". That would improve the performance...

Upvotes: 1

Yochai Timmer
Yochai Timmer

Reputation: 49271

SELECT * FROM 
    ( SELECT * FROM tfdata WHERE tfdata.web='"mysql_real_escape_string($uri)"'") As Result1,linkdata,gndata,serdata 
    WHERE Result1.web = linkdata.web AND Result1.web = gndata.web AND Result1.web = serdata.web 

Use the inline select to get a result table with less items, then join them all together.

Upvotes: 0

Related Questions