[thelist] MySQL sub-select with PHP [was: sql cross table exclusion??]

Michael Kimsal michael at tapinternet.com
Mon Sep 2 12:09:01 CDT 2002


On Fri, 2002-08-30 at 20:49, rudy wrote:
>
> why not save the results in a temp table in the database?
>
> then you can use that table to join with other tables for iterative
> searches
>
If performance and/or concurrency is an issue, this is probably not
the best approach, especially with MySQL.  We just finished a code
audit last month for a company that had done this, and this was the
biggest cause of speed issues for them.  They were having trouble
having more than 10 concurrent sessions, and it was primarily the MySQL
database which was the bottleneck.

An example: one page's execution time was 12 seconds.  Big report
with lots of queries with data from different tables.  They were
making temp tables and populating them, then doing queries on that.  We
got rid of a couple of the temp tables and did the calculations in
memory in PHP - page was now done in 5 seconds.

It's easier to scale the front end webservers than it is the
database server.  If that's your bottleneck, you'll have a hell of a
time overcoming it without moving wholesale to a new database platform.

I'm not suggesting 'don't do it' - I'm saying if performance is
important (current or future) then do some testing before deciding
on an approach.   Doing something 'the long way' now may save
you headache later on.  Then again, in some cases, it won't.


>
> the hard part comes six months down the road when you've forgotten all your
> nifty neato code, and have to make a change to it
>

That's what documentation is for.  It's just as hard to figure out why
11 temp tables are being made in a database and what all the strange
joins are as it is to follow code.  Code may be easier, because
you probably work with more PHP code on a daily basis than advanced
SQL queries.



--------------------------
Michael Kimsal
http://www.tapinternet.com
http://www.logicreate.com
734-480-9961




More information about the thelist mailing list