MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
1answer
67 views

What would be the first things you do to find out about an MYSQL environment you are taking over?

I am new to DBA role, and wanted to know if there was any commands (or free software) that come with Mysql 5.5 (master/slave/slave) replication to quickly get familiar with the database, and also ...
1
vote
1answer
350 views

How to re-arrange rows to best fit within groups?

I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100. mixing_order id num_items cat order_in_cat cumulative_sum folder 1 ...
3
votes
1answer
144 views

Strange behaviour of the float type

I have the following table: +------------+--------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
150 views

MySQL installation and data location on Windows

I'm gonna install MySQL 5.5 in a virtual Windows Server. I'd like to have the data in an separate partition (it may be in a different virtual disk) than the system one. I'm considering two options: ...
3
votes
2answers
376 views

Why ALL PRIVILEGES ON `db`.* gives access to all databases?

I've installed Ubuntu 12.10(quantal) and mysql-server-5.5. When I execute: drop user ''@'localhost'; create database `redmine`; grant all on `redmine`.* to 'redmine'@'localhost'; User ...
0
votes
1answer
284 views

Maintain table with too many insert

I have to create a table. Its size is going to increase very fast. Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
1
vote
2answers
291 views

mysql 5 vs 5.5 memory usage

I had a less less memory usage in mysql 5 vs 5.5 do they do something drastically different? I went from around 100mb to about 1 gb of memory usage. Does memory usage in 5.5 also grow over time?
7
votes
2answers
5k views

Is it bad to create many mysql temporary tables simultaneously?

I need to make heavy statistical analysis to deliver some data to users. Currently, I catch the data from mysql and process them via PHP arrays. However, mysql temporary tables are quite better ...
2
votes
3answers
13k views

which mysql version to use - 5.1 or 5.5?

I am designing a new database. There are going to be around 1000 write queries per second. There may be as much as 10000 read per second. Which MySQL version is recommended? I came across a post on ...
1
vote
1answer
222 views

What would be a optimal index for this query in mysql

I'm trying to optimize this query. SELECT * FROM sales_reps WHERE account_id = 1 AND (state = 'enabled') AND (clients >= 5 OR revenue >= 10000) ORDER BY name, platform, client_group, ...
8
votes
1answer
6k views

Is it possible to change ENUM() lists?

I wasn't sure if changing ENUM() list isn't possible so I made a test. In MySQL v5.1.58 I made a test InnoDB table that contains one field called 'bool' of type ENUM('yes', 'no'). Then I executed... ...
1
vote
1answer
295 views

mysqldump, preserve update_time and modify_time attributes as listed by show table status from

Does mysqldump preserve the create_time and update_time attributes that are output by show table status from? If not, is there an option that does this? From the manual it looks like mysqldump ...
3
votes
2answers
1k views

Database space doesn't match ibdata1 size

I'm using InnoDB database with a single file configuration (in /var), so no innodb_file_per_table. In the MySql workbench, when I query for the databases used space, with this query SELECT ...
1
vote
1answer
465 views

import data from csv to mysql - good database structure

I have a collection of csv files with measurements with the following 54 columns: SITE (string) TIME (integer as yyyymmddhh) PROD (double) EST.0 (double) ... EST.50 (double) I want to read this to ...
0
votes
1answer
2k views

MySQL: Can't find record in tablename

After crash: mysql> repair table lot_not_sold; +------------------------+--------+----------+---------------------------------------------------------------+ | Table | Op | ...
2
votes
1answer
558 views

How to know when to scale RDS MySQL instance? How to scale it?

I'm running 1 single medium RDS instance for my websites. How do I determine when I will need to scale it up? Is there a way to scale it horizontally or I can only upgrade it to a bigger instance?
1
vote
1answer
318 views

Group activities in activity feed by users and products

On my website I'm building an activity feed where I got the basics done and are now making some improvements here and there. One of them is the grouping. I've built it so that if multiple users buys ...
1
vote
2answers
514 views

Is a Secondary Index in MySQL the Same Thing As a Non-Clustered Index in SQL Server?

