-3
votes
1answer
20 views

When you type an SQL query in interpreter, what does happen? [on hold]

Actully this question is asked in an interview from me . When you type an SQL query in interpreter, what does happen? How is the query processed?
0
votes
2answers
46 views

Complex query: find all users who rated all the movies rated by user_id='1234'

So we have the following relational schema: users(user_id, ..) movies(movie_id, ..) ratings(user_id, movie_id, rate) So, we want to find all the users who have rated all the movies which are rated ...
0
votes
0answers
17 views

MYSQL Tables Join Filtering For Favorites

I am in need of some help figuring out the best way to join separate tables I am using. I am essentially trying to output a list of schools (first table in MYSQL) where one column is an empty star ...
0
votes
1answer
32 views

MySQL dies when trying to access a specific database

For some reason, whatever I do I cannot access one of my databases (through command line or through something like phpmyadmin). However, the application that depends on this database is not having any ...
0
votes
1answer
17 views

SQL: Repair With Keycache

Now my MySQL server running a REPAIR TABLE, table is almost 15gb size. Status - Repair with keycache. How I can restart REPAIR TABLE with normal (repair with sort) mode?
0
votes
2answers
21 views

Subquery or multiple left joins, or both?

I have two tables, for simplicity table1, with id as Primary key, and a field name, and table2 with the same. Each table has quite a few records, but during a complicated form process, the user is ...
0
votes
0answers
35 views

SQL syntax error [on hold]

I am working with a MySQL DB. When I run the code I get the following error: Error 1064. You have an error in your SQL syntax. What am I doing wrong? UPDATE project SET STATUS = 2, REACT_TIME = CASE ...
1
vote
1answer
23 views

SQL: SOS: MySQL indexes overloads disk

My server is down now, 'couse indexes of 84GB DB are overloads disc. Here is output of df-h: /dev/ploop58097p1 193G 187G 16M 100% / tmpfs 2,0G 0 2,0G 0% /lib/init/rw ...
0
votes
0answers
14 views

MySQL Case always executes the same [duplicate]

What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The problem is that the first condition is ...
0
votes
2answers
21 views

MySQL Case problem

I am trying to run a SQL code (I use MySQL). What I want to do is to set DATE_REACT a value when the first condition is satisfied, and a different value when the first condition is not satisfied. The ...
0
votes
2answers
17 views

Set of datetime versioned rows, how to build the most up to date row

