156 reputation
16
bio website
location
age
visits member for 3 years, 6 months
seen 9 hours ago

Mar
20
comment How to identify concurrency issues in Oracle 11g?
Indexes, Triggers? What happened during that update?
Mar
20
answered One to many to many
Mar
19
comment How does like query work with indexed table
You need to look at the index as a whole, for the whole table. It is some sort of file. If there's DML on the table the file slowly becomes fragmented.
Mar
19
comment How does like query work with indexed table
The index is for the whole table, so for all records in the table. DML will trigger changes to all indexes that are affected (e.g. multiple indexes containing the same columns).
Mar
19
comment How does like query work with indexed table
I don't really understand that questions. It's basically about disk space management. You want continguous segments for optimal performance and no duplicate or stale entries. However, this is not the case, you cannot compact/defragment larger indexes on every DML operation. It fragments over time and needs maintenance. And this is to my experience more common to fulltext indexes and has a larger impact on them, as they tend to grow larger.
Mar
19
comment How does like query work with indexed table
DML Operations can trigger updates on indexes (e.g. text changes, row deleted, new row inserted). These need to be written to the index and it's gotta be fast - you cannot wait on the index sometimes. Then the database decides to not cleanly insert the new value but rather put it somewhere else so it can be found. Or it deletes it but does not consolidate the space afterwards - and then you get index fragmentation.
Mar
19
comment How does like query work with indexed table
They are usually generated from the indexed texts (your columns in the FT index) and updated when there's DML on the table (DELETE/UPDATE/INSERT).
Mar
19
comment How does like query work with indexed table
Please look at my edit.
Mar
19
revised How does like query work with indexed table
added 406 characters in body
Mar
19
answered How does like query work with indexed table
Mar
18
comment How to find pairs of attributes who did the same thing in a same entity?
This is a simple join. Join user with his friends with their likes and the posts. In the where-clause, put the condition that a like for the same post must exist for user.uid
Mar
18
comment Aggregate query with min
Your approach is ok. What are you going to do about duplicate min-prices? This will get you a bit further: select * from ( select product_id, min(price) as price from ( select product_id, CASE WHEN stock = 0 then NULL else warehouse_id end, CASE WHEN stock = 0 then NULL else price end ) AS f from product_stock group by product_id) min_prices join product_stock stock on stock.price=min_prices.price and stock.product_id=min_prices.product_id ;
Mar
18
comment What is the best query to get the latest row version based on time?
This looks like Oracle. I suggest creating a test table and collecting execution plans and traces for both the queries. It can be that the with-clause leads to a materialization of the subquery which can have some IO overhead. There's an oracle hint to turn it off.
Mar
18
awarded  Commentator
Mar
18
comment Special function slow in where but not in select
If you are trying to implement a poor man's fuzzy fulltext search with mysql and levensthein - you're gonna have a baaaaaad time. Try elastic search or something.
Mar
18
comment Special function slow in where but not in select
See my edit. You can try to get some sort of execution plan and trace, but I don't know how to do that on mysql.
Mar
18
revised Special function slow in where but not in select
added 129 characters in body
Mar
18
comment Special function slow in where but not in select
See previous comment. And no, there's no nice way to express that in SQL, as it is a declarative language. It depends on the database engine.
Mar
18
comment Special function slow in where but not in select
SELECT * FROM (SELECT *,levenshtein(word,'Facbook') as dist FROM Words WHERE length(word) between 6 and 8) WHERE dist <= 1
Mar
18
comment Special function slow in where but not in select
Depends on the mysql optimizer. Try this: Is it faster?