0
votes
1answer
75 views

mysql directory grow to 246G after one query, which failed due to table is full

I was trying to run the following statement with the hope to create a join of two existing tables. create table CRS_PAIR select concat_ws(',', a.TESTING_ID, b.TRAINING_ID, a.TESTING_C) as k, ...
1
vote
1answer
98 views

Innodb buffer pool and multiple joins

assume we have 1 Billion records and want to store them in One or Three tables by post_type. post_id -> primary key post_type -> ( page, group, user ) user_id -> index date -> ...
0
votes
0answers
220 views

How to increase MySQL performance with joins

I have a MySQL database with InnoDB engine. Particularly one of my queries became very slow. The query is shown below: SELECT t1.`ARTREF1` FROM `lijnen` AS t3 INNER JOIN (`kortingen` AS t2, ...
1
vote
1answer
129 views

Combining multiple left join for one single line

We have a query as below. SELECT tblSData.header, tblEAlert.eMessage FROM tblSData LEFT JOIN tblEAlert ON tblSData.sDataID=tblEAlert.sDataID Where tblSData.aID=".$aID." Order By tblSData.dateTimer ...
-1
votes
2answers
2k views

How to use GROUP BY after the Having clause Mysql

Here is the query which gets the accounts for a specific user the products are related to accounts in one-to-many relation so for each product there are four permissions All the accounts which is not ...
2
votes
1answer
790 views

How does mysql deal with queries that touches myisam and innodb tables?

In our mysql database, we use both myisam and innodb tables, though there are more myisam tables. QUESTIONS If a query involves both myisam and innodb tables, will the query use table lock or row ...
2
votes
1answer
501 views

inner join on PK with extra criteria slow despite indices?

Given the two tables below I am struggling to understand: why is the third query slow even though first two queries are fast what exactly is EXPLAIN saying can I do anything to significantly speed ...
3
votes
1answer
826 views

MySQL 5.5.8 InnoDB Foreign Key, JOIN performance

We are using aws/rds, MySQL 5.5.8. all InnoDB. For performance purposes, we have merged some data-elements into varchar fields; mini-sized int and date type fields; added covering indexes; ...