Tag Info

Hot answers tagged

7

...SSMS is not running this using sp_execute so I don't think this is being caused by parameter sniffing. What are the possible causes for this behavior? The optimizer cannot 'sniff' the values of local variables, so the cardinality estimate is based on a guess. If you are using the original cardinality estimator, the fixed guess for BETWEEN is 9% of ...


4

Since you started both servers, you have executed (approximately) 162509 + 33073 + 11291 = 206,873 queries on the Linux server and 44648032 + 6866308 + 994889 = 52,509,229 queries on Windows. Why would you expect similar numbers when one has done more work than the other? The ratios, however, are similar with: 162509 / (162509 + 33073) ~= 83% 44648032 / ...


3

You can simply do SELECT * FROM reviews WHERE product_id = 723 ORDER BY language_id = 4 DESC, language_id ASC; language_id = 4 returns true or false, 1 or 0. Sort descending and you have language_id = 4 first, then all others.


3

This Script contain few columns 1. UserSeek : Number of seeks by user queries 2. UserScans: Number of scans by user queries 3. UserLookups: Number of bookmark lookups by user queries 4. UserUpdates: Number of updates by user queries on the basis these column stats you can figure out either index is useful or not. Actually Index are ...


3

Using debugging parameters like enable_nestloop should not change the results of a query. If it does that'd generally be a bug. However, you should not use enable_nestloop = off or similar in production. It's a very big hammer, and while it might appear to fix your immediate problem, it'll create performance problems elsewhere. A nested loop is often the ...


3

Based on your question, I would index the Timestamp column with the clustered index. And to make the index unique, just make sure to include the identity column in the index definition: ... PRIMARY KEY CLUSTERED ([Timestamp], [Id]) If query performance for queries on Exchange_Id is still an issue after that, you can also add a non-clustered index that ...


2

some 7 seconds for 500k to return, and also a lot of time to render the grid you're likely displaying the results in. You are waiting 7 seconds because that's how much it takes for SQL to push 500k rows to your client. Look at client statistics in your SSMS, see Database Engine Query Editor: Include Client Statistics: Includes a Client Statistics ...


2

Your conditions are searching for ranges. Now imagine you're looking in a dictionary and looking up all words where the first letter is "greater" than A. Of how much use is the index? You want to narrow the search range down as much as possible. MySQL most of the time can only use one index per table. Combine those begin and end indexes. CREATE TABLE ...


2

If you are 100% sure that the intervals (begin, end) are never going to be overlapping, you can use this query, which only needs an index on (begin) or (begin, end) and will be much more efficient than what you have: SELECT t.* FROM ( SELECT g.* FROM geo_ip_city AS g WHERE g.begin <= 2523596988 ORDER BY g.begin DESC -- ORDER BY ...


2

From my (rather limited) knowledge of php, I assume you want to combine the two queries into one: SELECT m.*, p.frequency FROM ( SELECT sid, COUNT(*) as frequency FROM plays WHERE time > NOW() - INTERVAL 3 DAY AND sid <> '' GROUP BY sid ORDER BY COUNT(*) DESC LIMIT 50 ) AS p JOIN music AS m ON m.sid = p.sid ...


2

I would do the following SELECT *, CASE WHEN language_id = 4 THEN 0 ELSE 1 END Ord FROM reviews WHERE product_id = 723 ORDER BY Ord, language_id


1

No, please don't store this data redundantly - you already have all of the information you need in the base table, without incurring the overhead of triggers or additional writes. Just create an index that supports your query, somewhat similar to what @Colin suggested, but I'm adding the output columns to avoid key lookups: CREATE INDEX ix_last_position ...


1

Use following query for example. SELECT * FROM sakila.actor a where a.actor_id = 10 UNION SELECT * FROM sakila.actor b where b.actor_id != 10 result of query # actor_id, first_name, last_name, last_update 10, CHRISTIAN, GABLE, 2006-02-15 04:34:33 1, PENELOPE, GUINESS, 2006-02-15 04:34:33 2, NICK, WAHLBERG, 2006-02-15 04:34:33 3, ED, CHASE, 2006-02-15 ...


1

The amount of memory specified in innodb_buffer_pool_size is allocated when the MySQL Server daemon starts up, and if the system doesn't have that much memory free at that moment, MySQL won't start. The amount of memory you specify is what the buffer pool gets, and it neither grows nor shrinks. InnoDB reads pages from disk into the pool as they are ...


1

SQL Server stores all the columns for one row together in on a single disk page. (It's more complex than this but for int and char etc. this is more-or-less true.) To retrieve any column's value the whole page is read into RAM. So once you have any column available for a given row, all of the columns for that row are available. There are several buffers ...


1

if you trying to use the same query then it could be optimized adding composite index to geo_ip_city the new index could be ALTER TABLE geo_ip_city ADD INDEX ind_begin_end (begin, end) USING BTREE with this you would need to remove you use index in the select statment to be SELECT * FROM geo_ip_city WHERE begin <= 2523596988 AND end >= ...


1

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly. Let's take your query and look for 'tom' SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info ...


1

You don't need a self join of any kind. Just use variables. SELECT time_recorded AS current, @prev AS previous, timestampdiff(second, time_recorded, @prev) AS diff_between_current_and_previous, @prev := time_recorded FROM reading , (SELECT @prev := null) var_init_subquery WHERE device_id = 1154 ORDER BY time_recorded What's important in this query is ...


1

Could be done much simpler, I guess; select id, type, rate, abs(25 - rate) as delta from rates order by delta LIMIT 3



Only top voted, non community-wiki answers of a minimum length are eligible