Tag Info

Hot answers tagged

8

If you are trying to delete a large number of rows in a single statement, then it is likely you are waiting on log activity. So you can: Make sure your log is adequately sized so that growth events don't slow you down. With the defaults your log is probably starting at 1MB with 10% growth. Growth events are expensive, and if you are logging even 10 GB of ...


6

SUGGESTION #1 SELECT A.* FROM table1 A INNER JOIN ( SELECT '2015-03-01' dtcolumn UNION SELECT '2015-03-15' UNION SELECT '2015-04-01' UNION SELECT '2015-04-15' UNION SELECT '2015-05-01' ) B ON A.dtcolumn >= B.dtcolumn AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY; SUGGESTION #2 SELECT * FROM table1 WHERE (column >= '2015-03-01' ...


6

The problem is that it might (and knowing spatial indexes, probably will) assume that the spatial filter will be a lot more selective than the time filter. But if you have a few million records within 200km, then it could be significantly worse. You're asking it to find records within 200km, which returns data ordered by some spatial order. Finding the ...


3

First retrieve id and the computed scores, then sort and join the result back to news: SET @line = 0; SET @last = 25; SELECT B.*,titlescore,timescore,textscore FROM ( SELECT id,titlescore,timescore,textscore,combinedscore,(@line:=@line+1) line FROM ( SELECT id, MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ...


2

Have you tried this? SELECT Id, StatusDate, [ some way to select columns from appropiate table depending on result.tbl ] FROM ( SELECT Id, StatusDate, 'Results_201505' as tbl FROM Results_201505 WHERE A = 0, B = 1, C = 3 UNION ALL SELECT Id, StatusDate, 'Results_201504' as tbl FROM Results_201504 WHERE A ...


2

I don't think you'll be able to get good performance while using OFFSET. The database must search through 1,000,025 rows of output from the inner query; even if you have a good clustered index on TaskResults the system doesn't know for certain that it can skip ahead to date X. But you do! Assuming this is for some kind of GUI, make a note of the earliest ...


2

Three points that the query can be improved: remove the join to company. The joining condition is through a non-nullable foreign key, so it should always be true. change the GROUP BY x ORDER BY x DESC to: GROUP BY x DESC. This will avoid the extra sorts. Note that the syntax is under deprecation, so you may need to change it back in a future mysql upgrade. ...


2

In situations where one static sql (procedure) executes better than an equivalent one, I would examine: a) whether this depends on the order of execution, i.e. does the second one benefit from the first one reading data from disk into the bufferpool? If one runs better than the other regardless of the order they are run in this is not the case. b) are the ...


2

Try creating a couple of temporary tables, indexing them, analyzing them, and then join to them.. CREATE TEMPORARY TABLE luid_lookup AS SELECT id, find_user(id) AS fuid FROM legacy_table; CREATE TEMPORARY TABLE eoid_lookup AS SELECT external_object_id, find_external_object(external_object_id) AS feid FROM legacy_table; CREATE INDEX luid_lookup_tmp_ix1 ...


1

I do not know how dynamic your queries are, but naturally each table needs a separate query for that table. Assuming that you are constantly querying this 4 table group, it might be worthwhile to create an indexed view that included the searchable fields you need from the 4 tables into a single view. This means the overhead of maintaining the indexed ...


1

By default, the results are entirely buffered in memory for two reasons: 1) Unless using the -A option, output rows are aligned so the output cannot start until psql knows the maximum length of each column, which implies visiting every row (which also takes a significant time in addition to lots of memory). 2) Unless specifying a FETCH_COUNT, psql uses the ...


1

Well, this is a bit of voodoo/shotgun debugging, but I've got it functioning okay for the time being. I set the extended_keys option to on, and also created a covering index on the 8-million-row table in question. Now I'm getting an execution plan that's even better than the acceptable plan the old server was coming up with (which was only using Magento's ...


1

What you have is a full table scan with no way to speed it up. Look into FULLTEXT(bussiness_name, trading_name) indexing as a much faster way to search for words. You would need MATCH (bussiness_name, trading_name) AGAINST("$data['searchstring']" IN BOOLEAN MODE). If status is rarely 1 and is_approved is rarely 1, then this would help: INDEX(status, ...


1

Try something like this: (I did this on Oracle, it should mostly work elsewhere, the WITH clauses are mostly for just faking sample data .. so not entirely necessary) with w_date_list as ( -- just some sample input dates - these are from your IN list (note that you want to re-org them as a "table" not an IN list - there's ways of doing that if you need ...


1

It is slow because the table is big, and it performs lots of IO when reading sparse data. The index on w2 would be used to tell which rows will be read from the base table, which is huge. And since the rows in that table are not in order- physically that are fragmented, there will be probably one disk access per row (This is to read the value of w1) To make ...



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