I am assuming that a secondary index in MySQL is the same thing as a non-clustered index in SQL Server. Is this true?
1
vote
1answer
509 views

MYSQL MyISAM data recovery implementation

I am creating integration into eshop script, shop is using MyISAM tables and I need to implement something like transactions in InnoDB, what I actually need is full recovery of data after modifying or ...
1
vote
0answers
104 views

MS SQL to MySQL Conversion

Is there a tool available that converts queries written as triggers, stored procedures and functions written in MS SQL 2008 to MySql? We switched databases and now I need to rewrite a bunch of queries ...
1
vote
1answer
1k views

Slow MySQL query with float using BETWEEN and ORDER BY

Trying to figure out whether it's the BETWEEN being used on a float or the ORDER BY and the lack of an index that is making this query slow. First the tables used in the BETWEEN and ORDER BY: ...
1
vote
1answer
1k views

Does MySQL have a limitation of one index per query?

MySQL supports spatial index. However, all the spatial index can do is find points in a box. It can't do fancy stuff like find the nearest 1000 points, etc. I think, and I don't know where I went ...
5
votes
3answers
681 views

How can I import/export a MySQL database without redirection?

I've been told that redirection via shell (e.g. < and > ) can (not always) affect the collation of a database (.e.g utf8). How can I import an export without redirecting so that I don't have to ...
2
votes
0answers
1k views

SQL query optimization in Prestashop e-Commerce solution

I'm working on a layered navigation module within Prestashop e-Commerce solution. I'm trying to optimize a SQL query which creates a temp table during execution. I think this is creating a ...
1
vote
0answers
285 views

mysql server is not sending result set back after query execution to client and staying in sending data state

When I pass a query from my Java application to mysql server, it stays in Sending Data state and not returning result set back to application. When I execute the same query in SQLYog directly, it ...
2
votes
1answer
167 views

Can I normalize my experimental data table further?

I am attempting to get a MySQL database to 3NF, but I'm unsure as to how to get there. I'm new to database design, so please correct me if my understanding is incorrect. I have 6 columns: ...
3
votes
1answer
1k views

How can I find out the last time a MySQL table was analyzed?

I'm interested in this for InnoDB mostly, but also for MyISAM. I looked around in information_schema and did not see this data anywhere. I'm using MySQL 5.5.16.
10
votes
2answers
15k views

Why is MySQL is creating so many temporary tables on disk?

Can any configuration mistake lead to creating too many temp tables by mysql..mysql tuner shows Current max_heap_table_size = 200 M Current tmp_table_size = 200 M Of 17158 temp tables, 30% were ...
5
votes
1answer
3k views

Is there a MySQL engine or trick to avoid writing so many temp tables to disk?

I've got a web application connected to a database running MySQL. Here's the problem: a not insignificant number of queries create temporary tables and many of the tables involved have BLOB fields. ...
1
vote
1answer
602 views

What is the best and most performant MySQL cloud solution?

I'm looking for a solid solution (~1GB of DB space). Speed is crucial and I'm happy to spend up to $100 a month on a decent solution. The database will be used by a series of desktop applications ...
1
vote
1answer
231 views

MySQL SELECT query: i want show duplicated entries

Example My query is: SELECT code_CAT FROM categories WHERE id_code IN(14,14,14,14); output is: 53724 Only one row. Instead I want this output: 53724 53724 53724 53724 How can I achieve ...
3
votes
1answer
906 views

How to set datadir to different values based on the hostname in my.cnf?

How to set datadir to different values based on the hostname in my.cnf? I want to use the same my.cnf file on a Linux machine and a Mac OS X machine where the datadir should have different values.
1
vote
1answer
1k views

does last_insert_id() returns correct result when writes from multiple sessions?

I have a table in which there is a combination of 4 columns that will act as a unique key. Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on ...
3
votes
1answer
1k views

mysql statement based replication - unsafe statements

I have recently set up statement based replication and my error logs are filling up with this error 121231 21:10:55 [Warning] Unsafe statement written to the binary log using statement format ...
2
votes
2answers
2k views

