Board index mysql

converting a MySQL 4.1 subquery to something that'll work with MySQL 4.0

converting a MySQL 4.1 subquery to something that'll work with MySQL 4.0

Post by yawnmot » Mon, 24 Jul 2006 09:15:23


Say I have the following SQL query:

UPDATE phpbb_users
SET user_nthpost = (
SELECT post_time FROM phpbb_posts
WHERE phpbb_users.user_id = phpbb_posts.poster_id
ORDER BY post_time ASC
LIMIT $n,1
)

ANay ideas to how I'd do this on MySQL (which doesn't support
subqueries)?

I could do something like...

for each $user_id and for some $n, set $user_nthpost equal to the
result of the following:

SELECT post_time FROM phpbb_posts
WHERE phpbb_posts.poster_id = $user_id
ORDER BY post_time ASC
LIMIT $n,1

then...

UPDATE phpbb_users SET user_nth_post = $user_nthpost WHERE user_id =
$user_id

Unfortunately, if there are a lot of $user_id's, this could be quite
slow. It'd take 2*(the number of users) queries to finish.

Is there a way I can do it, instead, with maybe, I dunno... 1 query,
or 2, or 3, or some other small (and fixed) number?

Any ideas would be appreciated - thanks!
 
 
 
Top

1. More subquery madness! - MySQL 4.1 vs. 5.0

2. Fast query in mysql 4.1 is too slow on mysql 5.0, why?

I am migrating a company PHP/mysql application to a new Linux machine, and
while doing so, am going from mysql 4.1 to 5.0. I have transferred the
database successfully using mysqldump from the old machine to produce a
file of sql commands, and then reading in this file on the new machine.

The database data and table structure are therefore the same on both
machines. I am using MyIsam tables.

Here is the problem: One of my queries is rather complicated, involving
several table joins, and one of the joins is on a derived table in order
to select the maximum value of an attribute. This query runs with mysql
4.1 in a fraction of a second. But the very same query takes over 5
seconds to run on the 5.0 machine.

If I do an "explain" on the query on both machines, all columns in the
explain results table are the same except for some differences in the
"extra" column. But the ordering of the rows in the explain table is
different for the two mysql versions.

Before I go into such detail as presenting the queries, table structures,
etc. here, I would like to ask if anyone can explain what is going on
here? Why does mysql 5.0 take so much longer to perform this query than
the older version 4.1, if the table structures and data of the database
are the same?

I see myself having two choices: downgrade mysql on the new machine to
version 4.1, but this is something that I really do not wish to do, as I
wish to keep mysql more up to date. The other choice is to keep adding
indexes and modifying the query on the 5.0 machine to speed it up. But
still, the basic problem is there: the query worked fast and well on 4.1
but not on 5.0, and that doesn't really sound like progress to me :)

Can anyone help?

Steve, Denmark

3. Benchmark of prep stmts on MySQL 4.0 vs 4.1...

4. PHP5 with MySQL 4.0 and 4.1 on different machines

5. migrating from mysql 4.0 to 4.1 using ports

6. dbExpress mysql 4.0 or 4.1

7. PEAR DB not working with recently upgraded MySQL 4.1

8. Tips : Mysql 4.1 and DBExpress with D7 can works together.

9. php 4.4 not working with mysql 4.1

10. left join on a mysql subquery; do I need to alias the subquery?

11. MySQL / clientdataset field mapping ( MySQL 4.0.x/D7 )

12. Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) LEOPARD

13. Can't connect to local MySQL server through socket '/var/mysql/mysql.sock' (2) LEOPARD

14. ERROR 2002: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

15. mailing.database.mysql-internals, mailing.database.mysql, mailing.database.mysql-java, comp.lang.java.programmer

All times are UTC
Board index