mysql slow query log - Page 33

How to get top 3 frequencies in MySQL?

- by Amenhotep

Hello, In MySQL I have a table called "meanings" with three columns: "person" (int), "word" (byte, 16 possible values) "meaning" (byte, 26 possible values). A person assigns one or more meanings to each word: person word meaning ------------------- 1 1 4 1 2 19 1 2 7 <-- second meaning for word 2 1 3 5 ... 1 16 2 Then another person, and so on. There will be thousands of persons. I need to find for each of the 16 words the top three meanings (with their frequencies). Something like: word 1: meaning 5 (35% of people), meaning 19 (22% of people), meaning 2 (13% of people) word 2: meaning 8 (57%), meaning 1 (18%), meaning 22 (7%) ... Is it possible to solve this with a single MySQL query? (If this problem is a classic one and has been answered elsewhere, I would appreciate if you could give me a link to the solution.) Thank you very much, ve

Read the article

MySQL Query order by numer of rows?

- by Clemens

hi, i have a mysql table for votes. there's am id, a project_id and a vote field (which is 1 if a specific project is voted). now i want to generate a ranking from those entries. is there a way to get the number of votes for each project_id and automatically sort the entries by the number of TRUE votes of a project with a single mysql query? Or do you know a php way? e.g. ID - Project ID - Vote 1 - 2 - 1 2 - 2 - 1 3 - 1 - 1 == Project Nr. 2 has 2 Votes Project Nr. 1 has 1 Vote Thanks in advance!

Read the article

SQL Query to duplicate records based on If statement

- by user328371

Hi, I'm trying to write an SQL query that will duplicate records depending on a field in another table. I am running mySQL 5. (I know duplicating records shows that the database structure is bad, but I did not design the database and am not in a position to redo it all - it's a shopp ecommerce database running on wordpress.) Each product with a particular attribute needs a link to the same few images, so the product will need a row per image in a table - the database doesn't actually contain the image, just its filename. (the images are of clipart for a customer to select from) Based on these records... SELECT * FROM `wp_shopp_spec` WHERE name='Can Be Personalised' and content='Yes' I want to do something like this.. For each record that matches that query, copy records 5134 - 5139 from wp_shopp_asset but change the id so it's unique and set the cell in column 'parent' to have the value of 'product' from the table wp_shopp_spec. This will mean 6 new records are created for each record matching the above query, all with the same value in 'parent' but with unique ids and every other column copied from the original (ie. records 5134-5139) Hope that's clear enough - any help greatly appreciated.

Read the article

Retrieving data from MySQL in one SQL statement

- by james.ingham

Hi all, If I'm getting my data from Mysql like so: $result = $dbConnector->Query("SELECT * FROM branches, businesses WHERE branches.BusinessId = businesses.Id ORDER BY businesses.Name"); $resultNum = $dbConnector->GetNumRows($result); if($resultNum > 0) { for($i=0; $i < $resultNum; $i++) { $row = $dbConnector->FetchArray($result); // $row['businesses.Name']; // $row['branches.Name']; echo $row['Name']; } } Does anyone know how to print the field Name in businesses and how to print the name from branches? My only other alternative is to rename the fields or to call Mysql with two seperate queries. Thanks in advance

Read the article

Group MySQL Data into Arbitrarily Sized Time Buckets

- by Eric J.

How do I count the number of records in a MySQL table based on a timestamp column per unit of time where the unit of time is arbitrary? Specifically, I want to count how many record's timestamps fell into 15 minute buckets during a given interval. I understand how to do this in buckets of 1 second, 1 minute, 1 hour, 1 day etc. using MySQL date functions, e.g. SELECT YEAR(datefield) Y, MONTH(datefield) M, DAY(datefield) D, COUNT(*) Cnt FROM mytable GROUP BY YEAR(datefield), MONTH(datefield), DAY(datefield) but how can I group by 15 minute buckets?

Read the article

Efficient algorithm for Next button on a MySQL result set

- by David Grayson

