0
votes
2answers
59 views

Speed up a MySQL BETWEEN query

SELECT dateToFind FROM table1 INNER JOIN table2 ON dateToFind BETWEEN dateFrom AND dateTo I need to improve the performance of this kind of query. I added BTREE indexes on dateToFind, dateFrom, ...
0
votes
2answers
25 views

Best way to store few rows of data every minute without creating 1440 columns a day?

I will be calling a server that returns some JSON data every minute. The values are along the lines of Value, Currency, DateTime, and maybe 1 or 2 more values. What is the best way to keep save these ...
0
votes
0answers
13 views

Can having a LIMIT on DELETE degrade performance dramatically?

I have a database project in php and mysql. For, hopefully redundant, data security I added a LIMIT 1 to the delete($uniqueID) php function we use. Nobody cared because records really aren't ...
0
votes
0answers
15 views

EXPLAIN (with subqueries) is giving results I don't understand

I have a query that relies on a subquery, and that subquery relies on another subquery. When I run an EXPLAIN on the full query, in does a full table scan of the table in the primary select. However, ...
1
vote
0answers
18 views

Why do these two queries (which I think are equivalent) rely on different keys?

We have a query that we don't like, since it ends up doing a full table scan. EXPLAIN SELECT count(*) AS c, location.name FROM study LEFT JOIN location ON study.location_id = location.id ...
1
vote
1answer
68 views

Reducing execution time for this gnarly SQL

I'm creating an order system of some kind. There is a function that I use to get orders (in PHP) and retrieve the required information about the order. However, it seems that I am pulling a lot of ...
3
votes
2answers
33 views

Why is MySQL ignoring my indexes?

I have a query select distinct id from patient where company_id in (1) and (name_last like 'peter%' or name_first like 'peter%' or remote_id like 'peter%') running on a table that looks like this ...
0
votes
1answer
31 views

Tuning Mysql and Monitoring

I would like to know if anybody can point me towards a tool/way for MySQL database tuning ... Actualy the tunning of my.cnf is like below My database is in MyISAM | Mysql 5.1.73 key_buffer = 500M ...
2
votes
2answers
178 views

UPDATE query with JOIN taking forever

I'm not very good with databases so please keep in mind even if the solution sounds like something everyone would have already tried, please post it because I likely have not tried it and it would ...
0
votes
0answers
19 views

Mysql query with LIMIT much slower under RDS (5.6) than old 5.1 system

We inherited an old legacy system running on mysql 5.1, and moved it to RDS running 5.6. One query is running much slower - it can take up to 20 minutes to run, where previously it took only a few ...
1
vote
1answer
77 views

MySQL: Which join is better between left outer join and inner join

Which join is better peforming if all of them provides the same result? For example, I have a two tables employees(emp_id,name, address, designation, age, sex) and work_log(emp_id,date,hours_wored). ...
1
vote
1answer
34 views

Mysql join table order by index

