Tag Info

New answers tagged

0

I have an idea that MAY cause it: The tables you are showing us are not SELECT * FROM ... are they? I think that in dtr_tbl, the logdate has a time part that is not 00:00:00 and this causes that in the WHERE condition, log_date BETWEEN CAST('03/13/2015' AS datetime) AND CAST('03/23/2015' AS datetime) leads to ie 2015-03-23 8:00:05 between ...


1

There is no connection between these two tables, so a join won't really get what you need. If everything goes into a single result set, you need to UNION them together. Note that when doing a union, you get one set of columns, and you can't mix datatypes within a column (no concern here since all your data are FLOATs). Here's an example starting point: ...


-1

SELECT GROUP_ID, Group_Name, SUM(Price) FROM ProductsTable GROUP BY GROUP_ID, Group_Name


0

a SUM is an aggregation of data, when working with aggregations you can specify the clause GROUP BY to show results grouped by what you need. SELECT Group_Name, SUM(Price) FROM Table GROUP BY Group_Name


0

INDEX(type, stamp) should speed up "Select 1" significantly, even without LOCK IN SHARE MODE. (Please provide SHOW CREATE TABLE for further discussion.) Edit Even though you have type and stamp in indexes, having these two fields in this order will be optimal for "Select 1": INDEX(type, stamp). This is because the optimizer can drill into the BTree to ...


0

In addition to above answers, B-tree, cluster and fulltext indexes can be multiple and will work from left-to-right. For example in: where `1` like '%1%' and `2` like '%2%' and `3` like '%3%' You should have multiple fulltext index on (1,2,3) to achieve good performance. If you have only one column in where condition, fulltext index on that column will ...


0

First of all @Falcon explained correctly that the Index can't work with like if there is a % at the beginning. But with the normal FULLTEXT IN NATURAL MODE it's only possible to search full words and the words have to be at least 4 chars long (if you have access to the database you can change this) You might want to have a look at BOOLEAN MODE option in ...


1

Let me help you to understand how a "standard" index works. Most databases indexes are just B-Trees (not to confuse with a binary tree). Simply speaking, when you query an indexed column, a binary search will be performed. A binary search performs generally in O(log(n)) and thus you can find individual rows quite fast, even if there are many of them. The ...


1

select c.classid, c.classname, count(case when s.status = 'failed' then 1 end) as no_of_failed, count(case when s.status = 'passed' then 1 end) as no_of_passed from class c left join students s on c.classid = s.classid where c.schedule = 'MWF' group by c.classiid, c.classname; This exploits the fact that aggregate functions will ...


0

SELECT (My_Field_List) FROM My_Table WHERE My_Timestamp BETWEEN TIMESTAMPADD(HOUR, -1, Alarm_Time) AND TIMESTAMPADD(HOUR, 1, Alarm_Time); Details available in the ever useful documentation here. Example: mysql> CREATE TABLE Sample(My_Timestamp TIMESTAMP); Query OK, 0 rows affected (0.71 sec) mysql> SELECT * FROM Sample; ...


0

show and set are commands of the MySQL client; you can't prevent users from using them. set global is only allowed if the user has the super system privilege. See dev.mysql.com/doc/refman/5.1/en/grant.html#grant-privileges


1

No, you shouldn't use OPENQUERY IMHO. How about this construct, which allows you to use sp_executesql and parameters: DECLARE @someParam INT = 5; EXEC LinkedServerName.master.sys.sp_executesql @stmt = N'SELECT @i, @@SERVERNAME, @@VERSION;', @params = N'@i INT', @i = @someParam;


1

In Postgres 9.4 you can simplify with the aggregate FILTER clause: CREATE VIEW fumbbl.matches AS SELECT m.fmid, m.time, d.name AS division , min(s.coachbracket) FILTER (WHERE NOT s.away) AS hbracket , min(t.name) FILTER (WHERE NOT s.away) AS hteam , min(c.name) FILTER (WHERE NOT s.away) AS hcoach , min(s.score) ...


0

You're seeing the intrinsic drawback of OFFSET pagination. Using LIMIT, you force your DB to sort all the records and count them all the way up to your OFFSET base. Keyset pagination may correspond more closely to your actual needs here and will yield much better performance. As described in the above linked article, simply paginate based on the last key ...



Top 50 recent answers are included