Hot answers tagged

10

Your first query works as an inner join because the y.TransType = 'used' condition which uses the right table is in the where clause. Your second query can be rewritten without derived tables by simply moving that condition to the on clause: select x.*, y.* from factI as x left join factI as y on x.tickedId = y.tickedId and ...


3

Do the percentages and query cost even mean anything? Not really--They have meaning, but they're unreliable. are they referring to the estimated data from the query plan, and do not reflect the real cost? Otherwise how could the same join make up 39% of the "cost" for both, but one takes 2 seconds and one takes 47 seconds? Exactly--The cost is ...


3

Quoting the manual: There are two ways to delete rows in a table using information contained in other tables in the database: using sub-selects, or specifying additional tables in the USING clause. Which technique is more appropriate depends on the specific circumstances. Bold emphasis mine. Using information that is not contained in another ...


2

very close only the GROUP BY Columns forgotten Try this: select PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, PT_BASIC.NAME_FIRST, PT_BASIC.NAME_LAST from PTC_DIAGNOSIS inner join PT_BASIC on PTC_DIAGNOSIS.PATIENT_ID=PT_BASIC.PATIENT_ID where PTC_DIAGNOSIS.create_date>'12/01/2015' group by PTC_DIAGNOSIS.PATIENT_ID, PT_BASIC.PATIENT_CODE, ...


2

As Patrick7 has already said, you need your GROUP BY to include all the non-aggregated columns that are in your SELECT list. In your case, however, there is a way to avoid duplicating all the SELECT columns in GROUP BY. You could first aggregate the PTC_DIAGNOSIS rows separately: SELECT patient_id FROM PTC_DIAGNOSIS WHERE create_date > '20151201' ...


2

There are several problems with the query The select list is missing. The join syntax needs ON, not WITH: a JOIN b ON <some condition> There is a missing on condition (what you correctly identified as arbitrary/random join between Contractor and ContractorJob). There are column names mismatches between the code and the design: (Job.contractor_job ...


2

I would opt for the third option purely for readability and simplicity. Have you looked at the execution plans for all three, I expect they may give the same result for each. I would have expected the bracket query with the * would have been slower.


2

You can use variables: delimiter // CREATE PROCEDURE mysql.selectTables (_first_name varchar(30), _last_name varchar(45), _create_time timestamp, _update_time datetime, _hashid int, _id_status bit(2), _id int, _criminal_status bit(1), _dob int, _stateid int, _stateid_status bit(1)) begin START TRANSACTION; SELECT @fn:=`first_name`, @ln:=`last_name`, ...


2

What you are looking for is a 0-many relationship. This is usually referenced by a third table. Publication_Scientists : Publication_Id (int) | Scientist_Id (int) with 1:1 relationships back to the other two tables. Preferably using something like an enforced foreign key.


1

In your first query, in the where clause, you're restricting results to those where y.TransType = 'used'. This turns the left join into an inner join because you're throwing away results where y.TransType is null (aka where the other ticket doesn't exist). An easy fix for this is to move that condition to the on clause like this: select x.* from factI as ...


1

The actual execution plans produced by a DBMS optimizer does not need to follow exactly the SQL code. It only has to produce the same results as if it followed the code. The optimizer is free to do any optimizations and is usually better at it when the code is simpler. Some optimizers (for example MYSQL before 5.6) would create a better execution plan with ...


1

The first query you have used returns Cartesian product of the rows from these three tables. For details: Join The following is the simple example to join three tables. SQL> select * from student; STUDENT_ID FIRSTNAME LASTNAME ---------- -------------------- -------------------- 1 Will Smith SQL> select * from module; ...


1

My own hint with the Write Set Cache Size (aka Galera Cache Size) nailed it (I should have asked earlier, it seems some supernatural voice told me the answer.) However I do not quite understand why 15 GBytes were too few for 45 KBytes/sec for an 18 hours transfer after a next try, obviously it was only a bit too short... (In my question I wrote about 10 ...



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