MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
3answers
2k views

Running an “ORDER BY CASE” query on MySQL view breaks it afterwards

Whenever I create a MySQL view and then run a query ordering the results with ORDER BY CASE WHEN (...), the view becomes unusable. The newly created view works properly and all SQL is correct. This ...
0
votes
2answers
119 views

high traffic mysql hosts

I'm wondering if any of you sql buffs out there have any suggestions for a high speed mySQL server. I need something that can handle roughly 33 updates and 33 queries per second, per user. and for ...
1
vote
1answer
845 views

MySQL: Filter results by joining a vertical table

Situation: I've got a table users and a table preferences along with some others. The table users is aligned horizontally (id, username,...) whereas the table preferences is aligned vertically ...
0
votes
1answer
53 views

Execution plan for select with empty value using wildcards

Short question: with this: foo like '%%', MySQL go through all records or through none? If you are thinking why i need this, it's because i'm concatenating a search string and if this have no ...
0
votes
1answer
131 views

Combine results from two queries?

I need to combine the results from two queries into one result set and order them altogether. This is the working, raw SQL code I have: SELECT * FROM ( SELECT t1.id AS entity_id, ...
0
votes
1answer
36 views

Sorting by a field in child table

There are two tables: contract (with primary key contractid) and contractupdate (with field contractid referring to a contract). contractupdate also has DATE field startdate and TINYINT field day. I ...
0
votes
1answer
86 views

How to check how the table structure effects the performance of my query

I want to check how the table structure of a certain table effects the performance of a delete query. This is not a generalised question, I have a table, with currently 2.2 million records ...
0
votes
1answer
647 views

Getting the top count of records from a table for each month and user

I have a table where translations are stored along with the user_id and when the translation was added. I want to create a query that shows the 'Translator of the month' (the person who did the most ...
0
votes
1answer
72 views

Are databases only growing in size?

Databases use the file system to store the data. As far as I know it is not possible to delete a record from a random access file. So does that mean that when we do a DELETE FROM TABLE the size of the ...
3
votes
2answers
361 views

Make MySQL server terminate when shell exits or on ctrl-c

If I run mysqld, ie invoke it directly on the command-line, it survives the shell being closed or hitting ctrl-c. Is there any way to run it "interactively", ie so that it will shut down if either of ...
0
votes
0answers
39 views

MySql Report Using CASE THEN WHEN

I am getting a syntax error on the code below. I wish to substitute the report text for each column clarifying the its meaning. SELECT CONCAT(LastName, ', ', FirstName) AS "System Administrator", ...
0
votes
2answers
99 views

Stored Procedure - MySQL

I am developing a database for an application. I have to create stored procedure to tables, so that programmers can simply call the stored procedure to implement the functions. I have a doubt to ...
0
votes
1answer
345 views

Update the host field in Grant privileges in mysql

I have an existing table and I want to update host part in privileges using Grant command mysql> show grants -> ; ...
0
votes
2answers
202 views

Which is better? A join or a SELECT IN? [closed]

I have a set of ids that I extract using a join of 2 tables. Now I need some information from 2 other tables. What would normally be the best approach? So a SELECT IN using the set of ids I already ...
0
votes
0answers
60 views

joining two tables and counting joined rows

table 1 : categories -------------------------------------------------------------- cat_id | cat_title -------------------------------------------------------------- 1 | sports 2 | news ...
0
votes
0answers
60 views

Is there a syntax to access the table name in the select query?

In MYSQL. I want to select the records which has the same contents with the table name. for example, consider the following table. <TABLE : tbl_1> +-------+------+ | id | no | ...
0
votes
0answers
72 views

Optimizing group by <date> div <const> queries (MySQL)

I have a query that produces graph data from SQL. The div is to group the numbers into fix dates so it will draw a nice graph (the const is determined beforehand to try and divide the data into ~100 ...
0
votes
1answer
234 views

MySql - is possible that performance issues are caused by dynamic SQL?

We have this MySQL SP, which uses dynamic SQL. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it uses dynamic SQL? Can dynamic SQL cause ...
0
votes
2answers
233 views

Binary Log Format in MySQL

Reference manual for MySQL 5.6 states that " Some changes, however, still use the statement-based format. Examples include all DDL (data definition language) statements such as CREATE TABLE, ALTER ...
1
vote
1answer
87 views

How is the number of records on a select determined as high or low by optimizer?

What is the threshold between using the index and doing an direct access to the database? I assume this decision is related to the number of index access (and subsequent access to the tables). If this ...
1
vote
1answer
78 views

Mysql with index [closed]

I have this following sentence: SELECT Day(C.DataCadastro) AS Dia, Month(C.DataCadastro) AS Mes, Year(C.DataCadastro) AS Ano, COUNT(I.Plano) AS Total FROM clientes AS C ...
0
votes
1answer
287 views

What performance should I expect from MySQL (Using Amazon RDS)

I'm trying to understand whether I'm doing something wrong, or expecting too much. I have an InnoDB table on a db.m1.large RDS (MySQL 5.6.13. 7.5 GB RAM) with ~1M rows. The primary key is a ...
0
votes
1answer
24 views

Basic metric information for views

I would like to output some basic metric information about my MySQL database. Such as the names and number of rows for all tables and views in a given database. Tables are no problem as I simply ...
0
votes
1answer
98 views

Can MySql nested SP be a bottleneck?

We have this MySQL SP, which calls a nested SP. It seems it does NOT perform well under load. It is possible that this SP becomes slow under load because it calls a nested SP and uses temporary ...
0
votes
1answer
194 views

Optimizing multiple MySQL sub-queries and joins for multiple runs

The query (below) that's running is taking ~0.6 sec which normally would be fine but it gets run almost 1300 times for different custids. How can I make it run faster? Setup: Mac OS X 10.7.5 MySQL ...
0
votes
0answers
129 views

How much RAM do I need?

Let's assume a query like the following as typical: select A.`date` as `date`, count(*) as N from A as A join B as B on B.id = A.b_id join C as C on C.id = B.c_id join D as D on D.id = ...
0
votes
0answers
83 views

MySQL overtaking system memory and forced to restart

I'm facing a frustrating issue. I have a MySQL Server 5.5 running on top of Ubuntu Server 12.04.3 LTS (VMWare guest w/ 16GB RAM - 8 Cores) mysqltuner.pl says [OK] Maximum possible memory usage: ...
1
vote
1answer
139 views

Check all MySQL tables at once

I'd like to run a CHECK TABLE on all my tables from the MySQL command-line. I can do it for a single table: mysql> check table Restaurant; +--------------------+-------+----------+----------+ | ...
0
votes
1answer
811 views

SQL Query is sometimes slow, sometimes fast

I have two hosts, one with my rails app, one with my MySQL database. Going through my rails Logfile I can see that sometimes queries take much more time than the same query at other times. Think 200ms ...
1
vote
1answer
62 views

Accessing MySQL Database

I am designing a database for an application, which will be accessed by different front end programming languages, such as php, asp.net, android applications and iphone applications. I am confused, ...
1
vote
0answers
137 views

How to use variables in file paths in MySQL statements?

I have now a problem that I have to set paths through variables in queries like load data infile '/my/file/path/to/file.txt' into table database1.table2 fields terminated by '' lines terminated ...
0
votes
1answer
650 views

Query: select all rooms which are not booked

I have a simple room booking system consisting of following tables: booking | id | user | begin | end | room | id | name | booking_map | id | room_id | I can list all rooms that are booked at ...
0
votes
2answers
424 views

Importing MySQL database using Linux Terminal

I have an existing database, named dlti.sql I want to transfer it to a Linux-based server. I tried using this code, however I get no response: mysql -u root -p Desktop < dlti.sql The .sql file ...
2
votes
2answers
4k views

How do I add a MySQL linked server to SQL Server?

I am trying to add a linked server on SQL Server to MySQL Server. Note the SQL Server is installed on a separate machine with a different domain name. The SQL Server is 10.0.1.1 on old_domain.com. ...
2
votes
0answers
97 views

Understanding Indexes and using them better

I've been spending some time trying to optimise our MySQL queries that we use which essentially joins one table with 2 million rows onto another which has 1-2 million. I know MySQL is able to easily ...
0
votes
1answer
620 views

MySQL select query result set changes based on column order

I have a drupal 7 site using the Views module to back-end site content search results. The same query with the same dataset returns different results from MySQL 5.5.28 to MySQL 5.6.14. The results ...
1
vote
1answer
237 views

Squeezing Maximum Out Of My Server

I have a dedicated server only built to hold MySQL databases. Currently I have a database that has 173 million records and increasing by 5-15+ million records very day. My question is what and why I ...
3
votes
2answers
3k views

MySQL temporary column in SELECT statement

I'd like to do some calculations in a MySQL query. I've created some temporary columns which I'd like to use for the calculation. SQL fiddle for currently working (but discouraged) SQL query: ...
1
vote
1answer
418 views

“copy to tmp table” for 24 hours, then timeout; when ALTERing to add PK & AI

I downloaded the Wiktionary XML dump, and ran mwdumper.jar to insert the data into MySQL. Following the advice in the manual, I commented out the PRIMARY KEY AUTO_INCREMENT part of the CREATE TABLE ...
0
votes
1answer
345 views

MySQL joining two tables and counting based on conditions

table 1 > cats cat1 cat2 table 2 > sites site1 > lang1 > cat2 site2 > lang2 > cat1 site3 > lang3 > cat1 site4 > lang1 > cat1 site5 > lang2 > cat1 site6 > ...
1
vote
1answer
205 views

Innodb buffer pool indexes versus data usage

The server has 128GB of RAM. The innodb_buffer_pool_size is set to 106GB. mysql> show global variables like 'innodb_buffer_pool_size'; +-------------------------+--------------+ | Variable_name ...
0
votes
1answer
270 views

Party Relationship Model in Practice - Insertions

I'm moving this question for StackOverflow in hopes to get perspective from a more database-oriented community. Here is a basic representation of what I'm doing (in pseudo sql): Parties - ID, ...
1
vote
1answer
291 views

Mysql: Possible to increase the priority of your query on a database?

In mySQL is it possible to increase the priority of your query when competing with other users without caching your query before hand?
0
votes
0answers
63 views

Backup in Mysql periodically

I have trouble scheduling a backup in mysql periodically, most of the tools do not recognize the geometric field, for example navicat which says Can not get geometry object from data you send to ...
1
vote
0answers
797 views

Fastest way to perform simple operations on 1 million rows

I have a simple table with more than 1 million rows in an InnoDB MySQL table. Here are the steps that I need to perform on each row: SELECT * FROM tbl where work_done=0 make some data processings ...
1
vote
1answer
636 views

mysql 5.1 — unknown variable 'basedir=c:/mysql/'

I recently changed the default port(3306) to another for mysql. I did it by changing the my.ini file and did restart of the server. No other changes were made whatsoever. When connecting using a GUI ...
1
vote
1answer
27 views

Optimised query to select document and possible annotations

I have a database of small documents which users can view and annotate. Their annotations are not public. I'm looking to optimise a single query to read the document and annotations for the current ...
0
votes
2answers
89 views

Table Design Cardinality

I am designing a database for an application, in that I need to consider both BUYER (BUYER_ID) and VENDOR (VENDOR_ID) as a USER (USER_ID). I have designed this logic in a following design. USER ...
0
votes
1answer
55 views

WHAT RDBMS ( SQL SERVER/ ORACLE ) close to manage MYSQL(DBA) [closed]

i have a requirement for learning MYSQL for DBA. here all training institutes teach on ORACLE AND SQL SERVER. SQL is mandatory to work on RDBMS. what RDBMS SYNTAX CLOSE TO MYSQL for DBA(backups, ...
0
votes
1answer
58 views

Query when index is ready

To improve performance of a query I set up an in-memory table and fill it with data. Of course to increase speed I will use an index on the data stored in the in-memory table. create table helper ( ...