MySQL : Open-Source, Relational Database Management System
0
votes
0answers
16 views
Is there a good reason to store big text data outside database?
I was analyzing my MySQL database performance and found that tables containing text fields are just huge, larger than 1Gb.
This data is only used when loading single record (like article or blog ...
0
votes
0answers
5 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
0
votes
0answers
7 views
Creating a global temp table in MySQL
I am working on a MySQL stored procedure.
I need to create a temp table and I want to access this temp table whenever I execute this stored procedure.
I know we can't access the temp table in ...
0
votes
1answer
33 views
MySQL Workbench - question about creating FK relationships
Forgive me a little new at databases and I came across something while working with Workbench that I have a question on.
In a very simple testing database, with three tables: Company, Department, ...
0
votes
0answers
7 views
Properly index magento mysql tables to improve specific query performance
I am running magento community edition and its db performance is horrible. I have the following query:
SELECT `width_idx`.`value`, COUNT(DISTINCT width_idx.entity_id) AS `count` FROM ...
2
votes
1answer
20 views
Best strategies to have a backup of an RDS MySQL database
I have an AWS RDS MySQL database. I'm not a DBA, so my knowledge is limited.
I wish to design strategies to have zero data loss in case of a failure, in terms of replication or data backup.
I know ...
0
votes
2answers
76 views
High Mysql Load , over 700% CPU
I had high mysql load on server linux 64 bit , 24 G.B ram , Intel(R) Core(TM) i7 CPU 950 @ 3.07GHz ,
Alot of quiers in sending data mode
Here is mysql status
...
1
vote
2answers
26 views
Is it better to use AUTOCOMMIT = 0
Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like
INSERT INTO SomeTable (column1, column2) VALUES ...
2
votes
3answers
7k views
Cannot GRANT privileges as root
Because of having some problems, I decided to re-create all users except for root@localhost. This works fine, but the newly created user has no right to do anything. What I want is to simply give all ...
1
vote
1answer
549 views
MySQL 5.6 on OS X 10.8.2 disconnects on user creation?
Via Homebrew I just upgraded from MySQL 5.5.29 to 5.6.10 and all my queries and existing users are working fine, but whenever I try to modify permissions or create users now MySQL disconnects me. Are ...
2
votes
2answers
22 views
MySQL many to many relation
Sorry is this seems a stupid question, but I have a table of server addresses and a second table with a list of hardware devices which use the addresses, it is possible for multiple devices to use the ...
3
votes
1answer
18 views
Will creating a view on a MySQL replication slave break the replication?
We need to run some very ugly queries on a MySQL replication cluster.
Does anyone know if creating a view directly on the slave will break the replication of the other, otherwise untouched data ...
0
votes
2answers
39 views
TokuDB/InnoDB Question
What's the faster scheme for insert data to TokuDB or InnoDB Engine? Over 200 Insert per second.
That's okay to answer of one engine.
TABLE: `game`
`gameId` bigint 20 (PK)
`gameMapId` int 4
...
0
votes
0answers
12 views
Setting master user not working in MySQL [closed]
I am follow these instructions to set up a master user in MySQL:
Log in to your Slave server with root privilege as:
mysql -u root -p
Create a new database named magentodb and import the ...
-2
votes
0answers
14 views
How to fix error: Connection is busy with results for another hstmt (SQL-HY000) [closed]
Connection is busy with results for another hstmt (SQL-HY000)
0
votes
1answer
74 views
Second time query execution using different constants makes faster?
Can someone explain or direct me how execution on indexes happen with different constants at intervals in Mysql. I notice only for the first execution on the table it takes time, after that with ...
2
votes
1answer
13 views
Percona Node fails to join: Operation not permitted
Joining a new Percona node fails with:
130521 7:15:44 [Warning] WSREP: Failed to prepare for incremental state transfer: Local state UUID (00000000-0000-0000-0000-000000000000) does not match group ...
2
votes
1answer
149 views
Does MySQL have problems with nested insert like with subqueries in where?
Query 1:
INSERT `personal`.`locations`
SELECT DISTINCT `s`.*
FROM `references` `t`
JOIN `locations` `s` ON `first_id` = `s`.`id`
WHERE
`lat` >= 37.3
AND `lat` <= ...
0
votes
2answers
47 views
How to modify MySQL 5.5 settings that were set during the installation?
Especially the one that defines how MySQL is gonna use server's CPU. I'd like to boost performance. Also, what are other ways to boost MySQL performance? (Maybe some code optimization hints?) Anyway. ...
5
votes
1answer
150 views
+100
MySQL Hanging Completely when `ALTER TABLE… ENABLE KEYS`
I know very little about database administration but I have to deal with some very large tables on my site.
This server has 64GB of RAM and Intel Core i7-3820 (4 x 3600 MHz). Most of everything it ...
0
votes
0answers
29 views
Do certain string patterns affect query performance on a VARCHAR column?
I am a beginner on MySQL Administration and performance problems. Now this is confusing me.
I have a VARCHAR column identity, the string in it is consisted of a Class type and an id, like note:123, ...
0
votes
1answer
75 views
Looking for performance improvements for this query
This query is taking a long time to execute. We are expecting results in 0 sec, if possible.
Please help me to do any other following:
Rewrite the query
Suggest any indexes
Any other optimization ...
0
votes
1answer
19 views
ERROR 13 (HY000) When Creating Database
I'm trying to create a database and when I type in CREATE DATABASE epik; I get the error ERROR 13 (HY000): Can't get stat of './epik' (Errcode: 13) I am running kubuntu 13.04 with mysql 5.5.31.
2
votes
1answer
263 views
Select * from statement execute very slowly, innodb io read speed is low
I have a very simple query " select * from ap_statistic " running in my servers. the servers have the same hardware and software configuration (CPU 8 core, mem :32G, OS: redhat 5.5, mysql version: ...
2
votes
2answers
68 views
+50
Drop partition not freeing disk space
I am running MySql innodb with innodb_file_per_table.
As a daily script, I create a new partition for a set of tables, as well as drop yesterday's partitions. I have noticed that through the df ...
0
votes
1answer
67 views
Index optimization
I'm building a 'tag' system for my blog posts. Currently my tags table has columns tagid - MEDIUMINT NOT NULL AUTO_INCREMENT PRIMARY KEY and tag VARCHAR(30) NOT NULL UNIQUE.
And I have another table ...
0
votes
1answer
57 views
Database schema design help needed
I am developing a PHP application expecting millions of records both parent and children. My goal is to design an optimized database design to achieve high speed and performance.
This application ...
1
vote
0answers
41 views
ORDER BY optimization issue
I have a query:
SELECT
user_details.id , user_details.first_name , user_details.last_name,
user_accounts.name account_name, jtl0.account_id account_id,
user_details.title , ...
1
vote
1answer
68 views
How to recover/restore corrupted Innodb data files?
A while ago, my Windows 7 system on which a MySQL Server 5.5.31 was running crashed and corrupted the InnoDB database. The weekly backup that's available does not cover all the tables that were ...
5
votes
4answers
13k views
Trying to use MySQL Workbench with TCP/IP over SSH - failed to connect
I can't connect using TCP/IP over SSH connection in MySQL Workbench from a PC. What's going on?
I created a MySQL 5.1 database on an Ubuntu server mysql.myhost.com. I can access it locally. MySQL ...
1
vote
1answer
116 views
MySQL Stalls and stops all queries
We occassionally have our servers stall and back up queres for 3-5 minutes. then back to normal but locks up all users.
We made a new server with binlogs and log fiels on seperate disk from data. ...
1
vote
1answer
57 views
Query optimization when no row is returned
I am using explain to figure out what is happening in my query which is:
explain select t from c where u1_id = 1 group by t;
I see "Using where" in the Extra column of the resultset. But this only ...
2
votes
1answer
59 views
Problem in Fetching Result set in Desired Format
I have 2 tables having the following columns
Person
------
person_id,
first_name,
middle_name,
last_name
Items
-----
person_item_id,
person_id,
item_type,
status,
ordered_date
The query is
...
1
vote
1answer
51 views
Varchar index - will hashing value make it faster?
I have a VARCHAR(1000) column in a table. It will contain strings that will not be guaranteed to be unique. I have a query that searches this column as part of a WHERE IN clause, the list of values in ...
0
votes
1answer
33 views
Database user specified as a definer
I have a view in my database. problem is below
Error
SQL query:
SELECT *
FROM `lumiin_crm_prod`.`v_contact`
LIMIT 1 ;
MySQL said:
1449 - The user specified as a definer ('lumicrm'@'%') ...
0
votes
1answer
38 views
Index on a query with order
I have the following table with >1M rows:
CREATE TABLE `wishlist_place` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`wishlist_id` int(11) DEFAULT NULL,
`place_id` int(11) DEFAULT NULL,
`city_id` ...
0
votes
1answer
48 views
Error “#1118 - row size too large” on the first row of the table only
I ran into a problem using a mySQL database. I have some columns as type text but when I try to enter data into the first row I get the error code "#1118 - Row size too large. The maximum row size for ...
0
votes
0answers
23 views
Problems Making Configuration File Changes
I use MySQL 5.5 on both Mac OSX (10.6.8) and Windows 7; in both contexts I use Workbench for my administrative needs. I've recently started tinkering with the configuration files (my.cnf/my.ini), and ...
0
votes
1answer
33 views
How to link several tables?
I'm a newbie at Database Designing. What I want to do is perform several actions at once.
I have a main users table which holds all the data for a user at a website. Then I have a primary key as the ...
2
votes
2answers
35 views
Slow INSERTs to MEMORY table
I've been trying to work this issue out, been really killing me! We've been experiencing some slow database queries for a Prestashop installation that we host, and a look into the query log revealed ...
0
votes
2answers
43 views
Slow query joining on subqueries with max
I'm creating a report based on user input (i.e. a search tool) that grabs data from eight tables and will output them to the screen.
Here are the create table statements. I'm very aware of how badly ...
1
vote
1answer
36 views
How can I make a select statement get blocked?
Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables.
To prepare the ...
0
votes
0answers
18 views
Should store procedures be cached?
Should store procedures be cached in MySQL? If so, How long it is stay in cache?
In my case, When I call one stored procedure first time, it is giving me results in 1 second, after that it gives me ...
0
votes
1answer
59 views
Synchronise data from one table to another & vice-versa using triggers
We are migrating from one database structure to another - very slowly. The system is MySQL. There are two databases. For example's sake, we will call the databases old_db and new_db. Both databases ...
0
votes
1answer
102 views
How to get rows where count() is null?
This is my SQL request :
SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
m2_.libelleMention AS ...
0
votes
1answer
45 views
How should I set up my social network database design?
I am designing a db for a Social Network type website where users enter lot of information varying from family member details, education, employment, personal favorite such as TV, movie, music, food, ...
1
vote
1answer
30 views
How to “swap” out production tables
I have a MySQL table that holds a few thousand records which go out of date very quickly. A few times a day we run a slow process which needs to pull data from multiple sources and re-populate the ...
0
votes
1answer
43 views
Order of where clause evaluation
If I have a MySQL table which has content from the last 3 months I actually do the following:
SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where _campaign = 2
and `session` = ...
0
votes
1answer
39 views
Stored procedure with and without preparing statement
I have 2 stored procedures:
CREATE PROCEDURE `GetEntryCount`(param_Criteria VARCHAR(500))
BEGIN
IF (param_CRITERIA <> "") THEN
SET @QUERY1 = concat('SELECT user_name,count(*) as count
...
0
votes
1answer
43 views
Search speed increase
I've heard that some queries like DELETE, or changing the structure of a table can decrease the speed of the search in a table.
First: is this true , or not?
Second: is there any way to increase ...
