SQL Joins & Speed

Which is faster or is there no differnece?

[Nick_W]

Hi all,

With SQL Joins, would there be any speed differnce or other performance considerations between these 2 scenarios?

table1
id INT NOT NULL AUTO_INCREMENT,
...seven/eight other columns

table2
id INT NOT NULL UNIQUE (gets value from table above's id)
...2-3 other columns

The 2 queries would be either to select stuff from table1,table2 WHERE table1.id=table2.id

Or the other way around.

Can anyone offer some comment?

Many thanks!

Nick

[Birdman]

I don't completely understand the question, Nick. By "two scenarios", do you mean either using one or two tables.

When I add another table to a database to avoid repetitive data(which I assume is what you are doing), I usually give that table it's own unique id and then add another field to cross reference the first table.

Table "Foo"
foo_id INT NOT NULL AUTO_INCREMENT,

Table "bar"
bar_id INT NOT NULL AUTO_INCREMENT,
foo_bar_id INT NOT,

[Nick_W]

Hi Birdman,

I meant in the SELECT. Would there be a diffence in starting with one table or the other?

SELECT * FROM table1,table2

or table2,table1

But, since posting this I have toyed with the idea of just adding extra fields to table1.

It's for the 'blog' I've been boring you all about for the last week. I need to categorize posts and thus need a few more fields. Currently I have:

id INT NOT NULL AUTO_INCREMENT,
authorid INT NOT NULL,
date TIMESTAMP,
description VARCHAR(255) NOT NULL,
title(VARCHAR(255 NOT NULL,
text TEXT NOT NULL,

and another table for 'live' and 'allow comments'

I need to add

• section
  
• level (beginer,advanced,scary)

So I was thinking of having 2 tables (and merge the 'live' and 'allow coments') like this:

table1

• Id
  
• Authorid
  
• Text

and

• id
  
• Corresponing Id
  
• section
  
• level
  
• is_live
  
• allow_comments
  
• date
  
• title
  
• description

Which I think would make it much quicker to buile section indexes and 'recent posts' etc...

Does that make any sense?

Nick

[Birdman]

I don't think there is a difference in the order you list the tables, yet I could be wrong. Your structure looks good except it seems that 'Level' could go in the first table, since it refers to the user and not the blog entry.

DB dev is fun stuff, huh? ;)

[DrDoc]

If you're going to query the database where the ID's have to match, then you should definitely do a join, not just look something up in two tables. Also, it's better to join tables than querying the db twice.

Speed? Well, unless your queries get extremely complicated, or the tables are enormous... I'd worry more about server load :) Join is still faster though...

Question: what are the 'live' and 'allow comments' columns for? If 'live'=true and 'allow comments'=true then you can post? If so, why not just make it:

live=0 - not live, no comments
live=1 - live, no comments
live=2 - live, comments

Or, did I misunderstand something? :)

Oh, and table order shouldn't matter...

[DrDoc]

Also, hmmm... You know it's good to avoid REGEXPs as much as possible, right? If you're not being careful it is possible to create looping REGEXPs that will match either everything or nothing, but you won't see the result until it has tried all possibilities...

[Nick_W]

live=0 - not live, no comments
live=1 - live, no comments
live=2 - live, comments

Now that's a damn fine idea! - Cheers ;)

Nick

[Jocelyn]

To know more about how mySQL executes your query, do:
EXPLAIN <query>

Jocelyn

[chameleon]

One other thought -- I haven't tried this myself, but I've read that using an INNER JOIN statement is faster than what you have. In other words, this:

SELECT *
FROM from table1 INNER JOIN table2
ON table1.id = table2.id

Instead of this:

SELECT *
FROM table1,table2
WHERE table1.id = table2.id

This was for MS SQL Server, but it may be true of other databases as well.

I've got a lot of great links with SQL performance tips. If anyone wants them, just sticky mail me.

[jamesa]

On a related note... From my experience you need to watch for the WHERE statement when doing joins. If you have something like this is the WHERE statement you can run into trouble, regardless of how you've indexed:

WHERE table1.id=table2.id AND table1.field1='x' AND table2.fielda='y'

That's because you're actually searching for matches across more than one table, which turns out to be much more resource intensive.

I worked on a site that had only about a couple-hundred thousand rows of data in their database. All the queries searched across multiple tables and they were taking 1-5 minutes to complete depending on the query, and some queries would just hang indefinitely. The ultimate solution was to have all the searchable fields in one table, which brought the times down to a couple of milliseconds per query. Dramatic difference.

<edit>Corrected typo</edit>

[DrDoc]

Very important point, jamesa! Thanks for the advice.

And, chameleon, you're right... inner joins are more efficient. However, that also depends on the type of query you have...