LIMIT Join

[dreamwest]

I need to limit and order  this join by id (primary key) to 40 queries is there a way i can do this??

 

Is this even possible??

 

$query = "SELECT gallery_import.*, video.* ".
"FROM gallery_import, video ".
"WHERE gallery_import.description_import = video.title ORDER BY `video`.`id` DESC  LIMIT 40";
$result = mysql_query($query) or die(mysql_error());

[Mchl]

There's no JOIN here (well ok... there is... sorta hidden ). It's a query, and you can limit it to 40 rows.

And yes, it is possible, and this sntax should work. Do you get any errors?

[dreamwest]

no errors....its just buggy keeps overwriting values i dont want it to , maybe its how the query is structured.....but your right the limit works

 

The reason i have to modify it is because it taking over 7 minuets to execute, and my site is database driven so i cant acess the site while the scripts running, and i need to run this every 20mins

[Mchl]

Do you have indexes created on 'gallery_import.description_import' and 'video.title' ?

[dreamwest]

no just the primary key is has an index.

 

Ive never tried to index tables before, but i read if your alway updating values you can have a negative effect using indexes

[Mchl]

Wrong. You should always create indexes on fields you use to join tables.

[dreamwest]

Ok trying it now.

 

I was able to bring the query time down to 40 seconds with the LIMIT , ill let you know how much the INDEX will bring it down by....

[dreamwest]

Bit stuck with the INDEX creation, heres what ive got

 

mysql_query("CREATE INDEX description_index ON gallery_import(description_import)")
or die(mysql_error()); 

 

but im getting this error:

 

BLOB/TEXT column 'description_import' used in key specification without a key length

 

 

gallery_import is the table name

[Mchl]

Oh my... joining on BLOB fields... no wonder it is slow...

 

CREATE INDEX description_index ON gallery_import(description_import(indexLength))

 

Instead of indexLength put number of characters you want to have indexed.

[dreamwest]

Brilliant!!

 

Execution time = "Query took 0.0011 sec"

 

Bit better than 7 minuets originally , site also runs faster

 

Thanks

[Mchl]

Here's some lecture for you

http://dev.mysql.com/doc/refman/5.1/en/mysql-indexes.html

[corbin]

Is:

 

SELECT blah FROM sometable1 a JOIN sometable2 b ON b.some_column = a.some_col;

 

faster than:

 

SELECT blah FROM sometable1 a, sometable2 b WHERE b.some_column = a.some_col;

 

 

or did I dream that?

[Mchl]

I can not point you to a source, but I read that MySQL query optimizer rewrites such joins to WHERE. So they're equivalent.

[corbin]

Hrmmm yeah, I guess logically it probably handles them the same way.

[fenway]

I can not point you to a source, but I read that MySQL query optimizer rewrites such joins to WHERE. So they're equivalent.

Yes, but NEVER use comma.