I have a composite index based on 3 columns, two of which are constrained in my query and the 3rd is in order by clause yet mysql doesn't use index for sorting. explain select * from videos where public_private='public' and approved='yes' order by number_of_views desc; +----+-------------+--------+------+------------------------------...
how does the computer retrive a particular entry in the MFT table for a file or directory? I read through many documents which describe the structure of NTFS and MFT, but i fail to understand say i have a file in E:\documents\test.txt, how can i identify it's entry in the MFT index. Is it sequential? ...
Consider a matrix M and a set of subscripts stored in columns I and J. I need to access the elements designated by I & J without converting them to linear indices (i.e. using sub2ind). E.g. M = [1 2 3;4 5 6;7 8 9]; I = [1 1 1]; J = [1 2 3]; VALS = [1 2 3]; Also, doing the following is not feasible since I & J are huge : VALS = diag(...
I have a certain object type that is stored in a database. This type now gets additional information associated with it which will differ in structure among instances. Although for groups of instances the information will be identically structured, the structure will only be known at runtime and will change over time. I decided to just ...
Is this true that Update SQL Query is slow because of Clustered index?????? ...
In a data warehouse, are there disadvantages to creating clustered indexes on fact tables? (most of the time, it will be on the datetime column) Would you answer yes or no "by default..."? If I shouldn't create clustered indexes by default, then why? (I know the pros of clustered indexes, but what are some cons?) References http://...
We have a SQL table that is populated with events from our website (mostly error logging and the like.) The table has several text fields that contain all of the information about the type of event, and a date/time field that shows when the event was logged. The table is fairly large and grows by around 10-100 records per day. Obvious...
I have a process that is performing badly due to full table scans on a particular table. I have computed statistics, rebuilt existing indices and tried adding new indices for this table but this hasn't solved the issue. Can an implicit type conversion stop an index being used? What about other reasons? The cost of a full table scan is a...
We have a Transactional Media table that contains a clustered primary key of 4 columns. RevenueCentreID, DateOfSale, MediaItemID and SaleTypeID. It also contains a quantity column and amount column. Most our queries will be selecting Revenue Centres for a date range for a certain media item. We hardly ever use the SaleTypeID. Would i...
I know just enough about SQL tuning to get myself in trouble. Today I was doing EXPLAIN plan on a query and I noticed it was not using indexes when I thought it probably should. Well, I kept doing EXPLAIN on simpler and simpler (and more indexable in my mind) queries, until I did EXPLAIN on select count(*) from table_name I thought ...
This a follow-up to a previous question. How can I optimize this query so that it does not perform a full table scan? SELECT Employee.name FROM Employee WHERE Employee.id <> 1000; . explain SELECT Employee.name FROM Employee WHERE Employee.id <> 1000; +----+-------------+-------------+------+---------------+------+---------+------+...
I need to search a large set of matlab (.m) files, and I can't find a way to do it in Google Desktop. I haven't seen any source code indexer that supports matlab either. What options do I have? ...
For example when you Google "stackoverflow" the first result links to http://stackoverflow.com with a description, "A language-independent collaboratively edited question and answer site for programmers." How is Stack Overflow or any other site working to set that description? I am about to put a website online and would like to get ...
I'm generating some statistics for some english-language text and I would like to skip uninteresting words such as "a" and "the". Where can I find some lists of these uninteresting words? Is a list of these words the same as a list of the most frequently used words in English? update: these are apparently called "stop words" and not ...
I'm thinking of a way of creating a local backup for the pages I will be linking to from my site. This would be text-only, similar to Google's 'Copy' feature on the search pages. The idea is to be sure that the pages I would reference to, or cite from, do not dissapear from the Web in the near future. I know I could just keep local copie...
I have a class like this class MyClass { int Identifier; int Context; int Data; } and I plan to store it in a STL container like vector<MyClass> myVector; but I will need to access it either by the extenal Index (using myVector[index]); and the combination of Identifier and Context which in this case I would perform a...
I used to think that Oracle does not index a row when one of the column values is null. Some simple experimentation shows this to be not the case. I was able to run some queries unexpectedly accessing only indexes even though some columns were nullable (which of course was a pleasant surprise). A Google search led to some blogs with co...
I have two tables like this. Table A A_ID ( Primary Key) A1 A2 Table B B_ID ( Primary Key) B1 B2 A_ID ( foreign key but not enforced in database, not unique ) Although by default SQL server creates clustered indexes on B_ID, I have lot of select queries on B, which depend on A_ID something like this SELECT * FROM B WHERE B.A...
I have a limited exposure to DB and have only used DB as an application programmer. I want to know about Clustered and Non clustered indexes. I googled and what I found was : A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one cluster...
I have a table in a SQL 2005 database which is brand new. As part of our application deployment we load the table with about 2.6M rows. Once that is done, the indexes on the table are all rebuilt. Then the users are let into the system and queries against that table time out. I can then rebuild the indexes (using the same exact script th...