Tag Info

New 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 ?


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 ...


0

Try to get rid of function calls in your where condition. Most of the time it'll result in a full table scan. If you can't avoid look if you can create a function index.


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 ...


0

The answer relies on what kind of data you store and how. I would never suggest making separate database. Depending on the data you can either: Create separate rows in same tables and add "language" column to specify which language the content is in. Create separate table per langauge to completely separate content. Which way to go depends on your data, ...


0

It's not clear what you want but this may be it: SELECT Name, SUM(CASE WHEN Used IS NULL THEN ItemQty ELSE 0 END) AS QtyNotUsed FROM ContainersReceived GROUP BY Name ; To be efficient, add an index on (Name, Used, QtyNotUsed) If you have another index, on (Used, Name, QtyNotUsed), this query will be ...


0

You can add a new column to replies, call it position, and fill it with consecutive numbers of replies per thread (the position of the reply in the thread). For example id | thread_id | text | position 1 | 1 | .... | 1 2 | 2 | .... | 1 3 | 1 | .... | 2 4 | 1 | .... | 3 5 | 2 | .... | 2 6 | 3 | .... | 1 ...


0

Look at the query SELECT * FROM replies WHERE thread_id = 1234 ORDER BY id ASC LIMIT 125400,10 /* whoops */ My guess is you are probably forcing MySQL to gather the records with thread_id 1234 and then sorting the rows in id order. You need to give MySQL more help. SUGGESTION #1 : Use a Better Index I would suggest changing the thread_id index as ...


0

Table partitioning may improve performance if you are able to work within the limits of how the partition works. See the description at: http://technet.microsoft.com/en-us/library/ms177411(v=sql.105).aspx However, partitioning can also make your server run slower if your partitions are not set up "just so" and your queries cannot remain within a single ...


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 ...


0

Read this on the partitioning -- SQL Server- Table partitions on SSD. As far as #2, you're table will be fragmented if you design it this way. You should switch places of the columns. Make DateTime the first column, this way the new rows will be added at the bottom, instead of finding room for every Unit_Id everyday -- lots of page splits. Then you can ...


0

WHERE STypeID IN (SELECT Value FROM LOVs WHERE Key LIKE 'SupportedTypeID') Quit using the IN clause, and simply JOIN your tables. Use AND in your ON clause to filter the joined table's rows down to the ones you're interested in. INNER JOIN LOVs ON LOVs.Value = STypeID AND LOVs.Key = SupportedTypeID Also, for your performance tuning, use SQL Server ...


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 ...


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. ...


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 ...


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: ...


2

Looks like you want your current date to be before or on last show date. If that is the case try following HAVING clause. HAVING MAX(CAST(CONCAT(shows.show_date, ' ', shows.show_time) AS DATETIME) >= Now()


0

I guess you need to order your result by the date if matches with current date then displayed at first and then all dates should come in descending order.For this you can use Field() function ORDER BY FIELD(`shows.show_date`,CURRENT_DATE()) DESC


0

No experience from MySQL, but in other systems the next idea works WHERE shows.show_date >=DATEADD(NOW(),1) it should compare show_date to now + 1 day (see Mysql page) If you want just compare dates use ( if you are interested shows tomorrow not shows 24 hours later) WHERE DATE(shows.show_date) >= DATEADD(DATE(NOW()),1)


0

Nothing suspicious in the plan itself, though I do wonder what your actual query looks like. If KONTRAHENCI.kt_nazwa has an index and there also is an index for the field in OFERTY that links to the primary key field of KONTRAHENCI, then a better plan might be something like: PLAN JOIN (JOIN (KL INDEX (KT_NAZWA_INDEX), OFERTY INDEX (LINK_FIELD_KONTRAHENCI... ...


0

There isn't much out there for MySQL except the following: EXPLAIN EXTENDED followed by SHOW WARNINGS SHOW PROFILES (Older Releases of MySQL) MySQL PERFORMANCE_SCHEMA Read these carefully, see what you think ...


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. ...


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, ...


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 ...


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 ); ...



Top 50 recent answers are included