Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
PerlMonks  

Re: mysql's join too slow; using Perl to compare two tables

by JamesNC (Chaplain)
on Feb 05, 2006 at 16:53 UTC ( #528084=note: print w/ replies, xml ) Need Help??


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 )

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


Comment on Re: mysql's join too slow; using Perl to compare two tables
Download Code
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 );

    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

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: note [id://528084]
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others studying the Monastery: (8)
As of 2014-12-23 02:02 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    Is guessing a good strategy for surviving in the IT business?





    Results (133 votes), past polls