1
vote
2answers
23 views

How to do the complex nested select SQL?

I have two tables, table1 is as following, id is primary key, table2_id is foreign key id date aid table2_id message 1 2014-03-19 16:21:02 121 1 login from xxxx 2 ...
1
vote
2answers
32 views

SQL - query multiplying records returned - Cartesian Product

I am using the following query which is returning a huge number of multiplied records - i.e. a cartesian product: SELECT * FROM specimen, topography_index, morphology, functions WHERE ...
1
vote
1answer
16 views

How to make MySQL join one-to-many with lowest value?

I have following database structure Domain has many URL which has many positions which belongs to phrase which belongs to user which belongs to subscription. In subscriptions table I have period ...
0
votes
1answer
16 views

Want to create partitions of a MySQL table and change the charset of new partitions

I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in ...
0
votes
0answers
20 views

Multiple client tables or large overall table

I've recently taken over a project linking to a large MySQL DB that was originally designed many years ago and need some help. Currently the DB has 5 tables per client that store their users ...
0
votes
0answers
12 views

Error Code: 1133. Can't find any matching row in the user table

Im reading O'Relly "Learning MySQL" and it says that you can create a user in MySQL by doing just this: mysql> GRANT ALL ON *.* TO 'jill'@'%.invyhome.com' IDENTIFIED BY 'the_password'; Query OK, 0 ...
0
votes
0answers
15 views

Auto backup only for MySql database and selected folders in WHM or via cron job command

