Any geniusqls out there??

paolodit

Hi everyone… I am OK at php and simple mysql. But I am absolute rubbish at genius level MySQL. Help! Is there anybody out there that is really good at mysql?? I don’t know how to write IF clauses, build short term tables or anything..

I can build happy eater sql statements separately, but haven’t a clue how to efficiently merge them into one effiecient whopper. Yikes, this needs real geniusql.

This is for a travelling-companion-website. When someone’s profile is activated by an admin person, I need an sql statement to search through the all members for similar people, prepare list of their member_id ready to email them: -

1. Find people who have matchfinder enabled
Select id, age, country from dt_members where matchfinder=’1’
This gets all the members, their age and resident country, who have match finder enabled

For each of the above people…

2. find people that are looking to travel to the same destination
destinations are stored in table destinations_x

each destinations_x record, has fields: id, profile_id and destinations_id
(each person can input as many destinations as they like)
Select profile_id from destination_x where destinations_id =’$destination’
(loop for each of the destinations in new persons profile)
This gets the ids of members with a similar destination in mind

2b. Resolve the profile_id to member_id
That’s right the profile_id, from the above, is different from member_id
Select member_id from profile where_id=’$profile_id’

3. find people that are looking for the same holiday type
holilday types are stored (as relationship) in table relationship_x

each relationship_x record, has fields: id, profile_id and relationship_id
(each person can input as many holiday types as they like)
Select profile_id from relationship_x where relationship_id =’$relationship’
(loop for each of the holiday types in new persons profile)
This gets the ids of members with a similar holiday type in mind

3b. Resolve the profile_id to member_id
Select member_id from profile where_id=’$profile_id’

Put in one nice list ready to go
Delete duplicates

bastien

you'd think that combining those sql statements would get you a performance gain...the trouble is that inner joins suck the life out of the db engine...mysql uses abrute force approach to joining tables in a select like this and its not the most efficient at it...

We tried that approach joining about 5 tables with inner and outer joins as the design dictated. Reports would take about 5 minutes to generate and slow the server to a crawl. I ended up breaking the sql into three portions and reduced the time to about 10 seconds total for about 5x the output...

If you are waiting very little for this info to be matched, don't mess with it, esp if the app is high load (ie you have lots of users)

You might try to optimize it by joining 1, 2b and 3b since they all revolve around the users IDs. But the thing to watch is the time it takes to gather all the info...

Sxooter

If you're gonna start writing complex queries, get a jump on the competition and switch to PostgreSQL where things like 5 way joins are no big deal.

The query Planner in PostgreSQL is as good as the planner in most commercial databases, and better in a few corner cases.

Plus it has almost all of the SQL92 spec implemented, so you won't be constantly hitting a wall of "can't do that yet" with it.

csn

Hey Sxooter,

Didn't a new SQL spec just come out, get finalized, or something? Know what's new and what pgsql's plans are for implementation?

paolodit

thanks everyone for your input. really appreciated...

In the end,

I built a first wave of IN statements to find the persons destinations & holiday_types

the results from this (the people IDs) were then made into one more master IN statement...

(I had never used IN before).. MysQL documentation is sh*t compared to PHP.

I used DISTINCT to de-duplicate a list

I then did a new query for each result, to find their real profile ID

I learnt how to use FROM where you can label is something as a shortcut

Phew!!

Cheers Paolo

ultraslacker

Did you try joins?

INs get rewritten in the parser, so they are just shorthand.