Tag Info

Hot answers tagged

9

If I saw a data model where a table had 2.1 million rows, one of the columns had only 5 distinct values, and I knew that people wanted to get a listing of those distinct values on a somewhat regular basis, I would strongly suspect that the data model was missing a lookup/ dimension table. Rather than trying to tune your current query, I would bet that the ...


6

If Oracle only scans the index it cannot decide if there is a row with NULL in this column. So it makes a full table scan. A possible workaround: create a bitmap index on column4. This index contains the NULL columns, too. More details can be found in this post Why isn't oracle using an index for distinct query ?


4

I see a couple of issues. The biggest one is that PG is using a sequence scan on A when filtering A. I think you need a composite index on A.flag AND A.strvalue. If there is already an index available, PostgreSQL is choosing not to use it for some reason. This seems to be eating up 92% of your cost estimate and is likely what's making it run for so long. ...


3

Using partitioning is only going to help your query performance if the partitioning scheme is built serve your specific queries. Your going to have to review your query patterns and see how they are accessing the table in order to identify the best approach. The reason for this is you can only partition on a single column (the partitioning key) and this is ...


3

The sub query in the second query it's what's killing you that requires that the sub query needs to be run once per row that's returned by the record set. If you look at the plan it'll tell you how many times each operator is executed. I'll bet that you'll see a high number of executions on some operators of the second query. (I'm on my phone at the moment ...


3

The query to accomplish this would be of the form: SELECT t.UniqueKey FROM mytable t WHERE t.UniqueKey = ? AND t.timeStamp >= NOW() - INTERVAL 5 MINUTE AND t.timeStamp <= NOW() LIMIT 1 (This query assumes that the timeStamp column is defined as datatype TIMESTAMP.) The query will either return one or zero rows, which will indicate either ...


2

SUGGESTION #1 You have no meaningful indexes. That can have a detrimental effect on execution since the MySQL Query Optimizer has no help in searching. You should all the following indexes ALTER TABLE customers ADD INDEX bigindex (status,deadlead,purchased,last_updated); ALTER TABLE notes ADD INDEX timestamp_id_index (timestamp,id); This may help with ...


2

If you are trying to insert into a new table that has already been created, you can say: ;WITH Nbrs ( n ) AS ( SELECT CAST(1 as BIGINT) UNION ALL SELECT 1 + n FROM Nbrs WHERE n < 480000) INSERT dbo.ExistingTable(First,Second,Third,Fourth,Fifth,Sixth) ----^^^^^^ this line SELECT tblOrderRandomNumbers.[1] AS First, ...


2

Oracle by default does not cache query and function result, but both cache do exist. http://www.oracle.com/technetwork/articles/sql/11g-caching-pooling-088320.html The importance of these: there is more than caching blocks. Even if you process everything in memory, why process the same requests again and again, if you already know the answer? What you are ...


2

You want to use a TRANSLATIONS table, that looks something like: CREATE TABLE TRANSLATIONS ( translation_id INTEGER, language_id INTEGER, translation VARCHAR(255), PRIMARY KEY (translation_id, language_id) ); Then edit your current schema and/or code to reference the TRANSLATIONS table, rather than any hard-coded values (using ...


1

For this query: select distinct date from mybigtable; or its twin: select date from mybigtable group by 1; ... the whole table has to be read. Postgres is not going to use any index, except, possibly, a covering index that is substantially smaller than the table itself. Postgres Wiki on slow counting. Also, to be precise, that's not a count. If you ...


1

There is no "result cache". There is the database buffer cache. Oracle stores recently-accessed data blocks in memory so that it doesn't have to go to disk every time for hot blocks - those chunks of data which are most frequently accessed. Function and query results are not stored, only the data blocks which are in demand. See here: ...


1

I will add to the recommendations made by @RolandoMySQLDBA. It looks like notes.cid should be a foreign key related to customers.id. You might try replacing the SELECT DISTINCT sub query with an EXISTS sub query. The select distinct will look for all related notes. An exists may perform better since it only check for the existence of at least one note. ...


1

For the table mydb.mytable with UniqueKey and timeStamp, to see if the UniqueKey exists within the last 5 minutes, simply run this SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 5 MINUTE ); or SELECT COUNT(1) FROM mydb.mytable WHERE UniqueKey = ???? AND timeStamp >= ( NOW() - INTERVAL 300 SECOND ); ...


1

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3). This column shall be kept up-to-date either by your application code or by triggers, such as: CREATE TRIGGER ... BEFORE UPDATE ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... CREATE TRIGGER ... BEFORE INSERT ... ... SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3) ... ...



Only top voted, non community-wiki answers of a minimum length are eligible