Tag Info

New answers tagged

1

Looking at the original query SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE() GROUP BY DATE(timestamp) It appears that you are looking for counts for just today. In that instance, why use GROUP BY at all ? You should run SELECT COUNT(*) as raw_views ... FROM logs WHERE timestamp >= CURDATE(); OK, let's get a little more ...


1

First create a Calendar table (a table of dates) as described in this answer; then extend it by adding and populating Year, MonthName, DayOfMonth, and Tomorrow columns in that table and add a unique index on it by (Year, MonthName and DayOfMonth), and another unique index on it by the base column (Date) - the Primary Key. Now you can generate your reports ...


0

This might work: select sum(N), the_date from ( SELECT COUNT(*) as N, timestamp, DATE(timestamp) as the_date FROM logs WHERE timestamp >= CURDATE() group by timestamp ) as A GROUP BY the_date -- or: GROUP BY DATE(timestamp) Your first problem is MySQL, for many reasons, here because function(column) shouldn't preclude the use of an ...


0

You could create an additional column, date, which stores the function date(timestamp) on insertion. This won't make the group by extremely efficient, but it can avoid the temporary table. The second problem is the range + GROUP BY, which would make an index on (timestamp, date) useless (BTREE limitations). You can create just an index on (date) or better, ...


1

As @dezso suggested, the table with test data was not big enough for the query planner bother going to the index. After I imported a larger set of data, the query uses the index as expected.


2

Index reorganize does not touches statistics so there is no chance for causing recompilation. Since when index is rebuilt with full scan stats are also updated for the column this can trigger recompilation as statistics change.


3

You seem to expect that rows with NULL values are excluded from a B-tree index automatically, but that's not the case. Those are indexed as well and can be searched for. However, since: access_type ... is null in 90% of cases that's hardly useful in your case. Such common values hardly ever make sense in an index to begin with, be it NULL or any other ...


0

since primary_child (parent_id,child_id) is defined to be unique, then KEY child_primary (child_id,parent_id) IS unique, so you only have to define it as KEY, not UNIQUE KEY. [This is more helpful while inserting though] Having lots of NULL values is not recommended. It is not good for performance. If possible, add NOT NULL instead. However, this is highly ...


2

If you go into your SSMS options table and look at the scripting options for object explorer do you see foreign keys and primary keys enabled? That's pretty much the only thing I can think of that may be holding you back.


0

Firstly, I'm giving a +1 for the script - it's a pity that more people don't put sample data up - even if only on SQLFiddle and SQLFiddle is limited in size though! It's good to work with real data! Now, to the issue. I ran your database create script and I did the following. mysql> select count(*) from `call`; +----------+ | count(*) | +----------+ | ...


1