I have a website that lets people view rows in a table (each row is a picture). There are more than 100,000 rows. You can view different subsets of the rows, and you can view them with different sort orders. While you are viewing one of the rows, you can click the "Next" or "Previous" buttons to go the next/previous row in the list. How would you implement the "Next" and "Previous" features of the website? More specifically, if you have an arbitrary query that returns a list of up to 100,000+ rows, and you know some information about the current row someone is viewing, how do you determine the NEXT row efficiently? Here is the pseudo-code of the solution I came up with when the website was young, and it worked well when there were only 1000 rows, but now that there are 100,000 rows I think it is eating up too much memory. int nextRowId(string query, int currentRowId) { array allRowIds = mysql_query(query); // Takes up a lot of memory! int currentIndex = (index of currentRowId in allRowIds); // Takes time! return allRowIds[currentIndex+1]; } While you are thinking about this problem, remember that the website can store more information about the current row than just its ID (for example, the position of the current row in the result set), and this information can be used as a hint to help determine the ID of the next row. Edit: Sorry for not mentioning this earlier, but this isn't just a static website: rows can often be added to the list, and rows can be re-ordered in the list. (Much rarer, rows can be removed from the list.) I think that I should worry about that kind of thing, but maybe you can convince me otherwise.

Read the article

How to add relation between two tables in Mysql using Mysql control center

- by moon

I want to create two tables with one to many relations. I use MySql Control center,but I did not find to add foreign key.I do not want to use console commands,How can I do? I also want to use transation so I use InnoDB table type, How can I add relation between two tables in Control Center.Is there another GUI software to handle Mysql?Pls tell me.

Read the article

Creating a foreign key in MySQL produces error:

- by SnOrfus

I'm trying to create a foreign key on a table in MySQL and I'm getting a strange error that there seems to be little info about in any of my searches. I'm creating the key with this (emitted from mysql workbench 5.2): ALTER TABLE `db`.`appointment` ADD CONSTRAINT `FK_appointment_CancellationID` FOREIGN KEY (`CancellationID` ) REFERENCES `db`.`appointment_cancellation` (`ID` ) ON DELETE NO ACTION ON UPDATE NO ACTION , ADD INDEX `FK_appointment_CancellationID` (`CancellationID` ASC) ; at which point I get the error: ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (alarmtekcore., CONSTRAINT FK_lead_appointment_CancellationID FOREIGN KEY (CancellationID) REFERENCES lead_appointment_cancellation (`) I've checked here but there's no data in the table.

Read the article

Which MySQL Fork/Version to Pick??

- by Drew

As most of you know, Sun acquired MySQL (and later Oracle acquired Sun), and during these acquisitions, there were a lot of FUD in MySQL community which resulted in creation of various forks. Today we have MySQL from MySQL, Percona (XtraDB) MySQL, OurDelta MySQL, MariaDB, Drizzle to name a few. Which brings us to the source of the problem. We are in the process of upgrading our databases (hardware/software) and I would like to know which one of the forks should I go with. Each has their own set of pros/cons. We are currently using MySQL 5.0.x from MySQL/Linux on an 8-core machine. Our new hardware is a monster with 32 cores and 32GB of memory connecting to a fast NetApp Storage via FC. I would like to stick with MySQL from MySQL but I have heard horror stories on how badly MySQL 5.1 performs on many cores. I have also heard that MySQL 5.4 performs better on multi-core machines but that's still not production ready. In addition, I have also heard a lot of good things about Percona builds. This is what I know so far: MySQL 5.1 from MySQL: Reliable choice, but doesn't scale well on a big machine Percona: Scales well, good backing company. I don't have much experience with it MariaDB: Don't know much about it besides that it was founded by Original MySQL developers (including Monty) OurDelta: Don't know much Drizzle: Mostly optimized for cloud computing I would like to know what's the general notion about this problem. Which build/version should I go with? How are you guys picking your builds/versions? Thanks!

Read the article

reset root password in mysql without access to mysql table

- by Rik89

I am having an issue on OS X 10.7.5 as I used to use MAMP but for .htaccess issues I am now using my own compiled local server from a long time ago, the problem is i forgot the root password for mysql. I have tried updating the password through terminal using mysql -u root, but I get this error message - ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) Thanks Ric

Read the article

MySQL Workbench 5.2. Load INSERTs from a file

- by shurik2533

How can I load INSERTs from a file in MySQL Workbench 5.2? I have manual for 5.0, but for 5.2 it is not working

Read the article

Trouble creating a SQL query

- by JoBu1324

I've been thinking about how to compose this SQL query for a while now, but after thinking about it for a few hours I thought I'd ask the SO community to see if they have any ideas. Here is a mock up of the relevant portion of the tables: contracts id date ar (yes/no) term payments contract_id payment_date The object of the query is to determine, per month, how many payments we expect, vs how many payments we received. conditions for expecting a payment Expected payments begin on contracts.term months after contracts.date, if contracts.ar is "yes". Payments continue to be expected until the month after the first missed payment. There is one other complication to this: payments might be late, but they need to show up as if they were paid on the date expected. The data is all there, but I've been having trouble wrapping my head around the SQL query. I am not an SQL guru - I merely have a decent amount of experience handling simpler queries. I'd like to avoid filtering the results in code, if possible - but without your help that may be what I have to do. Expected Output Month Expected Payments Received Payments January 500 450 February 498 478 March 234 211 April 987 789 ... SQL Fiddle I've created an SQL Fiddle: http://sqlfiddle.com/#!2/a2c3f/2

