11,197 reputation
11843
bio website
location Munich
age
visits member for 3 years, 2 months
seen 29 mins ago

Jul
27
comment MySQL CHAR(500)?
"and VARCHAR is a less efficient storage mechanism" - how do you know that? Did you do any performance testing?
Jul
27
comment DBMS database management system
This question appears to be off-topic because it does not show any research effort
Jul
27
comment Why would SELECT * be magnitudes faster than SELECT foo?
The first statements most probably uses the primary key index on id to find the first row. The second one needs to sort the complete result on the (un indexed) val column.
Jul
27
comment get average of rating using group by and then finally overall rating . Example below
Which DBMS are you using? Postgres? Oracle? DB2?
Jul
26
comment Search a alphanumeric value from varchar column in DB2
Cross post: stackoverflow.com/q/24969813/330315
Jul
26
comment Auto increment char varying column prefixed with some characters
"can't we just make any varchar as auto-increament" - that is not the correct question. What you are trying to achieve simply doesn't make sense.
Jul
26
comment How to generate the E-R Diagram of Wordpress using mySQL Workbench
Most probably Wordpress isn't using any foreign keys (as so many off-the-shelf products...)
Jul
26
comment Auto increment char varying column prefixed with some characters
Why would you want to do that? If every key value has the prefix PAT there is no sense in storing it. Create a serial column and a view that formats the number as desired. Additionally: the value of a primary key has no meaning whatsoever. You should not care about the values in that column, the only thing you need to care about is that they are unique.
Jul
25
comment Postgres vs Mongodb, Suggestions to improve mongodb performance
Relational databases have been designed for ages to do efficient aggregation. My understanding is that aggregation (of mass data) is not the sweet spot of NoSQL databases - they are intended and designed for other areas. See for example this blog posting: tapoueh.org/blog/2014/02/…
Jul
25
comment Index on 'GROUP BY' with functions
MySQL doesn't support indexes on expressions or functions
Jul
25
comment Why is my composite index not used for this table?
Half a millisecond doesn't seem to be slow though...
Jul
21
comment Demo DB for book on PostgreSQL
The book covers Postgres 7.0 which has been out of support for a very long time. Postgres has changed very much in the last 14 years. Make sure you check out the manual for the current version (9.3 as of July, 21st) as well. The manual also contains a good tutorial on SQL. Another option is postgresqltutorial.com which uses the DVD rental database for its examples: postgresqltutorial.com/postgresql-sample-database
Jul
20
comment Very large btree index with few rows (openstreetmap gis data)
@raph: I didn't mean that the solution was wrong, just that a where access_type is null condition is not required in the query (even if that is used in the index), as long as you compare the column to a non-null value: where access_type = 'foo'. The planner will still make use of the index
Jul
20
comment How do I go about easy table input without creating an application?
Another option would be to use a (before insert) trigger to lookup the corresponding ID during automatically
Jul
20
comment Very large btree index with few rows (openstreetmap gis data)
Just for completeness: for a partial index using is not null you don't have to use is no null explicitly in the query. If you compare against a value where access_type = 'foo', the optimizer is smart enough to recognize that
Jul
19
comment duplicate key violates unique constraint when using multi-threading
I think the best way to do it is to simply catch the exception and then process with the next row. Nothing you can implement in the code can be more efficient in checking for duplicates than the database.
Jul
18
comment Auto refreshing a query
I understand that, but still you are storing data that can easily (and without any performance penalty) be derived from the existing data: select mark_1 + mark_2 + mark_3 as t_mark from test_1 won't be any slower than select t_mark from test_1
Jul
18
comment Auto refreshing a query
Why would you want to do that? It's usually not a good idea to store data that can be derived from other data. Create a view with that formula and you'll always have up-to-date information. If you really insist on duplicating data, look into triggers
Jul
18
comment Btree index and Bit Map Index
The manual can't be clearer: your options are UNIQUE, FULLTEXT, SPATIAL for the index type. No mentioning of the keyword bitmap.
Jul
16
comment Primary key with randomly varying increments (so it cannot be guessed easily)
@frlan: there is an additional module that supplies functions to generate UUIDs: postgresql.org/docs/current/static/uuid-ossp.html