Tag Info

Hot answers tagged

8

You could break out of that subquery like this: SELECT Device_ip, Status, timestamp FROM ping_results WHERE Device_ip = '192.168.1.1' AND timestamp > DATE_SUB(NOW(), INTERVAL 800 MINUTE ) ORDER BY timestamp ASC That should simplify the query plan a bit, and you're only doing one ORDER BY instead of two. As everyone else also mentioned, ...


5

Is this good practice? In this circumstance I would say yes. I'd probably also add an OPTION (RECOMPILE) to let it "sniff" the variable values. The optimal plan will likely vary dependant on the proportion of rows in the larger table that match this range. It provides a potentially useful extra path to the optimiser and it is not something that the ...


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

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 ...


3

This is something you can easily test for yourself, but in my testing, no, there is no significant overhead in calling a stored procedure across a database boundary (I am sure you could make something noticeable though if you tried hard enough). However, I would say that a stored procedure should live closer to the data that it manipulates than the ...


2

Looking at the query, I see you need to retrieve the 800 most recent pings in ascending order. You should be able to improve the query with the following index ALTER TABLE ping_results ADD INDEX DEV_TIME_IP_NDX (`Device_ip`,`timestamp`,`ip_address`); This will help your query in the following manner The ORDER BY is quickly reduced to a backward index ...


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 ...


1

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 ...


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

In addition to @tombom's suggestions, creating an index on (user_id, post_id) instead of (or in addition, but the less indexes the better) separate indexes on user_id and post_id will simplify the query, probably getting rid of the filesort and temporary table, plus giving you the benefits of a covering index. This will probably lower the query execution ...


1

The order by column if have index gets highest priority when engines has to decide which index to use. Hope it helps


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