mysql replication: replicate all but one table to different database on different server

I got database1 on server1 and database2 on server2. Now I need to replicate a single table from database1 to database2 Now I thought that this would work: replicate-ignore-table = % ...
5
votes
1answer
1k views

Query not using index when it should

I have this query (below) which keeps hitting the table when it should be using the created index. Is there something simple I'm missing here? How can I get it to use the index instead of the table? ...
2
votes
3answers
4k views

Automatic MySQL Replication Fail-over Strategy

I want to know that if replication is down due to some reason as if master is down then what strategy should be followed so that the replication is up instantly without manually intervening the ...
4
votes
2answers
5k views

Why max_allowed_packet is larger in mysqldump than mysqld in my.cnf?

If you look at the sample config such as /usr/share/mysql/my-large.cnf, you will find.. [mysqld] max_allowed_packet = 1M [mysqldump] max_allowed_packet = 16M What is the point? Because when you ...
1
vote
1answer
203 views

Performance impact of including result column in indexed columns

I have a database (in MySQL, using InnoDB) that I am using for texture identification in a 3D image. In it, there will be a table of texels(texture pixels) with hue included, in the form of: texelid ...
3
votes
0answers
390 views

XtraDB Cluster Error : “IST first seqno 7 not found from cache, falling back to SST” in replication [closed]

I have a problem with MySQL clustering. My systems consist of: xtrabackup: version 2.0.2 MySQL: version 5.5.27 Distro: CentOS 5.7 64-bit my.cnf on the donor: [mysqld] binlog_format=ROW ...
1
vote
1answer
117 views

After piping a file with SQL statements to mysql, how can I keep the command line session open?

A file, in.sql, contains a CREATE TEMPORARY TABLE statement followed by some INSERTs that populate the above table. Temporary tables are purged after the session closes, so 'mysql -u user -p pass < ...
0
votes
1answer
181 views

How to number SQL results sequentially?

Given following query - SELECT name, age FROM students WHERE age > 22 The result is - name age ---- --- Kshitiz 23 Steve 22 Paul 24 How can I number the results sequentially? Like ...
2
votes
2answers
113 views

Optimizing index creation

One thing that has bothered me for awhile is creating many many indexes for the one table where the query only differs slightly or contains many of the same columns. An example case would be: table1 ...
3
votes
1answer
492 views

How to design sales model for escrow type payment process

I need advice for best sales model for payment process. Basically my system is kind of market place. There is two kind of transaction. Customer will pay full money to administrator. Administrator ...
3
votes
1answer
3k views

MySQL Replication and High Availability

I know this might not be a very good question to be asked in this site, but I really need some helps/hints to be directed to the right path. I want to have failover strategy for 2 MySQL servers which ...
3
votes
1answer
59 views

Model to handle music release artwork

I’m designing a FAN web page for one artist, more specifically, the discography section. The discography includes this entire artist CD’s, Vinyls, 8-tracks, cassette, etc. So far, I’ve build something ...
1
vote
1answer
315 views

Giving a name to a dynamic column

I have written a query to fetch a time internal: select *, (Time + interval '2012-12-18 13:36:47' minute) from chatnotiservice; Is it possible to name the (Time + interval '2012-12-18 13:36:47' ...
4
votes
1answer
139 views

help required with indexing and query optimzation

After browsing most of the posts and after running out for options I am posting here. I have a table with 76,222,954 (76M) rows. It collects web site traffic based on country. Here is my table ...
1
vote
3answers
434 views

Design for archiving a sorted list (with ability for a few INSERT)

I have a somewhat static sorted list of words which I want to save in a database table: -- simplified! -- Note: the rank/order of each item is NOT calculable from other values! CREATE table words ( ...
5
votes
1answer
273 views

Is it possible to determine mysql pre-allocated memory footprint

At startup, mysql will use some memory. Some of the memory is pre-allocated for "key_buffer" and "innodb_buffer_pool_size" ( and other things ). Is it possible to determine how much memory is ...