6
votes
2answers
2k views

Slow ORDER BY with LIMIT

I have this query: SELECT * FROM location WHERE to_tsvector('simple',unaccent2("city")) @@ to_tsquery('simple',unaccent2('wroclaw')) order by displaycount I'm happy with it: "Sort ...
0
votes
1answer
57 views

2 Very similar queries return different number of result (one is null)

I want to find businesses near a point that contain a word that start with sea lat=-6.185068&long=106.773121&keyword=sea&distance=5000&startfrom=0&howmany=20&prominent=5 ...
1
vote
1answer
359 views

PG_bulkload utility postgresql need help

i am using COPY command to export the data to the out file with changes made to the data before export using option ST_asBinary() using command COPY ( SELECT st_asBinary(geom) FROM n_america ) TO ...
1
vote
1answer
331 views

`SELECT FOR UPDATE` in master-master mysql setup

I am benchmarking a database schema with a dozen workers processes and was wondering what a SELECT FOR UPDATE does in a master-master configuration. For the moment it seems to work, but I am either ...
1
vote
1answer
323 views

What storage engine should I use for this MySQL table?

I have a table where I store all the urls from my website, and I update them from time to time when a visitor enters. The primary key is the url itself varchar(255). I have set it to use InnoDB but ...
0
votes
2answers
945 views

How to move all non-unique and non-clustered indexes to a different filegroup?

I want to move all indexes to a separate hard drive. To that effect, I created a new filegroup, then added a new file to it (e.g. alter database foo add file(...) to FileGroup). Now I want to move ...
1
vote
1answer
183 views

Hard Drive Configuration for my setup

I have a fairly large transactional database (100GB) with a lot of users. The database access is solely via stored procs that make heavy use of temp tables, table variables, cursors and other fun ...
4
votes
1answer
3k views

SQL Azure: More Intermittent Timeouts

