[RESOLVED] How to improve perf. of a simple Left Join (non unique indexed)

[Tea_J]

Hi guys

I've got two tables

contacts (id,name,mobile_no,etc) (20,000 rows)
inbox (id,mobile_no,msg,etc) (50,000 rows of messages )

I'm trying to load all inbox messages with contact names coming from the contacts table..However the only contact identifier I have in the message table is the mobile_no .. this is because some messages do not necessarily have a record in the contacts table (they're loosely relational so to speak)

obviously the mobile_no in the inbox table is NOT UNIQUE....

The task is to select and display the messages with their CONTACT NAMES if available on the contacts table...

This would be a simple LEFT join:

SELECT
inbox.*,
contacts.coid,
sms_contacts.name
FROM
inbox
LEFT JOIN contacts ON contacts.mobile_no = inbox.mobile_no
ORDER BY receivedtime DESC
LIMIT 50

This works fine but querying just 50 rows takes soooooo long (almost a minute) ..

I have tried to INDEXED/Key the CONTACTS.mobile_no to UNQUE
and also Indexed (Normal) the INBOX.mobile_no (non unique)

But i didnt notice any performance increase at all.

Any other ideas?

I've already developed a PLAN B to this, where I first load all the messages, used an array to store the 1 copy of each mobile_no involved in this query, then build and run a separate single SELECT query.

it's really fast.. but im still curious to know out how to improve that JOIN thing.

Regards to all

[johanafm]

Tea_J;11045859 wrote:

I've got two tables

SELECT
inbox.*,
contacts.coid,
sms_contacts.name

It sure looks like three tables... so I'm guessing sms_contacts is supposed to be the same table as contacts

The first thing you should do if you do not know what happens, is to run an EXPLAIN on the query. You simply take the entire query and put EXPLAIN in front of it:

explain select ... from ...

Without seeing that I can only guess.

My first guess is that you are not dealing with 50 rows, but rather 50k rows at roughly 1k bytes each. Those rows have to be sorted (on receive time), which would then involve handling 50MB data, which is sorted by writing to disk. This problem should be eliminated by adding an index on receivedtime.

My second guess is that you have no index on contacts.mobile_no.

[Tea_J]

Hi Johanafm!

You're right, i had no index on the contacts.mobile_no (i thought i had an index on it already but it was mistakenly set on the next column)

THanks!!!!!!