StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>From what I gather you're trying to put a layer between a 3rd-party forum application that you can't modify (obfuscated code perhaps?) and MySQL. This layer will intercept queries, re-write them to be executable individually, and generate PHP code to execute them against the database and return the aggregate result. <strong>This is a very bad idea.</strong></p> <p>It seems strange that you imply the impossibility of adding code and simultaneously suggest generating code to be added. Hopefully you're not planning on using something like <a href="http://pecl.php.net/package/funcall" rel="nofollow">funcall</a> to inject code. <strong>This is a very bad idea.</strong></p> <p>The calls from others to avoid your initial approach and focus on the database is <strong>very</strong> sound advice. I'll add my voice to that hopefully growing chorus.</p> <p>We'll assume some constraints:</p> <ul> <li>You're running MySQL 5.0 or greater.</li> <li>The queries cannot change.</li> <li>The database tables cannot be changed.</li> <li>You already have <a href="http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html" rel="nofollow">appropriate indexes</a> in place for the tables the troublesome queries are referencing.</li> <li>You have <strong>triple-checked</strong> the slow queries (and run EXPLAIN) hitting your DB and have attempted to setup indexes that would help them run faster.</li> <li>The load the inner joins are placing on your MySQL install is unacceptable.</li> </ul> <p>Three possible solutions:</p> <ol> <li>You could deal with this problem easily by investing money into your current database by upgrading the hardware it runs on to something with more cores, more (as much as you can afford) RAM, and faster disks. If you've got the money <a href="http://www.fusionio.com/products/iodrive/" rel="nofollow">Fusion-io's products</a> come highly recommended for this sort of thing. This is probably the simpler of the three options I'll offer</li> <li>Setup a second master MySQL database and pair it with the first. Make sure you have the ability to force AUTO_INCREMENT id alternation (one DB uses even id's, the other odd). This doesn't scale forever, but it does offer you some breathing room for the price of the hardware and rack space. Again, beef up the hardware. You may have already done this, but if not it's worth consideration.</li> <li>Use something like <a href="http://www.dbshards.com/dbshards/dbshards-is-not-a-database-engine/" rel="nofollow">dbShards</a>. You still need to throw more hardware at this, but you have the added benefit of being able to scale beyond two machines and you can buy lower cost hardware over time.</li> </ol>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload