Tag Info

New answers tagged

0

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


0

I'm no expert on the subject, but I'd recommend you look into FULLTEXT searches and indexes. From what I understand, they're much more efficient than LIKE statements for searching for part of a string. Here's the documentation on the feature.


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


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


0

Rewrote query to the following, reduced query time to 0.016s: SELECT *, floor((upvotes * 100) / (upvotes + downvotes)) AS percentage_liked FROM ( SELECT COUNT(*) AS views, file_name FROM video_views GROUP BY file_name ORDER BY views DESC LIMIT 12 ) p JOIN uploaded ON p.file_name = uploaded.file_name WHERE conversion_finished != 0 AND ...


0

Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement. I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance. It sounds like you have a Primary Key (BIGINT) already and ...


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


0

I did an upgrade to percona mysql 5.6 and it solves a problem. Both type of queries runs equally. Fortunately - equally fast.


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


0

I conducted a series of performance tests against the new service tiers (as mentioned by another poster above). I tested I/O rates back in July and have now also tested the memory (i.e. max buffer pool size) in each tier: http://cbailiss.wordpress.com/2014/11/11/azure-sql-database-memory-limits-by-service-tier/ (I was going to add this as a comment on ...


0

Indexes are usually the best way to increase the speed in any query, but there are other alternatives that might help: 1) Check if the field you are joining the tables are INTEGERS instead of CHAR/VARCHAR. In your case would be s0_.shop_id = s1_.id. Replace them by INTEGERS if possible, that reduces the time to join the table during the query. 2) The field ...


1

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


0

Look at http://stackoverflow.com/questions/11442191/parallelizing-a-numpy-vector-operation Essentially, you could do this in numpy relatively easily, with numexpr to take advantage of multi core. I haven't tried it yet, so YMMV.


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


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


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


0

Two things: 1) do you have indexes? What does show index from ping_results say? 2) Don't use subqueries. Convert the query to a join statement.


0

I've seen the query planner do similar things with index-less temporary tables, even sometimes when the number of rows in the temporary table is small. Try adding an index that covers the columns you are joining and filtering on, to see if the planner uses the stats from these to notice it can achieve the goal a more efficient way. Additionally: if you do ...


0

Put the list of values into a temporary table, and then perform an INNER JOIN to it. Most SQL optimizers and engines can handle joins much better than they can handle an IN operation. If the list is long, this also allows you to define an index on the temporary table to further assist the optimizer


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


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


0

Just going to let you guys know what I did, thanks for responding. I ended up sticking with my original query but added keys to my table, which are apparently a thing I didn't know about. When I did ALTER TABLE tblstatushistory ADD KEY (id), ADD KEY (itemtype) It dropped the time waaaay down. Thanks for pointing me towards resources that taught me about ...



Top 50 recent answers are included