First Master Table CREATE TABLE `edipostingmaster` ( `ID` bigint(20) NOT NULL AUTO_INCREMENT, `applied` int(11) DEFAULT '0', `fileName` varchar(255) DEFAULT NULL, `checkNo` varchar(100) ...
0
votes
0answers
32 views

How to optimize having bit_or?

Let's start with SQLFiddle http://www.sqlfiddle.com/#!2/64fc5/1 I have some urls and I am making some tasks (crons) related to them. Task can be successful or not. I want to try to do it only three ...
0
votes
1answer
42 views

Last row from a query without Order by

I have something like SELECT var1,var2 FROM table where var3=X AND var4=Y order by var5 desc limit 1; Running this query takes roughly 5 seconds on my database which is far to slow. I was ...
1
vote
1answer
42 views

A table that consists of multiple FK or creating separate tables for each FK

I have tables called 'books', 'category', 'subcategory', and 'booksCategory'. Each book can have its own category and subcategories. For example, a Sci-Fi book can belong in the 'category' table under ...
0
votes
1answer
20 views

How to optimized mysql query having large dataset

I have two tables in MySql with the following schema, CREATE TABLE `open_log` ( `delivery_id` varchar(30) DEFAULT NULL, `email_id` varchar(50) DEFAULT NULL, `email_activity` varchar(30) DEFAULT ...
0
votes
1answer
50 views

A single, simple update takes 10+ seconds

We are running MySQL 5.1.71-log and have a table with 2.7 million rows. Simple updates to it are regularly taking more than 10 seconds to execute. The table structure is: CREATE TABLE `person` ( ...
0
votes
0answers
24 views

Help with MySQL query which is only occasionally slow? Temp tables/Disc caching?

We have an issue with one particular query whch runs regularly on our MySQL database, though I am not necessarily sure it is a problem with the query itself. 90% of the time it runs perfectly fine ...
0
votes
1answer
66 views

Help optimizing very slow mySQL query

I have a query which takes about 6 secs to execute. SELECT * FROM (SELECT ord.ID, op.name AS prodName, op.code, ord.date, ord.email, op.total, stat.ID AS statusID, stat.value AS status, ...
0
votes
0answers
21 views

Select changes in table by date

I have following table schema name type id int key string count int date date url_id id with example data id key count date url_id 1 system 100 ...
1
vote
1answer
41 views

Mysql super slow

I have a database of movies that I downloaded, it is around 51mb in size. One of the tables has more than 750k records(all separate insert statements(I believe this is bad)). The rest aren't so bad ...
0
votes
0answers
34 views

Slow query (Wordpress)

Can I speed up somehow the following query? Somehow to stop from creating a tmp table/filesort. Tried a couple of indexes but no luck. mysql> explain SELECT wp_posts.ID FROM wp_posts LEFT JOIN ...
1
vote
1answer
32 views

What is the generally accepted method and tools for profiling MySQL queries?

Profiling database queries is difficult, and dependent on database technology. In MySQL, what is the usual (or, in deference to that, a great) process for profiling a query? For example: Run the ...
1
vote
1answer
47 views

Difference between DATE(time_field) AND time_field in a WHERE clause

I have 3 tables: users movements unique_ids I want to get the records gotten from march until now of the users registered in march, counting the unique id of their movements with a distinct and the ...
0
votes
3answers
64 views

How to return last used record?

I have simple table called ips with 2 field ip and address, which schema is following CREATE TABLE IF NOT EXISTS `ips` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `address` varchar(255) ...
0
votes
2answers
57 views

Increased query_cache_size, Queries slowed drastically on increased traffic

I increased our servers query_cache_size to 2gb, hoping for performance benefits. After sometime the query performance would drastically decrease. Once I dug in deeper, I found the queries in State ...
0
votes
1answer
160 views

Select from a list of values from JSON encoded data

I have saved the details of the product in JSON format. I am wondering how to select from a list of values from a "JSON_ENCODED DATA" For Example I want to select TV with screen_type as LED or LCD. ...
2
votes
2answers
87 views

Super simple mysql query taking too long

I have the following: SELECT * FROM autorepair WHERE zip_code IN (91316,91356,91416,91426,91436) AND specialty = 'transmission' ORDER BY ABS(91316 - `zip_code`) LIMIT 0,10 Indexes: -zip_code ...
0
votes
1answer
40 views

How to optimise very simple query with subquery that only needs to execute once

SELECT products_id, products_model, products_iban_no, products_allocation_number FROM products WHERE products_model IN (SELECT products_model FROM products GROUP BY products_model HAVING ...
0
votes
1answer
54 views

How can I write queries as fast as possible in MySQL?

I'm looking for two things: Awesome manual about writing fast MySQL queries. Tool which will decompose my query into discrete steps, and give estimations of each step. I've seen the next tool, ...
2
votes
1answer
91 views

Improve select query performance

The following select query from my application is taking more than 200ms. The table has around 2 million rows. Is there any ways to improve the performance of this query ? select count(id) from ...
1
vote
1answer
73 views

Performance issues when extracting scatter plot data

First off: Sorry for the title, couldn't think of anything better. Feel free to change it though. Ok, assume I have the following table: ...
-5
votes
1answer
140 views

Recommendation for mysql please :MySQLTuner [closed]

>> MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options ...
0
votes
0answers
17 views

Analize Query in MySQL Server 5.1

I try use MySQL Workbench 6 to analyze the cost, but they don't show the cost of query. How i get this costs?
0
votes
1answer
155 views

Improve MySQL Query Performance - Too many records

I am using MySQL RDMS for a contact management system. Now a days my database grew in size and contains more than 1 million records. Checking duplicate phone numbers became a big issue during ...
1
vote
1answer
34 views

Will composite indexes or unique indexes help with query performance?

I need help with identifying indexes to increase performance of the following query. I'm currently using v5.5 with InnoDB. I cannot modify the query. However, I can add indexes as needed. The ...
2
votes
1answer
95 views

poor and/or unreliable InnoDB performance

I recognize that there are a ton of posts like this, but after reading up on InnoDB performance problems, nothing I tried has helped. I have a database containing four InnoDB tables. The largest ...
1
vote
1answer
57 views

Slow queries InnoDB occasionaly

I have the next table: CREATE TABLE `sessions` ( `session_id` char(32) COLLATE utf8_unicode_ci NOT NULL, `session_expires` int(11) NOT NULL DEFAULT '0', `session_data` varchar(10000) COLLATE ...
1
vote
1answer
74 views

LOAD DATA stuck at Null State MySQL

I'm stuck trying to move one table to another, due to columns now no longer having proper references/meaning to their column titles. In doing this, I've created a new table with the same number of ...
0
votes
0answers
111 views

group by and order by making query very slow in mysql?

Query works fast: select SQL_NO_CACHE users.id, users.name from useres left join user_statistics on users.id = user_statistics.user_id where users.is_deleted = 0 group by users.id ...
0
votes
1answer
60 views

Query with INNER JOIN takes long time than expected?

I have the following query, which takes longer than expected. Anybody help me find a faster alternative? SET SESSION MAX_JOIN_SIZE=18446744073709551615; SELECT claims.claim_id, ...
3
votes
1answer
243 views

MySQL queries are 10 to 100 times slower after OS reboot

I have a MySQL server running on Ubuntu. If I restart the MySQL, everything is fine. If I however restart the OS, my queries take anywhere from 10 times to 100 times as long. The only "solution" to ...
1
vote
1answer
100 views

Why does innodb_flush_method = O_DSYNC provide such a performance boost with SAN-backed storage?

I've seen some people suggest that O_DSYNC should be used with a SAN. The MySQL docs have this to say about O_DSYNC in general: In some versions of GNU/Linux and Unix, flushing files to disk with ...
1
vote
2answers
137 views

Why does “SELECT *” take longer than “SELECT <primary_key>”?

I'm having a problem with a query running on a little PHP program. When I use * to select all the columns, it takes way more to return the result than when I use just one column. The query that is ...
0
votes
0answers
56 views

Latest of group in subquery is bottleneck, how to optimizing query?

See Original question I'm fetching the activity data for the latest activity for each company as part of a bigger query. I've been using the method provided by ypercube, and it works fine, but I find ...
1
vote
1answer
2k views

MySQL SQLSTATE[HY000]: General error: 1205 Lock wait timeout exceeded

I have database session enable, when website got thousands of visitor. It gives me error [30-Apr-2014 04:27:12 America/Denver] PHP Fatal error: Uncaught exception 'PDOException' with message ...
2
votes
2answers
675 views

MySQL COUNT Query taking too long time to execute

I have below query that takes 7 secs to run SELECT COUNT(*) AS `count` FROM `yuldi`.`businesses` AS `Business` LEFT JOIN `yuldi`.`businesses_categories` AS `BusinessesCategory` ON ...
0
votes
0answers
27 views

Can't run subqueries on my server (MySQL)

I have a weird problem, where I can run sub-queries just fine on my local machine, however they seem to be hanging forever when I attempt to run them on my server. Note that my server has plenty of ...
0
votes
0answers
33 views

MySql query keep running for a long time

Following query keep running for a long time and I'm using MySql 5.5.34 on XAMPP on Windows SELECT id_package, `pck`.`fk_sales_order`, `pck`.`fk_picklist`, count(pci.id_package_item) as items, ...
1
vote
1answer
68 views

Any idea why this seemingly-simple query is really slow?

Here's the query SELECT count(*) FROM registration r LEFT JOIN registration r2 ON r.baggage_group_id = r2.baggage_group_id AND r2.team_id IS NULL WHERE r.event_id = "102577"; It seems pretty ...