Read the article

PHP - SQL query to get update time from table status

- by Tribalcomm

This is my php code (I already have a connection to the db): $array = mysql_query("SHOW TABLE STATUS FROM mytable;"); while ($array = mysql_fetch_array($result)) { $updatetime = $array['Update_time']; } echo $updatetime; I get: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource. I am running MySQL 5.0.89 and PHP5. I do not want to add a new field to the table... I want to use the table status... Any help? Thanks!

Read the article

MySQL IDE recommendation?

- by Maxim Veksler

Hello, I've been wondering what you guys are using to write,debug,test your SQL queries there days? The requirements are quite simple: Auto-complete Syntax Highlighting SQL Hisotry Good UI There are some tools which are common for this task, each with his own problems. To name a few Mysql Query Browser MySQL Workbench (GA?, Beta?) Eclipse Database development perspective Oracle SQL Developer with Connector/J I won't go into why none of them is perfect, trust me they all have their problems. So, what are you guys using?

Read the article

Ultra-grand super acts_as_tree rails query

- by Bloudermilk

Right now I'm dealing with an issue regarding an intense acts_as_tree MySQL query via rails. The model I am querying is Foo. A Foo can belong to any one City, State or Country. My goal is to query Foos based on their location. My locations table is set up like so: I have a table in my database called locations I use a combination of acts_as_tree and polymorphic associations to store each individual location as either a City, State or Country. (This means that my table consists of the rows id, name, parent_id, type) Let's say for instance, I want to query Foos in the state "California". Beside Foos that directly belong to "California", I should get all Foos that belong every City in "California" like Foos in "Los Angeles" and "San Francisco". Not only that, but I should get any Foos that belong to the Country that "California" is in, "United States". I've tried a few things with associations to no avail. I feel like I'm missing some super-helpful Rails-fu here. Any advice?

Read the article

Query to sum duplicated fields

- by g0sha

Here is mysql data id usr good quant delayed cart_ts ------------------------------------------------------ 14 4 1 1 0 20100601235348 13 4 11 1 0 20100601235345 12 4 4 1 0 20100601235335 11 4 1 1 0 20100601235051 10 4 11 1 0 20100601235051 9 4 4 1 0 20100601235051 15 4 2 1 0 20100601235350 16 4 7 1 0 20100602000537 17 4 3 1 0 20100602000610 18 4 3 1 0 20100602000616 19 4 8 1 0 20100602000802 20 4 8 1 0 20100602000806 21 4 8 1 0 20100602000828 22 4 8 1 0 20100602000828 23 4 8 1 0 20100602000828 24 4 8 1 0 20100602000828 25 4 8 1 0 20100602000828 26 4 8 1 0 20100602000829 27 4 8 1 0 20100602000829 28 4 9 1 0 20100602001045 29 4 10 1 0 20100602001046 I need to group fields in witch usr & good has duplicated values with summing quant field for getting smth like this: id usr good quant delayed cart_ts ------------------------------------------------------ 14 4 1 2 0 20100601235348 13 4 11 2 0 20100601235345 12 4 4 2 0 20100601235335 15 4 2 1 0 20100601235350 16 4 7 1 0 20100602000537 17 4 3 2 0 20100602000610 19 4 8 9 0 20100602000802 28 4 9 1 0 20100602001045 29 4 10 1 0 20100602001046 Which MySQL query I need to do to have this effect?

Read the article

Effects of order of columns, while defining an index on them, on the performance of the queries

- by Hossein

Hi, Does order of the columns in an Index definition for a table in a database has any effect on the performance? for e.g. are these two queries different ? CREATE INDEX xxx ON tablex(col1,col2) CREATE INDEX xxx ON tablex(col2,col1) what about the in case that I use a BTREE index? I am using Mysql. thanks

Read the article

MySql product\tag query optimisation - please help!

- by Nige

Read the article

How to completely disable apache access log? [closed]

- by Miljenko Barbir

