Tagged Questions
0
votes
0answers
17 views
How much time is considered 'Ideal' for a SELECT query?
This is my first experience in developing for the masses, and I wanna know how much time is considered ideal for running a select query and returning the data to the client?
I've seen simple queries ...
0
votes
0answers
8 views
Get turno.tipo, maquina.patente and operador.nombre and status, complex query
I've this DB design I'm trying to build a query where I should get turno.tipo, maquina.patente, operador.nombre and detalle_emisores.revisado` but it's a bit complex to my knowledge. I started this:
...
3
votes
1answer
108 views
Algorithm for finding the longest prefix
I have two tables.
First one is a table with prefixes
code name price
343 ek1 10
3435 nt 4
3432 ek2 2
Second is call records with phone numbers
number time
834353212 10
...
0
votes
0answers
93 views
how to find similar word with more similarities
how to Find words with length less than or equal...
declare @inp nvarchar(max),@data nvarchar(max)
set @inp='You can dance, you can jive, having .... jove... jve, ...'
set @data = 'jeve'
...
0
votes
1answer
44 views
MySQL stored routine performance while using PREPARE
Instead of maintaining stored routines for each database in my current environment I have decided to create separate database just for stored routines storage. Mainly I am using them for reporting. ...
0
votes
1answer
21 views
Simple query not using indexes
My query is not using indexes.
It is a simple query.
Can someone explain why that's the case?
The query:
select TIME_FORMAT(start_time,'%H:%i') start_time,
TIME_FORMAT(end_time,'%H:%i') ...
3
votes
1answer
107 views
How does order of index affect performance in SQL Server?
I am wondering how does order of index affect performance (ASC, DESC) ? If it affects, why ? And which order should I choose ?
0
votes
0answers
22 views
Identical Postgres index scan taking 5x longer on server
I have an intermediate table for managing a many-to-many relation between tables called Expert and Subject:
Column | Type | Modifiers
...
2
votes
1answer
40 views
What is the algorithmic complexity of a PosgtreSQL greater than or lesser than index query (compared to equality)?
Assuming there is an index idx_int on an int_col column, what is the algorithmic complexity of a query like this?
SELECT id FROM table
WHERE table.int_col > 1;
I'm specifically interested in ...
0
votes
2answers
168 views
optimize big sql query
My query has to return a statistics for example for march containing productname and price and its sidedishes (1:n relation) with the right price of each sidedish and the right tax for each sidedish ...
6
votes
1answer
195 views
Landed as BI, but databases are a big WTF, what to do?
Maybe a duplicate, but I believe my case is a bit different. From one of the answers I got to this post on SQL Server Central that also comes handy too but is not quite the same scenario: 9 Things to ...
1
vote
1answer
92 views
SQL Server 2008 search joining query
I have two table as one is the message table and another one is messageUser table.
Now i need to check before insert a new row.
for example,
M_MessageMessageId=========
...
0
votes
1answer
86 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
0
votes
1answer
82 views
Looking for performance improvements for this query
This query is taking a long time to execute. We are expecting results in 0 sec, if possible.
Please help me to do any other following:
Rewrite the query
Suggest any indexes
Any other optimization ...
4
votes
1answer
64 views
Covering Index Question
I have the following query
Select Pt.PRODID, PT.INVENTREFID, Inv.ItemName, PT.ItemID, Configid, Pt.QTYSCHED,
PT.DLVDATE, Pt.CREATEDDATETIME, pt.SCHEDEND,
CASE Left(PT.INVENTREFID, 3)
WHEN ...
0
votes
2answers
183 views
Large time difference between two almost identical simple queries…why?
I'm trying to understand why is there so much difference in execution time and CPU usage between two simple queries that only differ in a computation. The queries are the following:
SELECT ...
0
votes
1answer
48 views
Faster joining on 'most recent' related rows
I have a bug tracking application. The bug table stores very little,
because every state change to a bug happens by adding a comment. For
example, the current state of a bug is determined by the state ...
1
vote
1answer
117 views
Query is not using indexes on third table in left join
My query is not using indexes on third table(pci promotion_coupon_images) .
i was tried with using index and force index in query but there is no change in result.
any body please suggest me how ...
6
votes
3answers
541 views
SQL Database Structure for RESTful API
I am creating a RESTful API. I am struggling to decide on the best way to design my database tables around my resources.
Initially, I though a table per resource would be a good way to go, but I'm ...
0
votes
1answer
81 views
Create a VIEW that spans 3 tables
Sorry if the title is not clear - feel free to improve.
Here are 3 tables (not all data is relevant to the question):
CREATE TABLE `content` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
...
1
vote
1answer
35 views
simple query is not using indexes
My query is not using indexes.
it was taking around 5 seconds.
output is 500 rows only. it is a simple query
Please find the explain plan and table structure here.
Explain Plan
mysql> explain ...
0
votes
0answers
74 views
Performance Optimising MySQL query
I have this query in my mysql database, when i execute this, it takes about 4 sec. To give me the result (on xampp 1.8.0 with mysql 5.5)
I was wondering if there is any way to enhance this query so ...
5
votes
1answer
116 views
Logical reads amount difference with almost identical indexes
I'm trying to figure out why two identical queries are taking slightly different times when executed under two indexes that are almost identical, in SQL Server.
The mentioned query is quite simple, ...
0
votes
1answer
60 views
Query taking long time
My query is taking around 540 seconds to get result.
Please help me to rewrite it.
Query:
SELECT pd.domain, fd.passkeyid
FROM portfolio.domains AS pd,
fabulousdomains.domains AS fd
WHERE ...
2
votes
1answer
147 views
Optimize simple query in SQL Server
This should be a quite easy query, but I honestly think its execution time can be improved.
select idTag,MAX(pctimestamp) AS PCTIMESTAMP,getdate() AS NOW, datediff(SECOND,MAX(pctimestamp),getdate()) ...
1
vote
2answers
180 views
Query Performance Tuning on a huge table
I have the following table Transactions. The table looks like this:
CREATE TABLE [dbo].[Transactions](
[TransactionID] [bigint] IDENTITY(1,10) NOT NULL,
[PlayerID] [bigint] NOT NULL,
...
4
votes
2answers
177 views
Performance on SQL Join
I have a query which takes ages to run mainly due to a join between 2 tables on a PK and FK.
The Key is prefixed with 2 letters (which is always the same, RN) followed by 7 digits
So RN1234567 for ...
2
votes
2answers
111 views
Query taking too much time
My query is taking too much time to execute. Please help me here.
mysql> explain
select pcm.catalog_id
from cat_produ_catal_map_defer pcm, cat_catal_catal_map_defer ccm, cat_catal_defer c
...
2
votes
1answer
193 views
Splitting a column to normalized table
I have a de-normalized table as
Original De-Normalized Table
id text
23 first,second|third,fourth,fifth|sixth
I want to normalize the database to create the following tables
Table 1: (Split by ...
0
votes
1answer
87 views
How can I optimize this query and support multiple SKUs?
My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
1
vote
2answers
177 views
How do I force a JOIN to use a specific index in MySQL?
I have a query that JOINs 2 tables; lineitem and part,
select
sum(l_extendedprice* (1 - l_discount)) as revenue
from
lineitem force index for join (l_pk),
part
where
(
...
1
vote
2answers
286 views
Why does my query run faster after I force full table scan?
I have a MERGE statement that took forever to execute the select as the table it is running from (SUB_DATA) is rather big (read 1.4 Tb, 700 million rows). My SELECT looks like this.
SELECT ...
9
votes
3answers
334 views
Are these two queries logically equivalent?
Are these two queries logically equivalent?
DECLARE @DateTime DATETIME = GETDATE()
Query 1
SELECT *
FROM MyTable
WHERE Datediff(DAY, LogInsertTime, @DateTime) > 7
Query 2
SELECT *
FROM ...
1
vote
2answers
102 views
How can I improve this query
I have the following table:
CREATE TABLE `twitter_relationships` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`source_twitter_id` bigint(20) NOT NULL,
...
3
votes
2answers
159 views
Do MySQL sub-queries essentially use as much overhead as separate queries?
If I have a query like this:
SELECT name FROM
(
SELECT * FROM `table`
WHERE id IN (1,40,300)
ORDER BY AnotherColumn
)
From a bandwidth and overhead standpoint, is that the same thing as doing ...
1
vote
1answer
117 views
query optimization delete nested query [closed]
Can the following query be optimized ?
DELETE FROM COLOR_TAGS
WHERE COLOR_PROJECTID = $id
AND NoteID IN(
SELECT ID FROM note_note
WHERE BibID=$bid AND isdel=0);
2
votes
1answer
307 views
Should I use left join to do my job in this scenario?
What I want to do is to find out the items which appeared more than once in this table:
mysql> desc tables;
+-----------------+---------------------+------+-----+---------+-------+
| Field ...
9
votes
4answers
907 views
Is it possible to increase query performance on a narrow table with millions of rows?
I have a query that is currently taking an average of 2500ms to complete. My table is very narrow, but there are 44 million rows. What options do I have to improve performance, or is this as good as ...
2
votes
3answers
184 views
selecting multiple instances of a record based on record lifespan over years
I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a ...
1
vote
1answer
129 views
Get and count all blog posts and all users that fits a LIKE condition (two sepparated queries)
I've this tables:
--
-- Table structure for table `default_cmvblog`
--
CREATE TABLE IF NOT EXISTS `default_cmvblog` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`title` varchar(100) COLLATE ...
1
vote
3answers
1k views
How to make multiple counts in one query?
I count records with queries like
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%something%'
SELECT COUNT(col1) FROM table1 WHERE col1 LIKE '%another%'
SELECT COUNT(col1) FROM table1 WHERE col1 ...
1
vote
1answer
101 views
EXISTS and sub-query cost
i've just wondered if there's a difference in cost(memory,cpu) of an EXISTS or NOT EXISTS in following queries. Does it make a difference if i select NULL, 1 or columns?
1)
SELECT id
FROM Parent p
...
3
votes
3answers
200 views
optimizing a compare query
Suppose I have two tables, A and B, and I know that size(A) = size(B). I want to confirm that the data in both tables is the same in three given columns, suppose they are X, Y, and Z (there are no ...
4
votes
1answer
65 views
Will these queries return the same results?
I have this simple query:
SELECT
friend_id
FROM
default_friend
WHERE
user_id = 1
And the results are below:
+-----------+
| friend_id |
+-----------+
| 2250 |
| 4901 |
| ...
2
votes
1answer
130 views
Need some help with this query get all status and their associated comments of me and all my friends
I've several days dealing with this query but can't get it to works as I want. This post is related to this one Several values from different tables in which I tough found the solution but I was ...
3
votes
2answers
190 views
which one is more efficient query?
I have a table called STUDENT
+-----------+-----------+-----------+---------------+
| StudentID | FirstName | LastName | EnrollmenDate |
+-----------+-----------+-----------+---------------+
| ...
1
vote
0answers
86 views
Get all comment.message, status.message, users.* and profiles.* where users.user_id exists in friend.friend_id or friend.user_id
I need some help building this query because it's a bit complicated at least for me. I need to get all comment.message, status.message, users.* and profiles.* where users.user_id exists in ...
0
votes
1answer
138 views
Is possible to optimize this query or it's already optimized?
I've three tables: default_users, default_profiles, default_friend. This are the SQL for each one:
--
-- Table structure for table `default_users`
--
CREATE TABLE IF NOT EXISTS `default_users` (
...
0
votes
0answers
103 views
Can I do a MySQL subquery across criteria with different number of results
I inherited a site which has a product search running Coldfusion8 and MysSQL(5.0.88). Both filling the search form with default values and the search take forever, so I'm trying to improve both.
...
1
vote
1answer
226 views
SQL Server 2012 Performance Test
I've been given a task to test where an SQL server 2012 can achieve better performances - we have two options:
Use the SQL Server as an On-Premise machine (Standalone server that we have)
Use the ...
