MySQL : Open-Source, Relational Database Management System

learn more… | top users | synonyms

0
votes
0answers
7 views

Will a covering index save a table hit if only the commoly-used length of a `text` field is indexed?

I would like to add a covering index for a table that contains a text field. The maximum size of the field that is in use is ~2000 ASCII characters, 95% is below 800 characters. I am considering ...
1
vote
0answers
18 views

Grouping results with left and inner join with zero counts

I have a query like SELECT `campaign_question_options`.`text`, COUNT(`campaign_submission_answers`.`answer`) as `count` FROM `campaign_questions` INNER JOIN ...
0
votes
1answer
262 views

How to organize the tables of a MySQL database with several statistics data of several years?

I am developing an app that must provide statistics information about the distribution of the money that a person spends each year in taxes in the city where he lives. The user should select the ...
3
votes
2answers
147 views

Best Indexing Strategy for Query with Equality[A], Range[B], Group By[C], AND Order By[count(P)]

I have a poorly performing query: SELECT user_id, count(item_id) as count FROM table items WHERE category = 'magazine' AND created_at > 1384754400 GROUP BY user_id ORDER BY count(item_id) desc ...
0
votes
0answers
25 views

mysql trigger, let trigger recalculate throug whole table after correction of errors

Raised this question before but no answer found I have one table (table1) with energy data updated every 5 min I am using a trigger to calculate day values on every update and fill another table ...
0
votes
0answers
27 views

Performance issue after reorganizing table in MYSQL

There is MYSQL(5.6.22-log Community Edition) database environment which has databases size varies between 1-40GB and using INNODB storage engine, it's serves for the several number of OLTP based ...
0
votes
1answer
18 views

Reusing covering index for fields in FROM, from another query's index

For the following query I have a covering index as so: SELECT bar_id FROM foo WHERE user_id=:user_id AND date_sent=:date_sent; ALTER TABLE foo ADD INDEX (user_id, date_sent, bar_id); Now I have ...
0
votes
1answer
23 views

Would we see a performance increase by moving one table to another physical drive?

We have a database of about 600MiB. One table is particularly large and occupies about 70% of that space. We quite often have to query this table - sometimes as far back as a few years, so we can't ...
0
votes
1answer
262 views

Mysql query is very slow

I am developing a website like any other social networking site using mysql. I wish it give people suggestion to my users, and I have implemented this functionality in my application, but It is ...
-3
votes
1answer
30 views

How to check table [on hold]

How to check which tables a user have access to on a server?
0
votes
0answers
22 views

How can I optimize a query with multiple left joins?

The working example that I have been provided with takes about 20 minutes to fully process. http://paste2.org/G8BZvNHe What are some basic strategies for making this more efficient? Edit: I've ...
0
votes
1answer
1k views

How can I get Performance Statistics to work in MySQL Workbench?

I have a complex query that is performing in subsecond time on my laptop, while the same query takes 70 seconds on my production server under no load against an identical schema. The EXPLAIN plan is ...
0
votes
1answer
16 views

multiple table inner join didn't get expected values

Student_register table: regNo pro(HNDIT,HNDA,HNDFT) name start_day etc ... more columns Semester: regNo curSemmester(3,4,5) name The query I used to get values: SELECT ...
0
votes
1answer
20 views

Difficulty with nested query order & grouping

I'm trying to identify the count of objects a user updated during a given day, but only where the user was the final update. Each object can have multiple updates in a day from any user, but the ...
0
votes
1answer
111 views

getting “table is full” error even if the table size is much smaller than tablespace

Currently I am using MySQL NDBCluster 7.3.5. I have created one UNDOFILE hhme.dat with INITIAL_SIZE=200m. Also one tablespace hhmefep_tbs with DATAFILE hhmefep_tbs.dat having INITIAL_SIZE=500M ...
1
vote
1answer
67 views
+50

Clustered index performance consideration in MySQL & PostgreSQL

In MySQL/InnoDB, the clustered index is synonymous with the primary key, so picking up a poor primary key affect your db performance, i.e. use of UUID as PK is a performance killer for database write. ...
0
votes
1answer
28 views