I'm running WAMP server on Windows Server 2003, Apache 2.2, and I would like to completely disable writing into the access log. It would be neat if I could do the following, but I'm on Windows: CustomLog "|/dev/null" common All I get in the error log is "piped log program '/dev/null' failed unexpectedly", although I kinda expected this... Is there a Windows alternative to this or any other way to just disable writing the access log?

Read the article

MySQL comparisons between multiple rows

- by Hurpe

I have a MySQL table with the following columns: id(int), date (timestamp), starttime(varchar), endtime(varchar), ... I need to find time slots that are occupied by two or more rows. Here is an example table id| date |starttime|endtime | __|_____________________|_________|________| 1 | 2010-02-16 17:37:36 |14:35:00 |17:37:00| 2 | 2010-02-17 12:24:22 |12:13:00 |14:32:00| 3 | 2010-02-16 12:24:22 |15:00:00 |18:00:00| Rows 1 and 3 collide, and need to be corrected by the user. I need a query to identify such colliding rows - something that would give me the ID of all rows in the collision. When inserting data in the database I find collisions with this query: SELECT ID FROM LEDGER WHERE DATE(DATE) = DATE('$timestamp') AND ( STR_TO_DATE('$starttime','%H:%i:%s') BETWEEN STR_TO_DATE(STARTTIME,'%H:%i:%s') AND STR_TO_DATE(ENDTIME,'%H:%i:%s') OR STR_TO_DATE('$endtime','%H:%i:%s') BETWEEN STR_TO_DATE(STARTTIME,'%H:%i:%s') AND STR_TO_DATE(ENDTIME,'%H:%i:%s') ) AND FNAME = '$fname'"; Is there any way to accomplish this strictly using MySQL or do I have to use PHP to find the collisions?

Read the article

MySQL TEXT field performance

- by Jonathon

I have several TEXT and/or MEDIUMTEXT fields in each of our 1000 MySQL tables. I now know that TEXT fields are written to disk rather than in memory when queried. Is that also true even if that field is not called in the query? For example, if I have a table (tbExam) with 2 fields (id int(11) and comment text) and I run SELECT id FROM tbExam, does MySQL still have to write that to disk before returning results or will it run that query in memory? I am trying to figure out if I need to reconfigure our actual db tables to switch to varchar(xxxx) or keep the text fields and reconfigure the queries.

Read the article

Query to bring count from comma seperated Value

- by Mugil

I have Two Tables One for Storing Products and Other for Storing Orders List. CREATE TABLE ProductsList(ProductId INT NOT NULL PRIMARY KEY, ProductName VARCHAR(50)) INSERT INTO ProductsList(ProductId, ProductName) VALUES(1,'Product A'), (2,'Product B'), (3,'Product C'), (4,'Product D'), (5,'Product E'), (6,'Product F'), (7,'Product G'), (8,'Product H'), (9,'Product I'), (10,'Product J'); CREATE TABLE OrderList(OrderId INT NOT NULL PRIMARY KEY AUTO_INCREMENT, EmailId VARCHAR(50), CSVProductIds VARCHAR(50)) SELECT * FROM OrderList INSERT INTO OrderList(EmailId, CSVProductIds) VALUES('[email protected]', '2,4,1,5,7'), ('[email protected]', '5,7,4'), ('[email protected]', '2'), ('[email protected]', '8,9'), ('[email protected]', '4,5,9'), ('[email protected]', '1,2,3'), ('[email protected]', '9,10'), ('[email protected]', '1,5'); Output ItemName NoOfOrders Product A 4 Product B 3 Product C 1 Product D 3 Product E 4 Product F 0 Product G 2 Product H 1 Product I 2 Product J 1 The Order List Stores the ItemsId as Comma separated value for every customer who places order.Like this i am having more than 40k Records in my dB table Now I am assigned with a task of creating report in which I should display Items and No of People ordered Items as Shown Below I Used Query as below in my PHP to bring the Orders One By One and storing in array. SELECT COUNT(PL.EmailId) FROM OrderList PL WHERE CSVProductIds LIKE '2' OR CSVProductIds LIKE '%,2,%' OR CSVProductIds LIKE '%,2' OR CSVProductIds LIKE '2,%'; 1.Is it possible to get the same out put by using Single Query 2.Does using a like in mysql query slows down the dB when the table has more no of records i.e 40k rows

Read the article

Running mysql 5.5 on centos 5.9

- by gerrytan

