fenway

Actually, you don't need a subselect, it's just a single table: SELECT max(order_num) from orders

Each question as a column really isn't ideal... not normalized table design. As for the "tie", it's pretty much random. If you want to deal with this boundary case, it's more complicated.

I agree... you need a single table with job_uid and person_uid fields.

So they're not currently represented in the db?

I don't understand... what information does a001 contain vs. b001?

Where are these values coming from?

Forget this "field to field" thing that you've envisioned - it doesn't exist. You can put whatever you want in the outer select list -- even * -- and it won't change anything, just like in a regular sql statement, it doesn't affect the table relationships, just the columns displayed. The inner "*" -- the one after select in the subquery -- has no meaning, and is just a place holder. It's just so that it looks like regular sql, but it's not doing anything. The "match" is ONLY determined by the where clause... there's nothing fancy about this at all. You're making this more complicated than it is.

How do you define failed? No record inserted? If so, just cause a key collision.

Probably... though in that case, because you have OR, it won't really use both (unless you have index_merge and v5)... also, why are these left joins and not inner joins? Basically, you need to give the server an way to find the matching row in the next table. You start with searching the entire forums table. You take the last_post_id, and look it up in the forum_posts table on post_id -- that should use the PK index. Then you need the two FKs in the topics table. Then the user_id (again, the PK). I'd like to see the current EXPLAIN for that 4 table query.

This is a classical name/value pair example -- I assume the categories and subcategories are "real" -- i.e. have UIDs to records in tables somewhere.

Doesn't sound like this has anything to do with mysql... I assume you have the correct login credentails?

From the mysql manual: SELECT DISTINCT store_type FROM stores WHERE EXISTS (SELECT * FROM cities_stores WHERE cities_stores.store_type = stores.store_type); Is the correct statement to answer the question: What kind of store is present in one or more cities? HTH.

Forget about php for a minute... why do you have tables like this? What do they represent?

No, entirely incorrect -- it has nothing to do with the outer query unless you specifically reference fields from the outer query in the where clause of the exists subquery. In this case, if there are any rows in table 2, the subquery will return the "TRUE" boolean.

Sort of... can you give an example?

If you don't have indexes, then your where clause didn't "solve" the problem, it masked it. At the very least, you need to make sure that mysql can quickly search the column you're using in your join condition.

Yup, transactions would apply here -- I don't think you can rollback a trigger... unless you trigger an SP... but I don't do things like that, so I don't know.

It's not different than any other query... except that it returns true/false instead of the actual records that matched.

Could you post the EXPLAIN for the full query? You don't have any indexes on your columns you're using in your joins. Hence possibile keys = NULL, hence ALL, hence table scan, hence bad.

In principle, you can use a multi-table delete if you can join the tables based on this user_id... check the refman, though, the syntax is a little funny.

It returns a boolean based on the where clause.

I'm sorry, I still don't understand. You want to include a FK reference to the file from all other tables?

First, there is no mysql v4.3.4. As for your question, if you wanted to do it one question a time, you could do the following (guessing DB structure here): select question_id, count(answer) as cnt from pq01 where question_id = X and user_id = Y group by question_id order by cnt desc limit 1 But your table name suggests you've made one table per question?

Define "link" and "this".

If it's a temporary hack, then no problem... I just tend to find that "temporary" often lasts a lot longer than expected, and the extra work at the outset is insignificant in the long run. Just my $0.02.