I have a large database (180k+ rows and growing fast) of location data and am plotting them on a google map. For a given view port, I just want to serve up a sample of 100 applicable points. The database is therefore queried by lat/lng, but if I put an index on these rows the problem is that the sample of 100 points will be either at t...
If I used an Index column in a query which returns all the Rows, is it advantageous to use the Index column in the where clause ? For eg. select * from table1 where Salary > 1; If the salary of all the employees is greater than 1, is it advantageous to use the Index column in the where clause ? Are Indexed a big overhead while inserti...
I want to index a "compound word" like "New York" as a single term in Lucene not like "new", "york". In such a way that if someone searches for "new place", documents containing "new york" won't match. I think this is not the case for N-grams (actually NGramTokenizer), because I won't index just any n-gram, I want to index only some spe...
I have a mysql database that runs for some time now with many changes on it. Lately i looked over it and i noticed that in some cases i have doubled index on the same field. Some Indexes are missing, And mainly there is a huge mess in all the indexes. I wants to drop all indexes from a table. Later on i have a prepared script that will ...
I now understand that a clustered index contains all of the row data, not just the index fields. I'm trying to understand the implications of this in regards to fragmentation. Say we have a table like this: create table Files ( ID uniqueidentifier not null, Field1 nvarchar(300) null, Field2 nvarchar(300) null, Field3 nv...
Hi all. In my Python application I have been using sqlite3.Row as the row factory to index results by name for a while with no issues. Recently I moved my application to a new server (no code changes), and I discovered this method of indexing is now unexpectedly failing on the new server given quite a specific condition. I cannot see ...
After an unfortunate misadventure with MySQL, I finally gave up on using it. What I have? Large set of files in the following format: ID1: String String String String ID2: String String String String ID3: String String String String ID4: String String String String What I did? Used MySQL on a powerful machine to import everything ...
Hi, This query pops up in my slow query logs: SELECT COUNT(*) AS ordersCount, SUM(ItemsPrice + COALESCE(extrasPrice, 0.0)) AS totalValue, SUM(ItemsPrice) AS totalValue, SUM(std_delivery_charge) AS totalStdDeliveryCharge, SUM(extra_delivery_charge) AS totalExtraDeliveryCharge, this_.type ...
I have 2 entities. entity A will hold many entity Bs and order will matter. if i check the little box that says 'indexed' in xcode, how do i go about using that index, if i even can? ( i know that i CAN use it in some way: http://cocoawithlove.com/2008/03/testing-core-data-with-very-big.html but i am not so spiffy with Obj-c yet.) I h...
I have a table in a Mysql db that I want to use multicolumn indexing on. How can I do this in rails w/o using the mysql console? ...
Subquestioning "full-text search sql server 2005" Would the use of FTS (Full Text Search) be faster in comparison with searches using tsql LIKE on indexed column(s)? Why? And how much? ...
I am trying to prototype an indexing/search application which uses very volatile indexing data sources (forums, social networks etc), here are some of the performance requirements, Very fast turn-around time (by this I mean that any new data (such as a new message on a forum) should be available in the search results very soon (less th...
Hello all, long time lurker, first question! I am struggling to optimize this query, which selects the lowest priced items that match the chosen filters: SELECT product_info.*, MIN(product_all.sale_price) as sale_price, product_all.buy_link FROM product_info NATURAL JOIN (SELECT * FROM product_all WHERE product_all.date = '2010-09-30')...
If you are talking about btrees, I wouldn't imagine that the additional overhead of a non clustered index (not counting stuff like full text search or other kind of string indexing) is even measurable, except for an extremely high volume high write scenario. What kind of overhead are we actually talking about? Why would it be a bad ide...
Using MySQL and PHP; I'm trying to build an index that contains the averages from table_1 grouped by: type, name, hour, day, month, year So I need to know which combination's of values are in table_1 so I know what to put in my AVG() queries. What I want is to figure out all the different combination's that can be made when comparing ...
When should I use KEY, PRIMARY KEY, UNIQUE KEY and INDEX? ...
I have a large table with millions of records. Table `price` ------------ id product site value The table is brand new, and there are no indexes created. I then issued a request for new index creation with the following query: CREATE INDEX ix_price_site_product_value_id ON price (site, product, value, id); This took long long t...
What would be the best way to find the index of a specified character in a list containing multiple characters? ...
For all you who know, in Google Webmaster Tools one can submit a sitemap or **sitemap_inde**x file and then google will fetch it and crawl the website when it "has time to". I have searched for this but can't find an answer anywhere... In the interface of webmaster tools, there is a section for "sitemaps" which lists all sitemaps submi...
I'm having a problem getting indexes in MongoDB to work correctly when I'm indexing into embedded documents. The indexes work fine if the inner key is a simple string but because of my data format the inner keys often need to be URIs and this doesn't seem to work, looking at the Mongo log when I try and create the indexes it says the in...