I installed mysql using yum install mysql-server on centos 5.9 and realized it's version 5.0. I need version 5.5 so then I did yum install mysql55-server however I couldn't find a way to start server version 5.5 instead of 5.0. service mysqld start will start 5.0 server and removing mysql 5.0 doesn't help either because service mysqld start fail to find mysqld service Update 1 Nov 2013: I noticed mysql55 package was being installed to /opt/rh/mysql55/root/usr/bin, so I appended that into the start of my PATH env var but service mysqld start still runs 5.0 server. If I tried running the server using mysqld_safe located on above mysql55 path but it says [root@***** bin]# mysqld_safe Use "scl enable mysql55 'service ...'" invocation Not quite sure what it means. I checked the running mysql version by connecting to it using mysql command line client. [root@***** bin]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.0.95 Source distribution

Read the article

Remote desktop is slow when connecting to a computer which is part of a domain

- by Peuge

Hey all, We have two windows 2003 machines, one is a DC and another is joined to the domain of the DC. These machines are not locally available to us so we have to remote desktop into them. When we first got the machines remote desktop was blazing as the machines are only a couple of miles away. I then installed AD and setup routing and remote access, I also setup DNS on the DC. Now when I try remote Desktop into the machine which is part of the domain (not the DC) it is painfully slow! Remote Desktop onto the DC is also noticeably slower! Another problem is that our FTP to the DC has also become slow. I don't know what other information I can provide, as I am new to Sys Admin (moving over from development). The speed should be fast as these machines are only a couple of miles away. Any help / suggestions is greatly appreciated! Thanks Peuge

Read the article

MySQL tmpdir on /dev/shm with SELinux

- by smorfnip

On RHEL5, I have a small MySQL database that has to write temp files. To speed up this process, I would like to move the temporary directory to /dev/shm by putting the following line into my.cnf: tmpdir=/dev/shm/mysqltmp I can create /dev/shm/mysqltmp just fine and do chown mysql:mysql /dev/shm/mysqltmp chcon --reference /tmp/ /dev/shm/mysqltmp I've tried to make SELinux happy by applying the same settings that are in effect for /tmp/ (and /var/tmp/), which is presumably where MySQL is writing its tmp files if tmpdir is undefined. The problem is that SELinux complains about MySQL having access to that directory. I get the following in /var/log/messages: SELinux is preventing mysqld (mysqld_t) "getattr" to /dev/shm (tmpfs_t). SELinux is a hard mistress. Details: Source Context root:system_r:mysqld_t Target Context system_u:object_r:tmpfs_t Target Objects /dev/shm [ dir ] Source mysqld Source Path /usr/libexec/mysqld Port <Unknown> Host db.example.com Source RPM Packages mysql-server-5.0.77-3.el5 Target RPM Packages Policy RPM selinux-policy-2.4.6-255.el5_4.1 Selinux Enabled True Policy Type targeted MLS Enabled True Enforcing Mode Enforcing Plugin Name catchall_file Host Name db.example.com Platform Linux db.example.com 2.6.18-164.2.1.el5 #1 SMP Mon Sep 21 04:37:42 EDT 2009 x86_64 x86_64 Alert Count 46 First Seen Wed Nov 4 14:23:48 2009 Last Seen Thu Nov 5 09:46:00 2009 Local ID e746d880-18f6-43c1-b522-a8c0508a1775 ls -lZ /dev/shm shows drwxrwxr-x mysql mysql system_u:object_r:tmp_t mysqltmp and permissions for /dev/shm itself are drwxrwxrwt root root system_u:object_r:tmpfs_t shm I've also tried chcon -R -t mysqld_t /dev/shm/mysqltmp and setting the group on /dev/shm to mysql with no better results. Shouldn't it be enough to tell SELinux, hey, this is a temp directory just like MySQL was using before? Short of turning off SELinux, how do I make this work? Do I need to edit SELinux policy files?

Search Results

Search found 48190 results on 1928 pages for 'mysql slow query log'.

Page 33/1928 | < Previous Page | 29 30 31 32 33 34 35 36 37 38 39 40 | Next Page >

- by Amenhotep

- by Clemens

- by user328371

- by james.ingham

- by Eric J.

- by David Grayson

- by moon

- by SnOrfus

- by Drew

- by Rik89

- by shurik2533

- by JoBu1324

- by Tribalcomm

- by Maxim Veksler

- by Bloudermilk

- by g0sha

- by Hossein

- by Nige

- by Miljenko Barbir

- by Hurpe

- by Jonathon

- by Mugil

- by gerrytan

- by Peuge

- by smorfnip

< Previous Page | 29 30 31 32 33 34 35 36 37 38 39 40 | Next Page >