http://qs321.pair.com?node_id=528084


in reply to mysql's join too slow; using Perl to compare two tables

I have to agree with friedo. This sounds like an index issue. What kind of indexes do you have on catalog and pivot? If you don't know what I am talking about, then you need to educate yourself on indexes.
Here is how you would create some indexes on these two tables. Enter these in the mysql command line tool. 
create index c_atldp on catalog ( author, titulo, label, description, 
+price )

create index a_atldp on author ( author, titulo, label, description, p
+rice )

[download]

You can create several different types of indexes depending on how and what kind of queries you are writing and a full discussion of indexes is OT. Sometimes you need to write new ones for a new type of query.
JamesNC

Replies are listed 'Best First'.
Re^2: mysql's join too slow; using Perl to compare two tables
by Andre_br (Pilgrim) on Feb 05, 2006 at 20:36 UTC
    Ok! I've implemented the indexes and things are now warp speed! Mysql is great, I was the one screwing up... lghs. Also because I was imposing a conversion to binary of all the fields, to make sure the different cases werenīt ignored when comparing similar records: 

    select pivot.received_id 
from catalog inner join pivot on 
( binary catalog.author = binary pivot.author and 
binary catalog.titulo = binary pivot.titulo and 
binary catalog.label = binary pivot.label and 
binary catalog.description = binary 
pivot.description and 
binary catalog.price = binary pivot.price );

    [download]

    Iīve put the indexes and things didnīt get any better. Then I decided to get less perfectionist, and took away all the binaries... ok, mysql, no problem, ths isnīt really thaaaat necesary for me. Well, then the join that was taking 3 minutes, surprised me with the output screen in, guess what, 1 second!

    Thanks for all the inputs, my fellow coders.And sorry for not having mentioned this 'binary' issue, as I thought it might not be it. But as you directed me to the only one thing that could explain the slowness, and it didnīt solve it, I could start suspecting about the binary.

    Take care

    Andre

[reply]
[d/l]

In Section Seekers of Perl Wisdom