New answers tagged

0

You are I/O-bound. That cannot be solved by tuning! Normalize region, customer, etc. This will save a lot of space and shrink the data, thereby making queries faster. Consider not splitting date and time -- it is usually less complex in the long run to have a single DATETIME. As @Quassnoi spells out, summarize the day's data each night. Put the results ...


2

Would i benefit in this case from the myisam table engine? No you won't. What could i do to further optimize the query or the settings? Materialize the query. MySQL does not have built-in means to do that easily (similar to indexed views in SQL Server or materialized views in Oracle) so you'll have to put some effort into it. Create a table like ...


2

You don't select those ids that are minimum in their systemno group so you select only those that have a lower id in their group: SELECT id FROM dbo.SomeTable st1 WHERE EXISTS ( SELECT * FROM dbo.SomeTable st2 WHERE st1.SystemNo = st2.SystemNo AND st2.id < st1.i )


2

why it expects for 1 row (in Clustered index seek), when I specify recompile That is fine. Somewhat confusingly the estimated rows on the inside of a nested loops join are per execution of the operator. A seek into a primary key will indeed return 1 row (or 0 if the value doesn't exists at all). In your case you have 2,000 seeks all returning 1 row ...


1

The two-column btree index will help with the like 'foo%' query, but probably not dramatically so. It helps because it can be executed as an index-only scan, and so it can compute the LIKE portion within in the index without ever having to visit the table. The index scan will jump to the first entry > '01/01/2012', and then traverse from there to the ...


0

Yes, it's correct, although, as noticed by yourself , it's pretty crude. This has been asked months ago. Why didn't anyone answer it yet? Are you still working on this project? I wonder if you really have the right skills to complete this... If it's indeed interesting, I could help you with the database architecture. One word of advice: MySQL does not ...


0

In Data Warehousing, "Summary tables" are very important for performance. A Summary table contains counts and subtotals for each day (or other time period). "Reports", such as your query are then run using a summary table, and can run much faster than running against the 'raw' data. For your case, build and incrementally maintain a table with, say: day, ...


2

That one query can be optimized with a composite index: INDEX(SuministrosId, Fecha). Let's see some more. (Meanwhile, that pie chart is useless, it rarely says anything other than "sending data", and that gives no clues.) Also, it may help to set innodb_buffer_pool_size to about 20G assuming you are using 64-bit OS and MySQL.


0

Give this a try: Add this composite index to user_activity: INDEX(user_id, action). Then SELECT SUM(x.ct), u.user_type, x.action FROM ( SELECT user_id, action, COUNT(*) AS ct FROM user_activity GROUP BY user_id, action ) x JOIN user u ON x.user_id = u.id GROUP BY x.action, u.user_type;


2

Looking into my crystal ball, your query might work faster by orders of magnitude like this: SELECT i.*, rr.urllist FROM vw_image i LEFT JOIN LATERAL ( SELECT string_agg(r.url, ',') AS urllist FROM resourceonpage rop JOIN resource r ON r.id = rop.pageid WHERE rop.siteid = 2294 -- or: = i.siteid AND r.resourceid = i.resourceid ...


10

How to avoid implicit conversion for an Integer column It is the parameter that has been implicitly converted, not the column. The query has been subject to Simple Parameterization by SQL Server. You have no control over the datatypes used in this process. It uses the smallest datatype that can hold the literal value (5 can fit into a tinyint). The ...


0

That should also work more efficient than Willem solution SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout FROM employees LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno LEFT JOIN attendanceOut ON employees.eno=attendanceOut.eno WHERE DATE(attendanceIn.puchtime) = '2016-07-01' AND DATE(...


1

Do not use functions on your columns you are comparing against, as that prevents the query from using any indexes you may have. Start with this: SELECT employees.eno,employees.ename,employees.dept,attendanceIn.attIn AS attIn,attendanceOut.attOutAS attout FROM employees LEFT JOIN attendanceIn ON employees.eno=attendanceIn.eno LEFT JOIN attendanceOut ON ...


3

Preliminary notes You are using odd data types. character(24)? char(n) is an outdated type and almost always the wrong choice. You have indexes on person_id and join on it repeatedly. integer would be much more efficient for multiple reasons. (Or bigint, if you plan to burn more than 2 billion rows over the lifetime of the table.) Related: Would index ...


4

This query usually perform better: SELECT rn.ID, --... other columns go here rn.OrganisationID FROM ( SELECT *, n = ROW_NUMBER() OVER(PARTITION BY OrganisationID ORDER BY id) FROM #t ) rn WHERE n= 1


1

Note sure if it will be more efficient select * from ( SELECT PID.ID, PID.OrganisationID , row_number() over (partition by PID.OrganisationID order by PID.id) as rn FROM #t AS PID WHERE PID.OrganisationID <> 0 ) tt where tt.rn = 1


1

Be aware - I posted this before index definitions were posted Insert in the order of the PK to keep fragmentation down INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT 2, value, 0 FROM Z_SIMULATION_0 WHERE ID >= 1 AND ID <= 500000 ORDER BY value; or INSERT INTO Z_SIMULATION_1_TABLE (ID, ELEMENT, IS_ACTIVE) SELECT top (...


4

It isn't just doing one insert operation per row, there is one insert per row per index with the associated sorts too. Each of those sorts may be spooling to disk (and probably is with that much data) to there is a lot of IO going on. When completely rebuilding a table's contents (i.e. starting with a blank table) it is usually more efficient to drop or ...


4

Idea 1 Judging by their names, the columns "denormalizedData" and "hugeText" seem to be comparatively big, probably many times as big as the columns involved in your query. Size matters for big queries like this. Very big values (> 2kb) for text or jsonb get "toasted", which can avert the worst. But even the remainder or smaller values stored inline can be ...


2

You are correct that PostgreSQL cannot currently use one index to provide selectivity and another index to provide order, in the way you want. Adding this feature has been discussed, but I don't think anyone considers it a high priority. Creating a multicolumn index will not help in any way. From my testing Postgres will not change its query plan at all ...


0

In addition to what Ollie says... "Rows_sent: 10 Rows_examined: 48683733" -- Is that a table scan? A giant GROUP BY? Those are things to work on avoiding in a production server. For Data Warehousing applications, build and maintain "Summary table(s)" that have subtotals by day (or week) so that the query might hit 400K rows instead of 46M rows. That, ...


0

Fuzzy matching made no sense, given the number of entries I would be dealing with. I needed something to quickly tell me whether or not a duplicate already existed. Consequently, I ended up creating hashes of all unique combinations for a single company. For example, if three different sets of information about Company A were obtained: Set one has hashes ...


-1

I think you don't need to have any indexes (Rather than primary key) for user table which is mainly used for authentication, because most of the fields in user table has unique rows (correct me if i am wrong), so Cardinality is very high. If you index high cardinality field it might be a performance hit. You can go through this article for more information ...


4

tl;dr You can't force MySQL to use a lot of cores. 40 million (4 crore) rows is a large, but not huge, database for MySQL. It is well within the capability of that software. You don't have to resort to desperate measures to get MySQL to work with that amount of data. You do have to index it correctly though. MySQL can use a lot of CPU cores as of recent ...



Top 50 recent answers are included