multiple LEFT JOINs

[sirugo]

I have four tables which I want to join to a table with "complete" information.
There are about 2000 rows in each table.
When I try a multiple LEFT JOIN it works as it should but I takes about 20 seconds or more.

How do I optimize a query to handle many rows?
What other JOIN-methods can I use for this?
Should I index in any special way?

 

[Kozusnik]

The fastest way to join files is on numeric fields. If you're joining on text fields, that will take more time. Is this a 1 to 1 join? It sounds like it may be 1 to all. Your key fields need to be unique in each table, joined by the primary key to get the 1 to 1.
Could you give some example data. Sometimes it's easier to write like below, although the LEFT JOIN (LEFT OUTER JOIN) is probably faster...

SELECT * FROM t1.table1, t2.table2 WHERE t1.key= t2.key
If there are multiple WHERE statements, just separate them with commas also.

Mark

SELECT * FROM management WHERE clue > 1
> 0 rows returned

--ThinkGeek T-Shrit

 

[ggggus]

Indexes indexes indexes...indexes fix everything...well not everything.

Indexing will slow down inserts, but dramatically increase complex select statements.

SELECT tableA.something FROM tableA LEFT JOIN tableB ON tableA.something=tableB.something;

in this statement, make sure that tableB.something is indexed...tableA.something does not need to be indexed because the statement is not selecting a subset since no WHERE clause was defined. If you defined "WHERE tableA.something='some text'"; you could create an index on tableA.something.

There is a balance to be struck if you do a lot of inserts on the tables...as I said, indexes slow down inserts.

***************************************
J. Jacobs

http://www.piercedmedia.com

 

[sirugo]

Hey thanks

Yes, as I thought I must do indexing on each "something/key".
And of course I have unique integers everywhere.
I've never worked with big tables like these before and therefore it's the first time I stumbled on slow queries.
There will be no insertions at all because the data is being imported from other sources and should only be presented, never tampered with.

Thanks again

 

[sirugo]

But one more thing.
Sometmes I'm doing chained LEFT JOINs like

FROM a
LEFT JOIN b ON a.x = b.x
LEFT JOIN c ON b.y = c.y

etc.

...meaning that I might want indexing on both x and y fields.
Is this bad?

 

[guelphdad]

Indexes on more than one field is not bad at all. you can only use a single index per query (i.e. if you have two, the database will choose the optimal one), but as you've shown two separate joins there then index on x will be used in the first join on b and ignored in the second query since the index on y will be picked up.

FWIW 2,000 rows per table is negligible, mysql can easily handle hundreds of thousands or millions of rows, particularly when you have them indexed correctly.