Hell all i have a VPS, WHM provide use auto backup feature. but i want only backup some selected database and folder. i try to use command via cron job but its not working. current code (not ...
0
votes
1answer
8 views

MYSQL testing string field contains another altered string field

I have this table : mysql> desc mytab; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
0answers
17 views

Select changes in table by date

I have following table schema name type id int key string count int date date url_id id with example data id key count date url_id 1 system 100 ...
0
votes
1answer
17 views

Insert pictures

I need to create dictionary with pictures. I created database of words in mysql I tried to collect pictures of each word. How can insert pictures into tables?
0
votes
0answers
19 views

Installing database

I have created database in my system using mysql.I tried to use in one of my friend's system.How can install the database in other system?
0
votes
0answers
17 views

eCommerce product table design - MySQL

I am designing database for an web application, I have designed the product and specification details database as follows, create table pr_size ( size_id int not null auto_increment, ...
0
votes
0answers
12 views

Redesign tables, multiple foreign key and related table to same table

Hello I have this database: Table Teams is used in each one table. Match table contains teamA and teamB and round contains team that win the SET and team that start the set. But I dont know how I ...
0
votes
1answer
9 views

Get devices with not driver assigned

I have this related tables: And I need to get those devices with no driver assigned, in other words the device returned on this query shouldn't be on driver_has_device table since it'll not have ...
0
votes
0answers
37 views

Database/Storage engine suggestion for a project involving heavy inserts?

I am looking into a project which involves large number of inserts daily.I will have a list of user's (for example a user set of 500k ) , for which I need to monitor daily certain activities ...
-1
votes
0answers
19 views

C++ MySQL_Connect issue [closed]

Below is some code I wrote for a function that is supposed to always be running on its own thread checking a database for new information and appending the new information to global vectors. The issue ...
1
vote
2answers
52 views

Checking if an Username is already in use

I am writing some scripts for my website, and I am currently focussing on checking if a username is already in use. I have tried writing a SELECT script where the datafield is selected and checks if ...
0
votes
1answer
49 views

What is a good/healthy mysql query execution time?

What is a good/healthy mysql query execution time? To my calculations: The site gets 1000 unique visitors per hr 10 page view for each unique visitor 5 minutes for each unique visitor ...
0
votes
1answer
34 views

GROUP BY performance

I have a query like this: SELECT HOUR(datetime) as Hour, COUNT(*) as hits, SUM(clicks) FROM visitors_log_table WHERE datetime >= CURDATE() GROUP BY Hour WITH ROLLUP It works fine. But I ...
1
vote
3answers
73 views

Block user access to certain tables

DBMS allows the admin to grant table access to a user using something like: GRANT ALL ON mydb.mytbl1, mydb.mytbl2 TO 'someuser'@'somehost'; However, is it possible to grant all tables to a user and ...
0
votes
0answers
12 views

Checking for records in lookup table before deleting

In my MySQL database, I have products table, a locations table and a lookup table (called location_products) where the ID of the product and ID of the location are stored. I am creating a transaction ...
0
votes
2answers
37 views

“GROUP BY” in ranges?

Suppose I have a table with a numeric column (lets call it "score"). I'd like to generate a table of counts, that shows how many times scores appeared in each range. For example: score range | ...
0
votes
1answer
25 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
0answers
14 views

Specific id to appear first in result of query [duplicate]

I have a sample query where: Select gID, entID, cID, gString From tblGF Where ((entID=$entID And cID=0) Or (entID=$entID And cID=$cID)) And (gFP='BB' Or gFP='CP' Or gFP='PS' Or gID=$gID) Order ...
0
votes
0answers
14 views

Would an INNER JOIN query in a sharded relational database hit the same shard when using a compound primary key?

In this question I have asked about modulus calculation in sharded tables, this design is used: CREATE TABLE Orders (order_id INT PRIMARY KEY, ...); CREATE TABLE LineItems (order_id INT, lineitem_id ...
0
votes
0answers
8 views

Modulus calculation for compound primary keys in MySQL Cluster (and other sharded databases)

In MySQL Cluster, and generally in sharded databases, how is a compound primary key's modulus calculated? Say, for example, there are two tables that have primary keys like so: CREATE TABLE Orders ...
0
votes
0answers
19 views

Indexed column INSERT vs. UPDATE

I have an indexed column. I could give it a value on INSERT. But it would be easier if I would add this value via UPDATE later (I have to update this row anyway). But I was thinking if it would be ...
0
votes
1answer
41 views

Need help optimizing query to prevent full table scans

I have the following query, which while it works, is also doing a full scan on each left joined table (the log* tables). I thought by using a subquery in the FROM that this would eliminate the ...
0
votes
1answer
30 views

COUNT and GROUP BY performance

I have a table where I store visits (id, datetime, country, browser). I want to count how many visits are from which country. I use this query: SELECT country, COUNT(*) as hits FROM hits_table ...
0
votes
1answer
41 views

Join 3 tables returns too many results [closed]

I have 3 tables: users, posts, videos. The last 2 tables have user_id that associate with users.id (first table). The problem is that when I try to run inner join query and return all posts and ...
1
vote
1answer
30 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
0
votes
1answer
29 views

Select post and concatenating all categories it belongs to

I have 3 DB tables POSTS +----------+----------+----------+ | id | title | content | +----------+----------+----------+ CATEGORIES +----------+----------+ | id | title | ...
0
votes
1answer
22 views

two auto incrementing columns

OK I have hit an issue, now I was going to look at having two auto incrementing columns, but I cant using InnoDB. So I am after a Solution. I have a table called queue. Everything to do with my task ...
0
votes
0answers
33 views

Database design Problem for the web-app

Hello I am in a problem of designing a web-app database schema . Suppose i have 10 shops and every shop gives deals on every different day Sunday Monday and so on and for limited time period like ...
0
votes
0answers
21 views

Cohort analisis for gamers activity

I want to do some cohort analysis of gamer activity in my games. By cohort I mean some set of users who registered (or had first activity) on some particular date. For example: users registered on 16 ...
0
votes
1answer
38 views

How do I write a mysql query to combine data from related tables into a single summary?

I'm having trouble defining the data in a query for a chart. I want to summarize data from the first table and combine it with a data summary from the second table. Products - (serial_number, date) ...
-1
votes
3answers
54 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
2answers
44 views

Check and Retrieve those results where a cell(row) contains atleast one value from many values in a column [duplicate]

Let me give you an example : I have a table (name = posts) which contains following rows: id | title | .... | names 1 | . | . | . | . | . | 4 ...
0
votes
1answer
24 views

Mysql Group Concat Distinct On One Field While Returning Another

I have a group concat distinct to return a distinct set of user names (user.first_name concatenated with user.last_name with a space separator). I want the distinct to apply on the user_id and not on ...
0
votes
1answer
38 views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
-1
votes
1answer
44 views

Query to retrieve data based on several where conditions ( Not off Topic , Answer provided below is right ) [closed]

Database : Mysql 5.5 i have a table T1 containing 4 columns : No. , Height , start_time , end_time In column height i have 3 distinct values only : Small Medium High Now i ...
2
votes
2answers
205 views

Design of an application log database

We are generating logs like following example(this is a table no actual pipes): 2014-06-10 09:00:03.457 | Channel1 | Operation3 | Function15 | 15ms 2014-06-10 09:00:08.245 | Channel2 | Operation5 | ...
1
vote
1answer
32 views

Can't figure out this “trick” to optimize MySQL query

Here's an excerpt of this article: SELECT id, name, address, phone FROM customers ORDER BY name LIMIT 10 OFFSET 990; MySQL is first scanning an index then retrieving rows in the table by ...
0
votes
0answers
60 views

Using a `mysql_query` to insert category values

Ok I'm importing direct from a csv to MySQL, using a mysql_query to insert values. How do you insert category terms into the Wordpress database structure assigning them to a post. Below are two import ...
0
votes
0answers
13 views

Users_With_Messages Table normal form

I have the following tables in my database: Above these tables (not pictured) is a Users table where the SendingUserId and ReceivingUserId are keyed onto (they are foreign keys). Are these tables ...
2
votes
2answers
31 views

MySQL nested queries

I'm working on a project using a MySQL database and we'd like to combine a query which was previously made in several steps into one step. The queries are created in a Java-App using Hibernate. Goal ...
0
votes
0answers
16 views

Avoid obvious potential NULL-values [duplicate]

I've been searching through the different threads discussing NULL values and how it is good whether using it or not. I cant find any concrete examples of how to design a relational data model to avoid ...
0
votes
0answers
12 views

Securing database from intruder DUMP

What would be the best way to secure a MYSQL database that contains customer information from being dumped by say a injected SQL or Remote file inclusion? Can the DUMP function be turned off globally ...
18
votes
6answers
625 views

Count where two or more columns in a row are over a certain value [basketball, double double, triple double]

I play a basketball game which allows to output its statistics as a database file, so one can calculate statistics from it that are not implemented in the game. So far I've had no problem caluclating ...
4
votes
1answer
61 views

Find the user with closest taste of mine

I trying to write in SQL the following requirement: Each user can rate as much films as they like. (1= I don't like, 5=I love it) And the system should provide to the current user a list of films ...