MySQL : Open-Source, Relational Database Management System
0
votes
0answers
5 views
Embedded select with rank
I am having the hardest time following the tutorials on this site regarding ranking with MySQL. I have a larger problem than others seem to show.
My table is constantly updating with single answers ...
0
votes
1answer
18 views
Optimize multiple self-JOINs or redesign DB?
I'm looking for advice on either optimizing multiple self-joins, or a better table/DB design.
One of the tables looks as follows (relevant cols only):
CREATE TABLE IF NOT EXISTS CountryData (
...
0
votes
0answers
33 views
Am I wrong in table design or wrong in selected index when made the table?
I've build web application as a tool to eliminate unnecessary data in peoples table, this application mainly to filter all data of peoples who valid to get an election rights. At first, it wasn't a ...
0
votes
1answer
12 views
HOW to “SUM” and “JOIN” for single query of a poll results?
I have a poll database as
CREATE TABLE poll_answer
answer_id,
answer varchar(255),
poll_id int(11),
FOREIGN KEY(poll_id) REFERENCES polls(poll_id) ON DELETE CASCADE,
PRIMARY KEY(answer_id)
) ...
1
vote
1answer
46 views
Retrieving 10 records from multiple tables
I have two tables where I store post data. What I want to do is to get 10 records from those two tables. Table 1 - all posts, Table 2 posts that user read.
There are some scenarios;
User has never ...
0
votes
1answer
16 views
Grant access to a table to all users
Is it possible to assign a grant to a table for all users, or a default permission so that when new users are created they will have the specific grants for that table to SELECT, UPDATE, INSERT and ...
-1
votes
2answers
46 views
Help my database isn't performing fast enough! 100M Merge with 6M need < 1 hour!
I have a server right now receiving more raw data files in 1 hour then I can upsert (insert -> merge) in an hour.
I have a table with 100M (rounded up) rows. Table is currently MyISAM. The table has ...
0
votes
0answers
33 views
Replicate database without Internet connection
I'm working on a company where I have a central DB and several agency databases. See the diagram at here. All the databases are identical, just the data can change and in some cases. I've no internet ...
0
votes
0answers
18 views
Slow queries after “too many connections” errors
Couple of days ago I had a persistent query running on my website which was taking minutes to complete (I've run it about 3 time)
It nearly killed my database, I got 1040 too many connections when I ...
0
votes
2answers
23 views
How mysql process where condition
I have a query like
Select * from tbl1
WHERE
( price > 10
AND qty > 200
AND condition=1
AND name like '%abc%')
My question is how MySQL will process this? My concern is that I want ...
1
vote
1answer
46 views
How to solve “The table … is full” with “innodb_file_per_table”?
I have a MySQL database that holds a large amount of data (100-200GB - a bunch of scientific measurements). The vast majority of the data is stored in one table Sample. Now I'm creating a slave ...
0
votes
2answers
27 views
Use timestamp(or datetime) as part of primary key (or part of clustered index)
I use following query frequently:
SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp
I would like to optimize ...
0
votes
4answers
62 views
MySql Order by isnull() Performance Problem
my sql below is use for listing stock added from 10 days ago.
Order by isnull(Price) is use so that stock without any price yet will still being listed.
AddDate and Price has an index.
SELECT Id, ...
0
votes
0answers
28 views
How should I arrange a database replication for my site?
Here is my problem. I have a busy Drupal site struggling under high load. After applying all caches I see that database is the bottleneck.
I have two servers to handle the site: A and B, on the same ...
1
vote
1answer
53 views
Fast query, in theory using index, still in the slow-query log
I'm having a hard time interpreting the EXPLAIN results of these queries. They both end up in the slow-query log, but the execution time is ~0.0050ms and the final result set is always under 100 rows.
...
0
votes
2answers
30 views
Is there any way to force MySQL use Hash Join instead of Nested Loop Join?
According to the document MySQL Explain Output format, MySQL resolves all joins using a nested-loop join method. Is there any way to force MySQL to use a hash join?
-3
votes
0answers
22 views
Database design for railway reservation management [closed]
Dear Ladies/Gentleman,
I want to create database for railway reservation management as a part of project of final year. can anyone help me?
thanks
0
votes
1answer
28 views
MySQL 5.5.21 ON UPDATE CURRENT_TIMESTAMP incorrect
As the title says, I'm having a few issues with 'ON UPDATE CURRENT_TIMESTAMP'. Here is the SQL definition for the table:
CREATE TABLE `judgements` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
...
0
votes
0answers
25 views
I can't start Mysql 5.6 server due to “TIMESTAMP with implicit DEFAULT value is deprecated” Error? [closed]
Ok, Here is my story, I went to mysql.com site & downloaded the file mysql-5.6.11-winx64.zip into C:, then I unziped it. Then I went to bin folder & started the server by using this command:
...
-1
votes
0answers
24 views
MySQL UPDATE .. WHERE IN .. RAND() .. LIMIT [closed]
As seen on Stack Overflow.
Ok, that's a weird query, i know:
UPDATE `entries`
SET `winner` = 1
WHERE `subscriber_id` IN (19, 128, 127, 125, 150)
ORDER BY RAND()
LIMIT 3
subscriber_ids are ...
0
votes
0answers
10 views
How implement MySQL unique index with two foreign key that one can be null?
I would know if can be declared in MySQl to create an index constraints in a table with two foreign key that one can be null.
For example cannot be repeated a null foreign key with the other foreign ...
0
votes
1answer
36 views
Is index on foreign key used? (No difference in EXPLAIN.)
Essentially, I am joining two MyISAM tables and have found that the EXPLAIN output for the query does not change regardless of whether I have an index on the foreign key in the parent table. I'm not ...
0
votes
2answers
36 views
database structure - complicated requirements
I have a project to build a website but it's complicated and I'm having trouble figuring out what the best way to build the database would be to handle these particular requirements.
The site is for ...
0
votes
1answer
27 views
Is there any manual for the visual explain in mysql? The output plan is unreadable
Just like this example, I cannot even tell the join ordering from it. And what does "ALL" and "ref" in the table mean? Is there any manual about the visual explain?
0
votes
0answers
32 views
Using something better than UNION for combining multiple queries
I have one big query which is based on 12 smaller queries that I have put together using UNION.
A relative simple example of my query:
(SELECT
'type1' AS 'type',
fieldOne AS someField,
...
0
votes
0answers
9 views
Problem with close connection [migrated]
I'm using myBatis on JBoss, transaction was managed by JBoss (MANAGED in myBatis config).
I'm using EJB too. Every time, when is EJB used is new database connection created and after while database ...
1
vote
1answer
42 views
Using Workbench to export. Views no longer work
I'm using Workbench 5.2 from my production DB machine (Ubuntu Linux). I've been trying to export. First try.. I had the entire schema selected including all tables and views in the right hand ...
0
votes
0answers
18 views
MySQL+Web-App Performance Issues using different servers
We are having a performance issue with our MySQL servers that does not make any sense. I have read countless articles from many people (mostly Percona) and have made my.cnf tweaks. We have even manage ...
0
votes
1answer
34 views
Bit column vs multiple join tables
I asked a question earlier about how I could define a many to many relationship between users and FOSes (fields of study). My original question was how could we know whether the user was the ...
0
votes
1answer
37 views
How to debug a db memory-leak causing mysql to go before it's own limits?
We are having a problem with one of the database server of one application, possibly caused by some code that is creating a problem in the way Mysql manage it's memory.
Until the second week of ...
2
votes
1answer
36 views
MySQL Subquery hangs / terrible perfomance
I am using MySQL 5.5.16 @ CentOS.
Please have a look at this query:
SELECT
inventory_ID,
(
SELECT
price_brutto
FROM
_inventory_prices
...
0
votes
0answers
17 views
MySQL GIS - point in polygon storedproc too slow
My server is on a shared host which is running MySql version 5.5.30-30.2 - Percona Server (GPL), Release rel30.2, Revision 509 (taken from phpmyadmin).
I have a table containing 10,023 records ...
0
votes
1answer
21 views
updation of a column after expire of specific time in mysql
i have a table where i have a column named 'state'(int(2)) and 'modify_time' (time_stamp on update current timestamp).
i have an update trigger which changes the value of state column to 0 based on ...
0
votes
2answers
43 views
Two nullable columns one required to have value
No-Explanation Question:
Is there anyway to have a constrain of 2 null values that always requires 1 to have value ? For example two date columns both null but having at least 1 that requires to have ...
1
vote
1answer
18 views
Executing local scripts against remote server?
I am in a situation where I would like to execute a number of sql scripts that are on my local machine against a remote MySQL instance. I found this question which seems to point me in the right ...
0
votes
0answers
27 views
What's the best database for analysis/research applications, i.e. involving complex queries but few transactions? [closed]
I'd like to start a discussion on what people think what the best database environment (MySQL, MS SQL, PostgreSQL, ...) is for data analysis and research applications like encountered in empirical ...
0
votes
0answers
12 views
MySQL - Faster after reload?
I'm running MySQL 5.1.41 (innoDB) on Ubuntu 12.10.
I noticed that the MySQL performance will be better immediately after I restart MySQL.
This smells like something bad, so I'm wondering what could ...
-1
votes
0answers
16 views
Accessing a NAS using MySQL/Etc [closed]
Probably an easy question, but I want to make a webapp to sort of View/Sort/Etc... Movies/Music/Files on a NAS (It's a Synology Station on a Home Network). There shouldn't be any reason a database ran ...
2
votes
1answer
24 views
Is there any way to get the optimization time in MySQL?
I'd like to see the time optimizer takes to optimize a query. Is there any way to get that time?
0
votes
1answer
37 views
Changing primary key from varchar to int
I am having user table where i am using varchar as primary key.
Now from different place I got the performance of int is better over varchar for priary key.
So i would like to change my primary key ...
0
votes
0answers
25 views
MySQL asynchronous multi master replication conflict resolution
I have setup Multi Master replication, following this blog thread:
http://0wned.it/geek-bits/guides/mysql-multi-master-replication/
And now it synchronizes perfectly, up until the moment the ...
1
vote
1answer
27 views
Like with the results of a subquery (multiple values)
What I'm trying to do is the following concept:
SELECT field1,field2,field3
FROM table1
where field3 like '%'+(SELECT distinct field from table2)+'%'
But this is not allowed because like does not ...
1
vote
0answers
42 views
Can't get rid of filesort on joined query sorting on second table
For some reason, this query:
EXPLAIN SELECT * FROM biz as b INNER JOIN listings as l ON b.id = l.biz_id
WHERE l.pub_id = 14 AND b.cat_id=310 ORDER BY l.level DESC, l.random DESC LIMIT 5;
says it ...
0
votes
1answer
17 views
UTF8 vs ASCII or custom binary format: high-performance optimization on very big tables
The summary of my question is whether there are advantages, even if minimal, to use ASCII, or even a smaller format designed yet created specifically for the situation, instead of UTF-8 for strings.
...
3
votes
1answer
158 views
+50
Relation to original tables or to existing linking table
In my database I have a table with different settings for my app. Each setting is in relation to a guest (table guests) and an event (table events). So basically each guest has specific settings for ...
0
votes
1answer
22 views
MySQL cannot create foreign key, errors 1005, 150
I'm trying to add a foreign key to a MySQL InnoDB table, linking a BIGINT with a table within the same database. I'm using MySQL Workbench to do this. It generates a script:
ALTER TABLE ...
2
votes
1answer
22 views
MySQL error 2006 MySQL server has gone away after upgrade to 5.6.10 using homebrew on Max OS X 10.8.3
I upgraded my installation of MySQL on my Mac OS X 10.8.3 using homebrew
brew install mysql
Everything seemed to go smoothly, but...not really, as it turns out.
I cannot add a new user (neither ...
0
votes
1answer
36 views
Multiple junction tables?
I am working on a system where you have users, and you have Fields of Study or 'fos' for short. Each user can have many fos and each fos can have many users. So we are dealing with a many to many ...
0
votes
1answer
16 views
MySql - set my.cnf location at server start
I want to have two MySql servers running on the same system (for master-slave replication tests purposes).
To be able to do this I need both servers read different my.cnf files.
As I see at startup ...
0
votes
0answers
25 views
MySql Database Design for Bollywood Movies (India) [closed]
I'm trying to get my head round this mind boggling stuff they call Database Design without much success, so I'll try to illustrate my problem with an example.
I am using MySQL and here is my ...