Row size too large (> 8126)

I'm facing the following problem. Row size too large (> 8126). Changing some columns to TEXT or BLOB or using ROW_FORMAT=DYNAMIC or ROW_FORMAT=COMPRESSED may help. In current row format, ...
0
votes
1answer
142 views

Query to get reposts from people the user is following

I have a table posts where all the posts by users are stored, the structure of this table is as follows. | post_id | post_user | post_content | post_date | The users table is as follows. | user_id ...
1
vote
1answer
148 views

How to export a MySQLtable with a longBLOB column to an Oracle database?

In MySQL I have a table: Select * from Dummy; Columns like below: id name salary binary_col 1 a 100 A.pdf 2 b 200 b.pdf 3 c 300 c.pdf 4 d 400 d.pdf 5 e 500 e.pdf ...
0
votes
0answers
20 views

Retrieve a list of users based on some conditions [on hold]

What am I doing wrong on this query? The main issue it's that the var_dump is returning null. global $wpdb; $users = $wpdb->get_results( $wpdb->prepare( "SELECT u.ID AS ...
0
votes
1answer
11 views

how to overcome these type of issues where null value inserted into primary key not null column in table

how to overcome these type of issues where null value inserted into primary key not null column in table
0
votes
1answer
246 views

phpmyadmin not working after mysql update

i followed this guide to update my mysql form 5.1 to 5.5. After lots and lots of command and headache, i was able to upgrade mysql. i checked the version via terminal. But now there is another ...
0
votes
1answer
100 views

Can a slave with a dynamic ip update the master

How can I setup mySQL replication in such a way that a slave on localhost with dynamic IP can update the master as well as get updates from the master which has a static IP. The slave will not be ...
0
votes
2answers
81 views

How do I find duplicate values in multiple columns over different rows?

I have a database with a table of games (some columns omitted): gameid hometeam awayteam `date` Sometimes games are added with the same home and away teams, but on a different date that is just 1 ...
0
votes
1answer
665 views

Can't change root password: “The system cannot find the file specified.”

I'm trying to change the root password in MySQL on my development machine (I've just installed MySQL, so it currently doesn't have a password), but it keeps failing with the following error message: ...
0
votes
1answer
29 views

select statement for subtype

Okay, so all I'm trying to do here is list the managers who supervised the employee that charged a customer $100 for a rental on November 1st, 1998. My issue is that I don't know how to select the ...
0
votes
1answer
265 views

MySQL select by primary key against 100M of records within 30ms

I have no expirience with MySQL and database performance tuning in general. Wondering whether it is possible to perform a large number of selects against 100 millions of records by primary key within ...
0
votes
2answers
224 views

Mysql trigger not working, syntax issues

I have this trigger CREATE TRIGGER checkcollision AFTER UPDATE ON players BEGIN SELECT RAISE(ABORT, 'collision') FROM walls WHERE NEW.x=x AND NEW.y=y; END; mysql 5.1.72-0ubuntu0.10.04.1 ...
0
votes
1answer
11 views

MySQL - Migrating from Master-Slave replication to Master-Master

We currently run 2 large mysql master-slave replication trees. Each tree is built with one master, several 'middle' slaves replicating from the master, and 'end' slaves replicating from the 'middle' ...
-1
votes
1answer
259 views

MySQL stress test tool, remote server preferred

For MySQL stress test there exists programs: http://dev.mysql.com/doc/mysqltest/2.0/en/test-programs.html Also mysqlslap exists: http://dev.mysql.com/doc/refman/5.5/en/mysqlslap.html Is it possible ...
0
votes
1answer
7 views

Alter doesn't show in processlist

I'm performing an alter. It's currently running on a large table (300,000,000). MariaDB [my_database]> ALTER TABLE my_table -> add INDEX a (x, y, z), -> add INDEX d (x); Stage: 1 of ...
0
votes
1answer
101 views

Index to fix this query MySQL

