The Setup What I am about to describe is run on the following hardware: Disk: 6x 2TB HDD in RAID5 (w/ 1 redundant drive) CPU: Intel Xeon E5-2640 @ 2.4 GHz, 6 Cores RAM: 64 GB SQL Server Version: SQL Server 2016 Developer Both SQL Server Management Studio (SSMS) and the sql server instance are running on this …
Tag: indexing
Suitable Index for improving Top N recent queries with tags
I am designing the schema of a database in PostgreSQL which would contain posts like Stack Overflow with tags. Users would search recent posts by different tags. From my research, I found that …
Nonclustered indexes will partially cover the select query
I having a non-clustered indexes created in my SQL database server with Include keyword. Please find my non-clustered index created for FACTORS table. if am having a SELECT query like below will my indcxes FACTORS_BKEY_PNO_IDX cover my select query. ( i mean will this index will help my below select query to …
MySQL GROUP BY slows down query x1000 times
I’m struggling with setting up proper, effective index for my Django application which uses MySQL database. The problem is about article table which for now has a little more than 1 million rows and querying isn’t as fast as we want. Article table structure looks more or less like below: After man…
Indexing (x 4 OR y 5)
I am searching for an index to make the following quick: The query is a delete and reads: so it deletes all rows that do not match a few hundred criteria on t1 and t2. explain gives a sequential scan and rearranges the query: Can I create an index that will avoid that full scan? Thanks in advance! Answer Alth…
Using two single-column indexes in where and orderby clause
I have googled a lot and couldn’t find a clear answer to my question assume we have this query SELECT * WHERE user_id = x ORDER BY date_created If we have a single column index on user_id and another …
Determining what index to create given a query?
Given a SQL query: How do you determine what index to write to improve the performance of the query? (Assuming every value to the right of the equal is calculated at runtime) Is there a built in command SQL command to suggest the proper index? To me, it gets confusing when there’s multiple JOINS that us…
Is there a way to use MySQL fulltext to search related tables?
I have a table called persons which contains data about, well, people. It also contains foreign keys to another table. I’d like to make a fulltext index that is able to search the related tables for …
MySql improve GROUP BY ORDER BY query with index
I have a table like this: the goal is to query how many products have been sold per day, so the query is: The output is: Ok. Now let us suppose we have a scenario with several records and this query has to be run several times per day, we need to care about performance now in order to retrive
Does DB2 implement multi-table indexes?
I’m afraid this functionality may not exist [yet]. I wanted to use an index that spans multiple tables in DB2. I know Oracle and SQL server implement them (with more or less options) and that …