I created this SQL fiddle with the sample data The sample data is this: -- -- Table structure for table `dogs` -- CREATE TABLE IF NOT EXISTS `dogs` ( `version_stamp` datetime NOT NULL, ...
-1
votes
0answers
19 views

SQL: How to optimize a 82+GB MyISAM Database? [closed]

I have a MyISAM DB with some content (users and admins account and thematic content). All query from PHP to my DB with mysql_ library (not mysqli!) have a mid process time 8-12 seconds, if online of ...
0
votes
0answers
7 views

MySQL Replication problem with VIEWS and “Sub-Databases”

I've got three servers that should do a master-master replication. On server 1 i got "01_maindb" which holds the master tables. I replicate this table to the two other servers without problems. ...
0
votes
1answer
30 views

SQL “select” query for getting 0 instead of null?

In the database I have some value as null . So while fetching these null value from database it should be treated as 0 not as null. Note:- I don't want to be it done by code but from SQL query.
0
votes
0answers
13 views

MS Access using MYSQL database and error in SQL (memo, hyperlink OLE)

I have a SQL statement that works fine in MYSQL workbench but when used in Access it produces a "invalid Memo, OLE or Hyperlink Object in subquery 'custom_values.value'. SELECT ccr1.value, ...
1
vote
1answer
49 views

AND operator in ORDER BY clause

While debugging some code, I stumbled into an error pretty much like the following: SELECT * FROM person WHERE family_size > 3 ORDER BY birthday AND registered=0; The error is that the AND ...
-1
votes
0answers
27 views

Best Mysql Performance Booster for Large Datawarehouse [closed]

What are the most effective and efficient ways to improve the performance of mySQL DB hosting a large Datawarehouse. Please suggest the best methodologies and performance tweaks that should be there ...
0
votes
1answer
13 views

How to check mysql password change and other events logs

Recently someone change my phpmyadmin password and drop database I think it was sql injecting. Now I want to find information about this like when, how and who change my password Please tell me ...
0
votes
0answers
17 views

MySQL - Pivot-style (row -> column) results in subquery

SELECT j.departure, stopDepartures.* FROM journey j JOIN journey_day ON journey_day.journey = j.id JOIN service ON service.id = j.service JOIN pattern ON j.pattern = pattern.id JOIN ( SELECT ...
2
votes
2answers
53 views

Unexpected result from query based on old column value

I need to update a column and based on the old value, update a second column. After googling I came up with THIS (*nr 4) solution: copy the table to have the old value at hand. The table looks like ...
1
vote
1answer
29 views

How to find non utf8 data in mysql

Ok so I have a mysql database containing data that is input from various customers. The customers are told that their data should only be encoded as utf8 however I have become aware that this is ...
0
votes
1answer
38 views

SELECT count vs DELETE JOIN. Why/how are they different?

Can someone explain in what way these queries could be different and the results are not the same? > begin; Query OK, 0 rows affected (0.01 sec) >select count(*) from tableA a JOIN tableB ...
0
votes
0answers
22 views

SQL query to fetch results for multiple many to many relationship tables

We are developing a database for storing licensedetails of source code, archives, shareobjects, binary. We parse a detailed build and extract list of source files that make objects, list of objects ...
0
votes
0answers
18 views

MySQL hook and alter incoming INSERT queries?

We have an application running on a server which parses an xml data feed into SQL queries and executes them continually 24/7. There is a serious issue in the design of the queries and we need to ...
1
vote
2answers
32 views

Unable to SQL => Getting content of last 7 days

I thought I was good with SQL till I was told to get the content of all 30 days. I do have a column called "connect_time" which has date and time in the following format: 2014-08-28 11:11:42 All my ...
0
votes
1answer
71 views

Why isn't this index helping my InnoDB MySQL query?

I'm having trouble understanding why an index I have on one of my tables is doing nothing to help the performance of a JOIN, even when forcing the index. Here is the query: EXPLAIN SELECT * FROM ...
0
votes
0answers
26 views

Cant Normalize my dimensions and fact table, need help

I have a task at school that I can't get my head around. The task is to create some dimetables and create a facttable from those. The data I have chosen is from Export and Import cost for each year ...
0
votes
2answers
65 views

Querying MySQL, duration increases 10x fold after adding one more Join

I have a somewhat complicated query consisting of many joins on an issue tracker/project management database in MySQL. My issue is that my query was doing OK while I was in the process of building it ...
0
votes
0answers
12 views

Control Query Time

I have a table for comments and I want users to wait 5 minutes before sending other comments. I have five columns: id, id_user_to, id_user_from, message and ,date (this is a table for comments). I ...
2
votes
1answer
52 views

Running a query on an improperly indexed, 600gb table

This is my first post on here and I'm really baffled by this. I have joined a team and somewhere in the past a horrific table was created which has been bloated to a point of 600+gb of data. ...
0
votes
1answer
27 views

column value is not increment after 10

I tried below 2 codes.... showing same error 1.SELECT columnname FROM tablename ORDER BY columnname DESC LIMIT 1 SELECT MAX(columnname ) AS max FROM tablename I am trying to increment value of ...
-1
votes
0answers
17 views

Sync MYSQL to MSSQL

I have 2 applications and one uses MSSQL and the other uses MYSQL. I am trying to copy columns from one table in mysql to a column in mssql table. On mysql database i have a table called Contracts ...
0
votes
2answers
35 views

MySQL: How to fetch nth level child menus of a parent menu

I have a menu which is parent of multiple menu items and each child menu has their own child menus. This pattern is continued till 5-6 vertical hierarchy level. I don't want to write a query which ...
1
vote
2answers
48 views

Getting root node for a given leafs IDs

I got the following tables: +-----------------+ | PRODUCTS | +-----------------+ | #product_id | | #company_id | +-----------------+ | | +---------------------+ ...
0
votes
2answers
30 views

How to rewrite subquery to return only 1 row

I need to somehow check if query result is NULL, MySQL returns error Subquery returns more than 1 row. How should I rewrite it in order to work correctly please? SELECT DISTINCT user.id, ...
2
votes
2answers
230 views

Get table name from union query?

This is my query SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 union SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 ...
1
vote
1answer
23 views

Are Blob Sizes Fixed?

I'm using sqlalchemy to store objects in a database. The kind of object I'm creating is unknown until run time. I could be storing something as simple as a string. I could be storing a jpg. I ...
0
votes
1answer
26 views

MySQL select data over time that matches pattern over several rows

We currently log data from our plants running remotely for diagnostic purposes on MySQL. I have been asked to come up with a typical running profile for several variables. For this, I would require ...
0
votes
1answer
64 views

Why does mysql give different result for this summations? [duplicate]

Can someone help understand the following behavior? mysql> select (1/3 + 1/3 + 1/3); +-------------------+ | (1/3 + 1/3 + 1/3) | +-------------------+ | 1.0000 | +-------------------+ ...
-1
votes
1answer
47 views

Select latest record for each ID

I have three tables that contains data as below: Users Id Name Other_Columns --------------------------- 1 John Blah 2 Ricky Blah 3 Stella Blah 4 Bob Blah Saldo Id ...
0
votes
0answers
35 views

mysql select latest per group using 2 different tables along with self join

I have a query SQL Fiddle which contains 2 tables tbl_data CREATE TABLE tbl_data ( id int(10) unsigned NOT NULL, type varchar(32) NOT NULL DEFAULT '', title varchar(255) NOT NULL DEFAULT ...
0
votes
1answer
41 views

How to generate a sequence in mysql

Consider this table in mysql create table numbers (number int); insert into numbers values (3), (2), (9); select * from numbers; +--------+ | number | +--------+ | 3 | | 2 | | 9 | ...
0
votes
2answers
24 views

mysql combine tables

I have three tables which has the same schema. all of them has 10000000+ rows and has unique data as well as duplicate keys but with a datetime column can differentiate them and get the newest row. I ...
0
votes
0answers
13 views

Query from database a set of infomation and sorting them out while mataining its structure

I am currently using mysql 5.5.32. Below is my database design. It is used for storing student infomation, however each student have 61 "entries" or more(some schools might have more options), eg. ...
0
votes
1answer
43 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
1answer
35 views

How can I transfer Mysql data to another computer and add it to an exist database

I'm trying to transfer Mysql data to another computer and add it to an exist database Here is the condition: 1. Computer A, A has a database named 'Test'; 2. Computer B, B has a database named ...
2
votes
1answer
138 views

Difference data between adjacent rows

I have a table (called Visits) with ReqTime DATETIME NOT NULL column AND Duration INT UNSIGNED column. Duration should be set to the difference of ReqTime for two adjacent rows. But Duration is not ...
1
vote
1answer
72 views

Query does not use index (according to EXPLAIN SELECT)

Can anyone give me an indication, why this query does not use the Key on the table 'gtg'): ...
2
votes
0answers
35 views

Emulating WITH clause in MySQL/query perfomance review

I have never really worked with MySQL queries till this last week. I was tasked with making a simple tool for my department that tracks contacts. Here are the 2 tables and the type of data they ...
2
votes
3answers
231 views

Can I use PHP function strtotime() in SQL queries?

I have this table, and I need to use a PHP function in the query. id time lastTime 1 08:02 2 08:04 3 08:30 I am using this query with the strtotime(), and it's not working: ...