(also posted on MSDN forums http://social.msdn.microsoft.com/Forums/en-US/ssdsgetstarted/thread/79058c06-d3c5-4073-8571-c29d1f43f7ae) Hi guys, We have a set of 5 online auction systems running on ...
0
votes
1answer
1k views

Unable to reset the password of users in postgres

Evrytime i run, su - postgres it asks me for a password which I did not set. I simply installed the postgres. I have gone to the pg_hba.conf. # Database administrative login by Unix domain socket ...
1
vote
1answer
1k views

PDO count(*) does not work with LIMIT and OFFSET

here's my code it works normally, but this time i want to also count the records it gives. at the point of inserting COUNT(*) it will give NULL value to counted rows $query_list_records = ...
4
votes
1answer
496 views

hostname incorrect in sysprocesses

I'm running SQL Server 2000. While doing some profiling to trace activity, I noticed some hostnames showing up that were mysterious -- names that shouldn't exist on the network anymore. I got the MAC ...
6
votes
1answer
7k views

How to set Postgresql database to see date as “MDY” permanently

Ho can I set my database to see the 'date' as "MDY" without running: SET datestyle = "ISO, MDY"; every-time I'm trying to access it? I'm using Postgresql version 9.1, Ubuntu 12.04. My system ...
4
votes
2answers
15k views

Can I execute a prepared statement inside a MySQL SELECT?

I'm running a search query in MySQL to return items from a products and pricelist table. When a user does a search query I need to get products from the products table LEFT JOINED with all ...
1
vote
1answer
74 views

MySQL Replication - replication issues; procedures registered on master not giving same result on slave in MySQL 5.5.20

I am not able to replicate procedures registered in the Master to the Slave in MySQL 5.5.20 The Procedure is registered on the Master and can be seen on Slave. However, results fetched using the same ...
2
votes
1answer
134 views

MySQL REPLACE appears to corrupt data

For reference, this issue was posted on StackOverflow.com before coming here. In MySQL I am running multiple REPLACE queries to update hardcoded URLs in a database. Queries look like this: UPDATE ...
2
votes
2answers
666 views

Plan Cache Pages by Database in SQL Server 2008R2

Is it possible to get a break down of how many plan cache pages are being used per database? Perfmon seems to only have the total for the server, but maybe there is a DMV with more detail? Also, does ...
6
votes
2answers
424 views

Less restrictive query return less result due to simple removing one additional constraint

Look at this query SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude, 111151.29341326*SQRT(pow(-6.185-TB.Latitude,2)+pow(106.773-TB.Longitude,2)*0.98839228980165) AS Distance FROM ...
0
votes
1answer
2k views

How to create custom error messages in APEX?

I would like to replace standard ORA-xxxxx messages with custom ones in Oracle Application Express. For example instead of this: to have something like "You have left some fields blank, try again". ...
1
vote
1answer
57 views

delimited values in a column value

I have a table which have a * to * relationship with another table. Lets say the first table is T1 and the second table is T2. So I need to keep the T2 Pks linked with single row in T1. I really like ...
0
votes
2answers
1k views

How to set lower case for identifiers in Toad?

I'm using Toad to write queries agains an Oracle database. When I write table_name. I get an auto-suggestion menu with all the column names. But if I choose a column name, both the table name and ...
1
vote
3answers
74 views

How can I query to do this?

I have 2 tables, in feeds I have my feeds and (feeds.ID) is the primary key. second table is items. and feeds.ID is realted to items.feed_ID, so with a simple inner join i can extract the data of each ...
8
votes
1answer
37k views

How do I reset the postgres password for PostgreSQL on Windows?

I have installed PostgreSQL on my Windows machine for development. Now I haven't used it for a while, and have forgotten the password for user postgres (default admin user in PostgreSQL). How can I ...
1
vote
1answer
132 views

Storing all data in a single db versus multiple dbs in Mongodb

We have an app that is basically an ERP. Some of the resources in the ERP are satellite tracked. So the information related to the resource itself is minimal, but the satellite tracker produces data ...
3
votes
1answer
408 views

How does R-Tree outperform B-Tree for simple check if a point is within a rectangle

For things like find nearest neighbors, I can undertand that R=Tree can outperform B-Tree. R-Tree can kick out more obviously false points. However, for simple check of a point in a rectangle, it's ...
-2
votes
1answer
4k views

Why does MATCH (FullTextSearch) AGAINST ('*' IN BOOLEAN MODE) return nothing?

MATCH (FullTextSearch) AGAINST ('k*' IN BOOLEAN MODE) return something Namely anything with a word that start with k MATCH (FullTextSearch) AGAINST ('ku*' IN BOOLEAN MODE) return less MATCH ...
3
votes
2answers
5k views

How to create column in DB with default value random string

Can I create column in DB table (PostgreSQL) which have default value random string, and how ? If is not possible, please let me know that.
0
votes
1answer
195 views

What database do most mobile start ups use? [closed]

mongodb? MySQL? PostgreSQL? If someone would add pluses and minus, let me know. Why I ask: I am a mobile startup. I need a database that supports spatial indexes. The spatial index must be able to ...
1
vote
0answers
107 views

Why do we need myisam spatial extension anyway?

If just to determine whether a point is within a rectangle, What is wrong with this query? EXPLAIN SELECT DISTINCT TB.ID, TB.Latitude, TB.Longitude FROM TableBusiness AS TB WHERE ...
5
votes
2answers
2k views

Transaction Log and mirroring - looking for dumbest explanation possible

First of all I must admit that I struggle with concept of Transaction Log. I mean - I do understand that it is Log of all Transactions that happen on database, but when it comes to properly putting it ...
0
votes
1answer
305 views

Why number of matches in fulltext search get less when we put less strings?

SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('resta*' IN NATURAL LANGUAGE MODE) show 5 results SELECT * FROM tableauxiliary WHERE MATCH (FullTextSearch) AGAINST ('restaurant*' ...
0
votes
2answers
4k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...
5
votes
1answer
1k views

NESTING_TRANSACTION_FULL latch

Production SQL Server 2008 performance is far below expectations. Simple single-table queries sometimes are working for 5 seconds in the mid-day compared to 250ms after hours. We cannot find which ...
2
votes
4answers
1k views

best mysql field type for temperatures

I'm realising that this is the first time I've stored temperatures in a MySQL DB. What do think the best mysql field type for temperatures (c) is? Decimial (3,3) ?
3
votes
2answers
11k views

forcing Oracle to use hash join for a subquery

I have a query that looks like SELECT * FROM table0 WHERE id IN (SELECT id FROM table1 JOIN table2) Oracle is choosing to join table0 with the result of (table1 x table2) using nested loops and ...
1
vote
1answer
200 views

What is the SQL command to get 20 closest points from some center location

There are 1.6 million rows. Must not compute distance for all rows. Spatial index is available This is the table structure. The name of the table is businessauxiliary. So yes I used MyIsam, added ...
5
votes
2answers
667 views

SQL to select random mix of rows fairly

Here's my problem: I have a set of tables in a database populated with data from a client that contains product information. In addition to the basic product information, there is also information ...
0
votes
1answer
296 views

MySQL Partition based on month-year

Is there a way that I can create a month-year partition on the below table? Without having to define all the permuations of month/year combos ? CREATE TABLE visitor_visits ( id int(10) UNSIGNED ...
3
votes
2answers
9k views

How do I copy databases from Postgres 8.4 to Postgres 9.1?

My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the ...
3
votes
0answers
580 views

SQL Developer Data Modeller and Data Dictionary question

I'm not a DBA but I am a (mostly!) competent SQL report writer. Can I trouble you DBAs for some pro-knowledge, please? I can connect SQL Developer to Oracle XE's data dictionary without any problem, ...
0
votes
2answers
603 views

How to build dimensions from SCD tables

Having the foresight to begin collecting data from our production environment several years ago, I have a table with a following structure- Part Status Startdate Enddate I can write a relational ...
0
votes
2answers
80 views

Best practice for entry position indicators

My problem is, that I have no idea how I could cleanly make an index (not a real Table index, just an indicator) for each table row representing the entries position while each position must be unique ...
1
vote
1answer
2k views

MDX - Sum values greater than then a median calculated measure

I'm relatively new to SSAS / MDX. I have a cube with a calculated field which calculates the Median value for a range of prices. MEDIAN( [DimItems].[Dim Items Id].CurrentMember.Children, ...
2
votes
1answer
221 views

Indexing or changing one-to-many relationship

I have these two Microsoft SQL Server 2005 tables: ObjectLicenses (ObjectLicenseID [PK]; ObjectID; LicenseType; LicenseNumber) 1|1|A|000001 2|1|A|000002 3|1|B|000003 4|2|C|000004 Objects (ObjectID ...
2
votes
2answers
4k views

Encoding problems with sqlplus, Linux

I use sqlplus and all non-Latin characters appear as '?' when I run select queries. When I run insert or update queries with non-Latin characters I get question marks on a web page. select * from ...
2
votes
2answers
2k views

SQL Server 2008 linked server to an Access 2007 DB that is in a protected directory on the Network

We have a SQL Server 2008 database that we are attempting to link with an Access 2007 DB (the maintainer works down the hall and refuses to migrate to SQL Server). We have the password to the database ...
11
votes
1answer
6k views

MySQL transaction size - how big is too big?

I have an import process that runs every so often and I want it to be an 'all or nothing' kind of deal, aka: a transaction. There are many aspects, and the imports may yield anywhere between ...
6
votes
4answers
3k views

Recommended MySQL statistics for production environment

Other than usual system stats (i/o, ram usage, cpu, load, etc) I am currently collecting questions, qps, running queries and buffer pool hit. I find qps pretty useless since our production servers ...
6
votes
4answers
5k views

Passing in the table into a stored procedure

Is it possible to pass in the name of a table into a stored procedure? For example, suppose you have several views of the same table. They all have the exact same structure. You want a store ...
1
vote
1answer
846 views

Delete by primary key takes too long

What would make a query that deletes a row using the primary key of the table .. take too long ? This is a table with about 1.4M rows that gets .. 70%/30% read/write . I don't know where to look. ...
2
votes
3answers
8k views

Starting postgresql service and specifying data directory

I have recently installed pg 8.4 on Ubuntu 10.0.4 using apt-get install. It installed pg as a service which starts ehenever the machine starts. However I have created a db cluster in a specific ...

15 30 50 per page