My bet is on creating the following indexes: (incoming, targetNumber, dateCreated) (sourceNumber, incoming, dateCreated) But you have to rewrite your query to do: select * from tesis.call oc --outgoing calls join tesis.call ic on oc.sourceNumber = ic.targetNumber and ic.dateCreated > oc.dateCreated and (ic.dateCreated - interval 30 ...


4

The book is assuming that PersonFriend is indexed on PersonID, but not on FriendID. It also seems to assume that Person indexes PersonID and Person independently. If this is the case, the first query comes back as {INDEX UNIQUE SCAN Person on Person => 'Bob' get back PersonID} {INDEX RANGE SCAN PersonFriend on PersonID => PersonIDs for Alice and Zack ...


1

As per followed practice you can rebuild index when fragmentation is >30 % and can reorganize when fragmentation is between 10 and 30 %. You should also include page_count value in your query. If page_count value( which is there in DMV sys.dm_db_index_physical_stats) is <1000 you don't need to rebuild or reorganize such indexes even though they show large ...


0

Use BST where returning ordered records by the indexed column(s) is a typical use case. Use HT otherwise.


1

You seem to be thinking of column-based storage systems (or you may come from that background). MySQL (InnoDB, in particular) stores its rows clustered around its primary key, and thay, and any other secondary indexes are stored in B+Tree structures. So, if you store the text in varchar(64) and index it wholly, it will take up to -the storage is in fact ...


1

Lets answer your question as you asked create clustered index ix_index1 on table1(col1) create clustered index ix_index2 on table1(col2) You are creating a clustered index(CI) but as soon as you create CI on col1 you cannnot do it on col2 as on a table you can have only one CI. A CI is physically a table arranged in particular order. Since a table ...


5

If you need columns in the output that aren't covered by the index, the optimizer has to make a choice: Perform a table / clustered index scan (therefore all columns are there) Perform a seek, then perform lookups to retrieve the columns not covered Which way it will choose depends on a variety of things, including how narrow the index is, how many rows ...


1

This can be achieved by using a unique index on the combination of counter and the month of date_booked. Something like this: create unique index idx_tbl on tbl (counter, to_char(date_booked, 'yyyymm')); However the above will fail because to_char() is not an "immutable" function and thus cannot be used in an index (even if we know that the above is ...


0

Having those other fields in the index is just making it worse if you're always using select *. Try creating index just for CustomerID. That might help, but it depends on several things like how many IDs there is in the in list and how many rows per CustomerID there is in the table.


1

I am assuming that you want the Oracle optimizer to pick tableb as the driver table and for each row in tableb, have it run an index scan for tablea (Since tablea has 3GB of data). Your indexes start with col4, so I am guessing you are hoping that one of them will be used by the optimizer. One thing to point out is that in your WHERE clause you have both: ...


0

Based on the plans I would just drop the index on the #testing2 table as it's not doing anything for you. With more data is might, but your sample tables are so small that SQL won't use it.


1

Here are my notices: Make sure that creator exists only in table1, or add [... WHERE table1.creator=..] It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned ...


2

Matching index After re-reading your question I realized you are not running Amazon Redshift, but Amazon RDS, which seems to be running unsullied Postgres, at least according to the documentation: Amazon RDS supports DB instances running several versions of PostgreSQL. Currently we support PostgreSQL versions 9.3.1, 9.3.2, and 9.3.3. This would mean ...


1

From the documentation: Full table scans are cheaper than index range scans when accessing a large fraction of the blocks in a table. Full table scans can use larger I/O calls, and making fewer large I/O calls is cheaper than making many smaller calls I suggest reading that entire page and then asking a more specific question if the optimizer is ...


1

This may be about the selectivity of your predicates. If a query uses an index it suffers the overhead of reading from disk the pages which constitute the index itself. If instead it performs a table scan it has the overhead of retrieving data it will ignore. The relative cost of these two options will depend on the selectivity of your index, how the ...


1

Take a look at http://ola.hallengren.com/ He has a great maintenanceplan which is free to use. I am using it for every project.


1

The reason that your table is growing while only making updates is that PostgreSQL is that "tuples that are deleted or obsoleted by an update are not physically removed from their table; they remain present until a VACUUM is done." Of course, while you are updating 1000 rows per second your updates are in contention with VACUUM FULL and perhaps REINDEX. ...


1

Is there any way to calculate indexes multithreaded for single inserts? Unfortunately, no. PostgreSQL backends are single-threaded, and a single connection can use only one backend. There's work to enhance this, but it's a seriously difficult and big job because of PostgreSQL's process-based architecture. The only real option is to split the insert ...


2

Back in the day we would bulk load our data in this way: Drop indexes Load data in the order for which the clustered index would be built (i.e., you export the data in a precise way) After the load is completed, create the clustered index Next, create any additional non-clustered indexes Miller time (this was before I could afford decent beer) That ...


1

If you are doing a large, load operation it is faster to utilize the TokuMX bulk loader, as it only requires one pass over the data to create both the primary key index and any secondary indexes. More information is available in the documentation at http://docs.tokutek.com/tokumx/tokumx-commands.html#tokumx-new-commands-loader



Top 50 recent answers are included