A question of particular interest about python for loops. Engineering programs often require values at previous or future indexes, such as: for i in range(0,n): value = 0.3*list[i-1] + 0.5*list[i] + 0.2*list[i+1] etc... However I rather like the nice clean python syntax: for item in list: #Do stuff with item in list or for...
CREATE INDEX alias_pub_idx2 ON info.palias USING btree (publisher_id, player_id, pub_player_id); CREATE INDEX alias_pub_idx3 ON info.palias USING btree (player_id); The first includes the three columns; the latter includes only the one. I'm thinking they are redundant- that the first btree index is sufficient, but I'm not ...
I am really interested on how mysql indexes work that it could not scan the whole table to give us results? It's off-topic, I know, but if there is someone who could explain me that picturesquely I would be very very thankful. Thanks. ...
I have some MySQL queries with conditions like where field1=val1 or field2=val2 and some like where fieldx=valx and fieldy=valy and (field1=val1 or field2=val2) How can I optimize these queries by creating indexes? My intuition is to create separate indexes for field1 and field2 for first query as it is an OR, so a composite index ...
Our system's automated database migration process involves running .sql scripts containing new table definitions and their accompanying indexes. I require the ability to create these tables and indexes only if they don't already exist. Tables are taken care of by using IF NOT EXISTS but no such syntax exists when creating indexes. I've...
Hello, Do the clustered and non-clustered indexes both work on B-Tree? I read that clustered indexes affect the way how the data is physically stored in table whereas with non-clustered indexes a separate copy of the column is created and that is stored in sorted order. Also, Sql Server creates clustered indexes on primary key by defaul...
Hey guys, I have a question about Sphinx. I use Sphinx to index the full-text searches for my sites, and it works like a dream. At this point in time it takes about 30 minutes to create the indexes for all my databases. This is fine as I only run the indexing script once every hour. But the databases are getting bigger quickly, and soon...
I have a App Engine/Python/Django application which has grown and been modified over the past year and currently has 175 indexes. The problem is that I have not been thourough in cleaning up/removing indexes that are no longer needed. Now, I am not sure which indexes are active and which are essentially dead, but I am guessing that abou...
Hi, For arguments sake, lets say it's for SQL 2005/8. I understand that when you place indexes on a table to tune SELECT statements, these indexes need maintaining during INSERT / UPDATE / DELETE actions. My main question is this: When will SQL Server maintain a table's indexes? I have many subsequent questions: I naively assume th...
I've got a modest table of about 10k rows that is often sorted by a column called 'name'. So, I added an index on this column. Now selects on it are fast: EXPLAIN ANALYZE SELECT * FROM crm_venue ORDER BY name ASC LIMIT 10; ...query plan... Limit (cost=0.00..1.22 rows=10 width=154) (actual time=0.029..0.065 rows=10 loops=1) -> In...
I'm creating a .exe in C# to import data from Excel and insert into my DataBase. This Excel file has in each column the name and the values below and for each Table in DB it has a sub table(those that are changed on the bottom of the page in Excel) with the table name. It's working fine, but one of the tables in my DataBase has a forei...
Is there going to be any real benefit to me putting indexes onto date fields that are going to be mainly used in queries using stuff like. dateField < 'var' And 'var' BETWEEN dateField1 AND dateField2 The searches get done a lot but I am never doing a direct comparison "=" on them. ...
One of my co workers is under the impression that when adding an index to a table in SQL Server 2008 that the PK's index is added to that index as well. Therefore if you are using a wider primary key then that key will also be included in the new index vastly increasing the disk space used above and beyond the penalty already paid for th...
A colleague and I are new to Oracle and are analyzing indexes on a table. This is a legacy and indexes currently exist on the table Mytable * ID (primary key) * partId (Id column in part) * partNum (partNum column in part...partNum can have more than one partId) * description (description of partNum...can be different for each pa...
For example, I'm querying on a field I know will be unique and is indexed such as a primary key. Hence I know this query will only return 1 row (even without the LIMIT 1) SELECT * FROM tablename WHERE tablename.id=123 LIMIT 1 or only update 1 row UPDATE tablename SET somefield='somevalue' WHERE tablename.id=123 LIMIT 1 Would adding t...
I have a MySQL query: SELECT DISTINCT c.id, c.company_name, cd.firstname, cd.surname, cis.description AS industry_sector FROM (clients c) JOIN clients_details cd ON c.id = cd.client_id LEFT JOIN clients_industry_sectors cis ON cd.industry_sector_id = cis.id WHER...
I have a metric table that I expect to be very large. It has a polymorphic association so that it can belongs_to other models that want to record some metric. I typically index association columns like this to speed up association loading. I've heard people talking about joint-indexing this association. This looks like: add_index :c...
Hi I am having the same problem as the OP here: http://stackoverflow.com/q/2305534/485483 My CPU load goes up to 100+ thanks to a WP query like this one: # Query_time: 8 Lock_time: 0 Rows_sent: 9 Rows_examined: 1705 SELECT SQL_CALC_FOUND_ROWS wordpress_posts.* FROM wordpress_posts WHERE 1=1 AND wordpress_posts.post_type = 'pos...
This is a general question on doing pagination with models containing multiple entity indexes. This is easier with an example so let us consider the example that Brett Slatkin provided in his video (http://www.google.com/events/io/2009/sessions/BuildingScalableComplexApps.html). You have your Message model (I have ignored the MessageIn...
I don't really care if it's NoSQL or SQL based - as long as it uses int indexes (and stores them in RAM for fast searching) so I can find my data with simple queries based on criteria like user_id, lat, status, or other common int fields. The actual records can be stored on disk. My first guess would be SQLite - but it moves slowly when...