Hi, I don't know much about database optimization, but I'm trying to understand this case. Say I have the following table: cities =========== state_id integer name varchar(32) slug varchar(32) Now, say I want to perform queries like this: SELECT * FROM cities WHERE state_id = 123 AND slug = 'some_city' SELECT * FROM cities WHERE st...
Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query. select p.ProductId, p.Name, c.CategoryId, c.Name AS Category from Products p inner join ProductCategories c on p.CategoryId = c.CategoryId where c.CategoryId = 1; When I create execution plan, table ProductCateg...
I've got a largish (~1.5M records) table that holds text strings of varying length for which I run queries against looking for matches: CREATE TABLE IF NOT EXISTS `shingles` ( `id` bigint(20) NOT NULL auto_increment, `TS` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP, `shingle` varchar(255) NOT NULL, `...
This is puzzling me - in SQL Server 2005/2008, I can specify the default file group which is then used for all data tables. I can also specify another filegroup for all BLOB type fields. But I cannot figure out if and how I can specify a default filegroup for indices..... My setup usually is: * primary filegroup - nothing but the syste...
PHPWiki has a 5 second slow query each time you save a page edit. The query caught often in the "mysql-slow.log" is: INSERT INTO wikiscore SELECT w1.topage, COUNT(*) FROM wikilinks AS w1, wikilinks AS w2 WHERE w2.topage=w1.frompage GROUP BY w1.topage; The current indexes are as follows: table "wikilinks" has a primary index on "...
Our web application basically dynamically generates tables and relations. It also generate indexes on the basic level. We are looking for a MySQL profiler that will be able to suggest indexes. We have come across this two profilers : MYSQL JET PROFILER : will not tell use what index or covering index will do the job. ROT : will not ...
I have a MySQL table holding lots of records that i want to give the user access to. I don't want to dump the entire table to the page so i need to break it up into 25 records at a time, so i need a page index. You have probably seen these on other pages, they kind of look like this at the base of the page: < 1 2 3 4 5 6 7 8 9 > For ex...
If I want to specify some nonclustered indices in a hibernate mapping as follows: CREATE NONCLUSTERED INDEX [Index_name] ON [dbo].[Individual] ( [ID] ASC, [IsActive] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] what's the best way to do it for multiple tables that a...
How many records should there be before I consider indexing my sql tables? ...
Hi! I'm building a index which is just several sets of ordered 32 bit integers stored continuously in a binary file. The problem is that this file grows pretty large. I've been thinking of adding some compressions scheme but that's a bit out of my expertise. So I'm wondering, what compression algorithm would work best in this case? Also...
If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them? I believe that when you set a primary key on one column, you have it indexed by it; is it the same with multiple column PKs? Thanks ...
I have a large set off files (hdf) that I need to enable search for. For Java I would use Lucene for this, as it's a file and document indexing engine. I don't know what the python equivalent would be though. Can anyone recommend which library I should use for indexing a large collection of files for fast search? Or is the prefered way ...
I'm encountering a very strange problem concerning what appears to be a corrupt index of some kind. Not corrupt in the sense that dbcc checkdb will pick it up, but corrupt in the sense that it has rows that it shouldn't have. I have two tables, TableA and TableB. For the purposes of my application, some rows are considered functionally ...
I have an user scalar-valued function which takes NVARCHAR(MAX) and returns NVARCHAR(MAX). To increase perfomance of GROUP BY command utilizing this function I wanted to create an index also based on it. But documentation says what CREATE INDEX command only takes columns not expressions, so this is illegal: CREATE INDEX an_index ON a_ta...
Hi All, I have a MySQL indexing question for you guys. I've got a very large table (~100Million Records) in MySQL that contains information about files. Most of the Queries I do on it involve substring operations on the file path column. Here's the table ddl: CREATE TABLE `filesystem_data`.`$tablename` ( `file_id` INT( 14 ) NOT...
Suppose I have two columns, keywords and content. I have a fulltext index across both. I want a row with foo in the keywords to have more relevance than a row with foo in the content. What do I need to do to cause MySQL to weight the matches in keywords higher than those in content? I'm using the "match against" syntax. SOLUTION: Wa...
I've got a multidimensional array: foo = [["a","b","c"], ["d", "e", "f"], ["g", "h", "i"]] Now I need to ruturn the "coordinates" of "f", which should be (1,2). How can I do this without knowing which "row" "f" is in? I've been trying to use the index method, but this only works when I tell it where to look, i.e. foo[1].index("f") #...
Hi guys, I know PRIMARY key is to have unique value, what about INDEX? What is the function of a table column with INDEX? ...
I know that it's more performant to use '' delimited strings rather than ""... but I was wondering if there's any performance improvemente doing this $a = array( 'table' => 'myTable', 'order' => 'myOrder' ); $table = $a['table'] instead of $a = array( table => 'myTable', order => 'myOrder' ); $table = $a[table] I guess so, bu...
Hi, I am having a very small tables with at most 5 records that holds some labels. I am using Postgres. The structure is as follows: id - smallint label - varchar(100) The table will be used mainly to reference the rows from other tables. The question is if it's really necessary to have a primary key on id or to have just an index on ...