Tag Info

Hot answers tagged

15

A full analysis would require access to the execution plans, table and index definitions, and database statistics (or a copy of the database itself). That's possibly unrealistic, so here's some general observations, and a possible solution for you to try. (Strictly, this question is probably beyond this site's remit.) General background The SQL Server ...


3

You probably don't want to hear this, but the best option to speed up SELECT DISTINCT is to avoid DISTINCT to begin with. In many cases (not all!) it can be avoided with better database-design or better queries. Sometimes, GROUP BY is faster, because it takes a different code path. In your particular case, it doesn't seem like you can get rid of DISTINCT. ...


2

I suspect that the DISTINCT is a performance barrier. I suggest you get rid of the duplicates (I would think that no device would ever be in 2 distinct locations at the same time, so a unique index on (imei, timestamp) would work just fine). Such a change may need changes in your application, so until you do get rid of the duplicates, you could use this ...


2

Edit - The below is assuming that the number of rows that would be returned by the query (if the limit wasn't present) exceeds the limit. (as in ... it regularly would return 5000 rows, but the limit forces it to return 1000) Any time you have a limit on the number of rows returned by a query, you should not expect the TIMING on that query to have any sort ...


2

Option 1 will always force a O(N) operation (a table/index scan) which disqualifies this option for significant amounts of data. You must use option 2 if you want a solution that scales with the amount of data. If there are very few rows (such as 3 or so) option 1 might actually be faster. I doubt that we are talking about that case here.


2

Definitely you want to use option 2. Not only will your queries be faster (= is always faster than like) but you can also index on that keyword field for even faster queries AND your storage space will be significantly reduced since you are not storing the long keyword strings for each website.


2

No way! Every 'end' is not optimizable because of the leading wildcard. That means that if there are, say, a mere 3K 'end' entries in match, then there will be over a billion (350K * 3K) tests to perform! The query can be partially optimized by INDEX(type, string) -- in `match` INDEX(name) -- in `user` SELECT ... FROM user JOIN match ON ...


2

Why have an id at all? Why not have PRIMARY KEY (user_id, post_id)? Why have user_id and post_id nullable? Shouldn't they be NOT NULL? @jynus is right about a covering index, but if you change the PK as I suggest, that separate index won't be necessary. innodb_buffer_pool_size should normally be 70% of available RAM. I don't see how (pre)caching would ...


1

First, please remove the parentheses from DISTINCT(reservations.id). DISTINCT is not a function. About performance, why do you join reservation_nights? You only seem to use that table in the ORDER BY. Assuming that a reservation can span several nights, you probably mean to use the last date (from the many that a reservation has) for the ordering and ...


1

I would try breaking this down into three subqueries and then join the results using full outer joins. select distinct coalesce(a.name, b.name, c.name) from ( select user.name from [user] inner join match on user.name = match.string where match.type = 'exact' ) a full outer join ( ...


1

While by no means a dead cert, PAGEIOLATCH_SH and CXPACKET in equal weight can be indicative of parallel table scans. Without a baseline to compare with I'd suggest looking at the top CPU and IO consumers and looking for those which appear in both.


1

I think your understanding of query queues is a little off. A queue is like a thread in Java. A query arrives and is designated to the "less loaded" queue, and it waits for its turn to be resolved. The decision of where to put a query is independent of how busy a queue is; queries are allocated based on the rules you've set up: ...



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