Joins are damn slow. . .

[Steve Quezadas]

Maybe I'm a bit naive in saying this but. . .

SQL joins are damn slow! I have a simple database of about 37,000 records in [Court Cases] and 2,000,000 records in [Defendants] (soon to be more). When I do a search on [Defendants] (the big table) alone, a saerch can go in like .7 seconds. But if I do a search on [Defendants] joined to [Cases], the search jumps to about 5 seconds. (yes, I indexed the joining fields and the search terms).

This bites. . .

However. I notice that if I do two separate searches it goes quicker (about 2.5 seconds combined). I can do a criteria search on defendants and then put all the resulting case numbers in a temporary table. Then do a join of that temporary table to the much smaller Cases table and do a search on that. I get the same results, and the query time is halved.

Ummm, is there any reason why I shouldn't do this? (Other than the inelegance of running two queries instaed of one) Do people do stuff like this for performance reasons?

- Steve



---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php