We have an extremely large forum running lots of third party software (Most of which doesn't run all that well) and we're starting to optimize things as time allows. This query keeps popping up in the ...
0
votes
0answers
5 views

looking for mysql tablespaces introduction

What would be a good place to read about the advantages of splitting data across several MySQL table spaces (as well as limitations that come along with that) ?
0
votes
1answer
426 views

MySQL Master-Master Replication not syncing

SETUP: I have a WAMP MySQL Master-Master replication setup on 2 machines with sync_binlog=1 for safety. Machine config: Windows Azure Standard 2: 2 core, 4GB RAM, Windows Server 2008, MySQL 5.6.12, ...
0
votes
1answer
106 views

How can I optimize this Cross Table COUNT() mysql query?

I am trying to cut down the retrieval speed of this MySQL query. All instances of COUNT() point to the PRIMARY key of each field. Currently this query makes my MySQL hang. It seems ridiculous as I'm ...
0
votes
1answer
301 views

Report Builder cannot publish to report server

This is currently in a cloud lab environment. We're not allowed to join any AD domains. We have a linux server (RHEL 6) running MySQL 5.3 and a Windows SQL Server 2012 on Win 2k8 R2. Our laptop is ...
1
vote
2answers
158 views

MYSQL joining 3 tables with left join

I have these tables: pupils: (id_pupil, name, surname, email, user, pass, level, class) incidents: (id_incidents, date, time, type_incident, comments, id_pupil, id_user, subject, id_trimester) ...
2
votes
2answers
71 views

How to batch multiple insert statements for database dump with MySQL?

I have a MySQL database with some large tables. In particular, the largest table (by far) currently has ~11,000,000 rows. According to the a query I ran, the table clocks in at around 1.7GB, but it's ...
0
votes
1answer
757 views

mysql dump a table to remote host

i want to dump a table from localhost host (with public ip), here is the command so far, the command will be run on localhost mysqldump --opt --host localhost --user=username --password=password ...
0
votes
1answer
18 views

How to know if 'mysql index' are used or not in all database or table? [on hold]

I read similar topics without success :/ I'd like to know how can i determine which index are used in my database or table : DATABASE Is there any tools, or SQL to use on the database, which can give ...
-3
votes
0answers
28 views

why i am getting “Table 'DATABASENAME.INTOusers' doesn't exist” actually this table present in db [on hold]

I am trying to INSERT values from temp_table table to another table users both are present in same database i am successfully performing operations on temp_table. when ever i am trying to insert the ...
1
vote
2answers
853 views

Can't start MySQL server because of wrong file permissions?

I'm trying to start the server and then fix the root user by $ mysql -u root mysql $mysql> UPDATE user SET Password=PASSWORD('my_password') where USER='root'; $mysql> FLUSH PRIVILEGES; But ...
1
vote
1answer
192 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 ...
1
vote
1answer
332 views

MySQL slave taking too long on “Queueing master event to the relay log”

I have two mysql mysql slave servers. The DB version is: mysql> show variables like '%version%'; +-------------------------+-----------------------------------------------------+ | Variable_name ...
0
votes
1answer
80 views

MySQL update one table from another joined many-to-many

I have two tables, events and plays joined many-to-many by a third table plays_in_events. Where the events.Name field is blank, I want to set it equal to the name of the play that was performed in ...
1
vote
1answer
274 views

Mysql HY000 the table table_name is full when adding new column

I can't add a new column innodb table which size's 16MB. when I try to add, mysql says HY000 the table table_name is full. I changed my tmp_table_size and heap_size but mysql still says same error. ...
2
votes
2answers
3k 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. ...
1
vote
1answer
356 views

MySQL (or MariaDB) multi-master without MMM

I've been reading blogs on MySQL multi-master setup. The way I've used MySQL replication in the past was master-slave where if the master died (or was being taken down for maintenance) we would shut ...
0
votes
2answers
191 views

How can I optimize this query and support multiple SKUs?

My current query only can select one SKU at a time. I can leave salesite_id constant. If there is a way to also have varying salesite_ids that would be good too, but not necessary. Also any ...
0
votes
0answers
4 views

What is the way to store calculated value (CNXwindow) in same table (postcotoffbookings) of (Pricingmodel) Mysql Databse?

This is the image of calculated cnxwindow after running the query, so kindly let me know how can i store this value in the same table after calculation.