Tag Info

Hot answers tagged

13

Your index is seemingly fine and good (i.e. covering) for the query and it should be used. The real problem is the query itself and specifically this condition which hides an implicit conversion: WHERE [serialNumber] = 137802 According to SQL Server's datatype precedence, when two values of different datatypes are compared, the value with the datatype of ...


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

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

Performing those subqueries repeatedly for each distinct ID value is what's killing you. I ran a test of your method on a sample table of 10,000 rows and it took ~2.5 minutes to execute on my notebook. By comparison, the following code consistently returned the same results in less than 0.5 seconds: Option Compare Database Option Explicit Sub ...


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



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