22,949 reputation
53983
bio website in.another.dimension
location Athens, Greece
age 47
visits member for 4 years, 1 month
seen 9 hours ago

Interests: math, programming and games, not necessarily in that order.

Favourite numbers: Surreals


11h
comment How do I define a 'flexible' relationship in my table?
Oh, because there are so many question that raise restrictions. Happy you do not. Can you also add if you are interested in a specific DBMS or not?
12h
comment Viewing execution plans for queries with parameters
My eyes bleed. Allergic reaction to the redundant parentheses.
13h
revised Unable to get the latest value added to Mysql table based on posted time
added 33 characters in body
13h
comment Unable to get the latest value added to Mysql table based on posted time
me too ;). You have my +1 anyway.
13h
comment Unable to get the latest value added to Mysql table based on posted time
If you meant to write a correlated subuery, it would have to be: WHERE posted_datetime in (SELECT MAX(posted_datetime) FROM example WHERE id = e.id);
13h
comment Unable to get the latest value added to Mysql table based on posted time
Keeping the previous IN syntax would be possible with WHERE (id, posted_datetime) IN (SELECT id, MAX(posted_datetime) ...) but as I said (several versions of) the mysql optimizer cannot fully optimize queries with where x in (select ...)
13h
revised Unable to get the latest value added to Mysql table based on posted time
added 90 characters in body
13h
comment Unable to get the latest value added to Mysql table based on posted time
There is a chance that your query would show false positives (different ids, datetime in one id identical to the max(datetime) in the other). And since subqueries with in usually are not very efficient in mysql, I'll edit your query. You can of course revert if you don't like my changes ;)
14h
comment mysql - order by first condition in where clause
order by -(name like 'ci%'), name will probably work, too (one more addition to your collection of counterintuitive code samples ;)
14h
comment Subquery optimization
Show us the SHOW CREATE TABLE orders_created; The issue is that this table does not have latin1 charset but very probably utf8. That's one reason why the query is not efficient.
14h
comment Subquery optimization
Thnx. Two more questions: How many rows does the query return? Have you tried the NOT EXISTS version and what explain plan would that version have?
14h
revised delete query performance issue on mysql
added 51 characters in body; edited tags
14h
comment delete query performance issue on mysql
Do you have any indexes on the 2 tables? Add the output of SHOW CREATE TABLE ...; for both.
14h
comment How do I define a 'flexible' relationship in my table?
Are you allowed to change the design (e.g. by adding a Product table or modifying table/column properties, PKs, FKs?)
14h
comment Subquery optimization
Edit the question and add the SHOW CREATE TABLE tablename; for both tables.
14h
comment mysql - order by first condition in where clause
I also removed one of the likes in the where. Not needed.
14h
revised mysql - order by first condition in where clause
deleted 19 characters in body
14h
comment Subquery optimization
Add the SHOW CREATE TABLE tablename; output for both tables. (what is the datatype of order_id? VARCHAR(50)? )
14h
comment Subquery optimization
Do you have an index on order_id in both tables?
15h
comment mysql - order by first condition in where clause
order by name will give you different order (3,1,2) in the result than you claim (1,2,3).