Tag Info

New answers tagged

13

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


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


0

You are missing the single quote closure after motherboard in both of your queries AND value != 'Motherboard AND AND a.value != 'Motherboard The first query can be more organized as SELECT id_product FROM Atr_basic WHERE attribute = 'Product Type' AND category = 140 AND value NOT IN ( 'Motherboard', 'Intel Motherboard')


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.


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


0

Try adding index on combincation of tableB (col2, ,col1) because selection is happening on col2. This should work.


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


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


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


0

You have the best index there is. It is in the right order, and the EXPLAIN says "Using index", which means that it read the index to get the answer, and did not have to reach into the data. (To further address all the comments...) Note that it needed to read about 200K rows (of the index) to do the count. That many rows takes time. INDEX(offer_id, ...


0

For now, the issue has been addressed by: Converting AllEligibleEntries to a temporary table Converting NumberOfEligibleEntries to a local variable, obtained from a select on the temporary table Converting SelectedEntries to a DELETE WHERE NOT BETWEEN operation on the temporary table Keeping the remaining CTEs as-is. Performance is measurably better (a ...


0

Rather than trying to explain why most of the partitions seem empty, let me argue against that flavor of PARTITIONing. To put it bluntly, PK & BY KEY(id, sha1) gains no performance, nor any other benefit that I can imagine. Note that to get "partition pruning", you have to specify both the id and the sha_checksum. Performance will be essentially the ...


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


0

WHERE playable_character = 0 AND date_published BETWEEN date_sub(now(), INTERVAL 3 YEAR) AND now() Start with the "=" item, then do the range: INDEX(playable_character, date_published); "Pagination", a la ORDER BY rating DESC LIMIT 4000, 1000; is best done by remember where you "left off". That way, you don't have scan over the 4000 records that you ...


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


0

(I'm writing this as I see your points; please read to the end before taking action. I 'develop' the best answer piece by piece -- hope you learn some things.) DELETE FROM xxxxx WHERE aggr_id = 3000010; must scan every row in every partition. That means it must do a lot of I/O, which will take a lot of time, regardless of the tuning. If this is a ...


0

I'd run sp_whoisactive a few times to get a sample of what's going on on the server. Spend your time on the queries that are running most of the time when you take a sample. Those are the hot queries. Not, that CPU usage is not (directly) reflected in the wait stats. Wait stats are not a profile of what the server does. It is a profile of what the server ...


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.



Top 50 recent answers are included