1
vote
0answers
31 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 ...
-1
votes
1answer
22 views

How can I optimize a big query when EXPLAIN hints don't work?

We have an application that uses MySQL Ver 14.14 Distrib 5.5.35, for debian-linux-gnu (x86_64) using readline 6.2, which esentially manages lots of data from lots of people over lots of tables and ...
0
votes
2answers
33 views

What's the best way to translate a website

I have a website in Django using MySQL, and I have more than 4.000 entries that I want to translate. What's the best way to do it? Create a new database for every language? Create other table for ...
1
vote
0answers
48 views

Best database for fast read all in one table

I currently have a large database in SQLite that I was using for rapid-prototyping for a software I am working on. Now that I am planning to move it to another database that should give better ...
0
votes
2answers
37 views

How to fix slow LIMIT queries with big offsets without heavily rewriting them?

Imagine a very basic example of your average discussion board. For example: CREATE TABLE threads ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(100), PRIMARY KEY (id) ) CREATE ...
0
votes
0answers
23 views

Big Mysql table optimization

I have a table that I use to cache google translate API answers. Table structure is orig_string and group are indexes Now it has more that 1million rows with 35000 distinct values in group field. ...
0
votes
3answers
50 views

Get only future records

I have the following query: SELECT master.master_id, master.showname, MIN(CONCAT(shows.show_date, ' ', shows.show_time)) AS first_show_date, MAX(CONCAT(shows.show_date, ' ', shows.show_time)) AS ...
0
votes
1answer
38 views

Is there any way to get the estimate cost of executing a query in MySQL?

In PostgreSQL, EXPLAIN or EXPLAIN ANALYZE will show the estimate cost of executing a query. But EXPLAIN in MySQL doesn't provide this information. How can I get the estimate cost without installation ...
0
votes
2answers
34 views

MySQL query optimization for legacy application

I'm working with a legacy application that started out with little data years ago (developed by a third party custom for this client), and now we're seeing MySQL load skyrocket on this query alone. ...
2
votes
2answers
197 views

Optimized query to fetch a record from 10 million entries

A table has 2 fields | UniqueKey | TimeStamp | ------------------------- | xfsddddq | 1024125412 | | xfstttdx | 1024125413 |< | xfsdxxau | 1024125415 | Estimated, there are 10 million ...
0
votes
0answers
14 views

Dynamic chart data from MySQL

I'm building a chart with JS, where i'm doing a query with PHP and pass it to chart in JSON format. The problem is the whole table exported in json file gets up to 10mb, and it takes too much time ...
1
vote
0answers
48 views

Tagged questions

I have similar to StackExchange network that utilises tagged questions. In my web application Users also can find questions by tags. The query below returns a set of tagged questions: SELECT ...
0
votes
1answer
118 views

Delete millions of rows, fast

I currently have a database with the following structure: id->varchar(9) time->datetime val01 - val20->int(11) val21 - val40->tinyint(1) id and time are the primary key. also I defined an index ...
0
votes
1answer
32 views

Query causing Database lock problem (php/Mysql) [closed]

hi friends can anyone tell me what is the issue with my query. when i execute, it lock the Database for 5 to 10 minutes, please help me to optimize this query . SELECT u.username, ...
-1
votes
1answer
42 views

Approximate MySQL Query Time For A Where Clause On A 10 Million Records Table

I'm looking forward to designing a database (in MySQL) but before I start I need to have some calculations to make sure that my application will remain efficient even if there are 10 Million+ rows ...
-4
votes
2answers
66 views

Performance with IN clause

Does indexed data affect query performance? means If I passed value as 1,2,3,4... in ordered form in any IN clause, can it improve query performance? and what happen in case I pass value randomly ...
0
votes
0answers
31 views

query comes in slow query when log_queries_not_using_indexes is enabled

This query comes in slow query log when log_queries_not_using_indexes is enabled. SELECT a.id, d.id AS city_id, d.city FROM booking a, sub_area_master b, area_master c, city_master d WHERE ...
1
vote
1answer
50 views

How can I optimize this query?

I'm trying to select the time field from five tables and return the oldest time. The userid field in every table is indexed. Here's the query: ( SELECT `time` FROM `eventlog` WHERE userid = ...
1
vote
1answer
82 views

MySQL with slow queries

I have a website running on Wordpress Multisite + Woocommerce with over 5700 users on a Nginx + Apache server. I recently experienced some down time but didn’t get any errors, just a blank page. I ...
0
votes
2answers
127 views

What is the performance difference between IN (42) and id = 42 in MySQL?

Are there any performance differences between SELECT * FROM table WHERE id IN (42); and SELECT * FROM table WHERE id = 42; ? The question is about just supplying a single value. The ...
0
votes
1answer
36 views

MySQL: What points should we consider when create composite index

There are multiple views behind the logic of creating composite index in a table. Creating composite index is obvious when we have composite primary key. Beside this, we create composite index on the ...
0
votes
1answer
40 views

MySQL: Different thread states in Query execution

My MySQL 5.1 bases Intranet system is facing very bad performance during high load. When observe through MonYog, I found below thread states: We can simply understand the different thread state by ...
2
votes
1answer
80 views

Optimize table and query, aggregate by date or date-hour

I have a table storing sensor data (100M rows currently) from stations from different sites. In my case, sites may have many stations. Create table statement is as follows: CREATE TABLE sensor_data ( ...
0
votes
0answers
26 views

What Index can I apply to a GROUP BY, MIN() and WHERE MySQL Query

I have the following table: CREATE TABLE `wp_prize_draws_log_copy` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `pid` int(11) NOT NULL, `entry_date` date NOT NULL, `entry_time` datetime ...
1
vote
0answers
43 views

MySQL aggregation count performance

I have a table with about 30 million records, and I'm doing a query where I can group by field Year and count the results for that year. The table structure looks like: id | year | amount The ...
0
votes
1answer
59 views

Slow MYSQL performance due to sub queries

Here is my query: SELECT BAL,NAME, (SELECT SUM(DR) FROM transaction WHERE NAME=TNAME AND DATE < '2000-05-01') AS DR , (SELECT SUM(CR) FROM transaction WHERE NAME=TNAME AND DATE < ...
0
votes
1answer
41 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
2answers
135 views

MYSQL - Why does this SELECT get slower the further down the table?

My table: CREATE TABLE `wp_users` ( `ID` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `user_login` varchar(60) NOT NULL DEFAULT '', `user_pass` varchar(64) NOT NULL DEFAULT '', ...
1
vote
2answers
62 views

Indexing varchar column to search for first letter

I'm trying to optimize a legacy web-app where users can ask for a list of all records whose name starts with a given character. The query to optimize is: SELECT oggetto.id_oggetto, titolo, anno, ...
1
vote
1answer
84 views

How to use LOW_PRIORITY for heavy queries?

I have scheduled heavy UPDATE queries at administrator level, and since they are heavy UPDATEs with multiple JOINs, consume a huge amount of resources. I do not want them to stop end users from ...
1
vote
1answer
116 views

UPDATE a column by COUNT of another table

It might be a naive question, but what is the difference of these two queries and which is preferred? UPDATE table1, (SELECT id,COUNT(*) idcount FROM table2 GROUP BY id) AS B SET table1.Freq = ...
0
votes
1answer
328 views

statistics State in MySQL Processlist

I have a busy MySQL Server having query SELECT sys_sess_state, index_state, timeout_lvl, last_queued_dt, last_polled_wait_dt, create_id, create_dt, modify_id, modify_dt FROM PQR_AM_SYSTEM_SESSION ...
0
votes
1answer
71 views

Is it possible to optimize this query? Or any recommendations to speed it up?

After looking at my mysql slow log file I see that almost every entry there is related to this query: SELECT n.nid AS nid, AVG(v.value) AS rating FROM node n LEFT OUTER JOIN votingapi_vote ...
1
vote
1answer
78 views

MySQL: What is the most optimized way to use datetime type field in query

I am working on optimization of some slow queries in my application. I found a query in which datetime comparison has been used. Below is the subquery: select * from jos_content c where c.visible = 1 ...
1
vote
1answer
325 views

Query State hangs on sending data

For some reason, the query state hangs on "Sending Data" (using show processlist;) and I have no idea why. This is the table creating script: CREATE TABLE IF NOT EXISTS `esp_game` ( `gameID` ...
1
vote
1answer
106 views

Using OFFSET in a large table by a simple sub-query

The slow performance for using OFFSET in large table has been widely discussed in various blogs, and the most efficient way is to use an INNER JOIN as SELECT * FROM table INNER JOIN ( SELECT id FROM ...
1
vote
0answers
40 views

join performance, index vs. index+foreignkey

I'm using an orm framework that handles data (and its integrity) on top of mysql. So far I ignored foreign keys because the orm handles that just fine. I recently read that foreign keys can enhance ...
0
votes
0answers
47 views

MySQL, query optimization with join on huge table

I'm trying to execute a query on MySQL (Database created from a GTFS schema) I have two tables like these: trips trip_id | route_id --------------------- 1 | 409 2 | 409 3 ...
0
votes
1answer
129 views

Insert but ignore if duplicate AND set a value different if exist in another table

I'm trying my make an invitation system together with the Facebook PHP APK. What I basically want is when you have invited your friends, Facebook redirects back to your website with an array ...
0
votes
2answers
165 views

How to get rid of using temporary when using DISTINCT in MySQL?

When I do the following, I get Using where; Using index; Using temporary. How do I get rid of Using temporary? http://sqlfiddle.com/#!8/f61a2/3/0 CREATE TABLE `test` ( `a` varchar(45) NOT NULL, ...
1
vote
1answer
89 views

Identical query, tables, but different EXPLAIN and performance

I have two tables levels and users_favorites +--------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | ...
0
votes
1answer
54 views

Is it any good for performance to have master and slave on the same server

There are similar questions on this site, but they mostly cover server failure and data backup aspects. I'm concerned only as far as query performance. If master and slave share the same resources, ...
1
vote
1answer
103 views

Precautions when designing database schema if tables are going to have billons of rows

I am about to start work on telephone dictionary kind of project. Its confirm that there are going to be billions of records in dictionary table, and each entry from this dictionary table may have ...
1
vote
1answer
174 views

Large INSERT INTO SELECT [..] FROM gradually gets slower

I've written a program that does a large INSERT in batches of 100,000 and shows its progress. The source table contains 2.5GB of data: CREATE TABLE wikt.text ( old_id INT(10) UNSIGNED NOT NULL ...
0
votes
0answers
492 views

Database design to track inventory?

Would this be a good starting point for tracking inventory with several warehouses? If yes what is the best approach to sum quantity when displaying stock by warehouse/type/general? mysql structure ...
1
vote
2answers
76 views

MySQL (InnoDB) UPDATE SET performance with value in WHERE AND clause

If an UPDATE query is performed where the values being SET are also part of the WHERE clause, in the form of an additional AND statement on the column being set's value, is there a significant ...
1
vote
1answer
64 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 ...
0
votes
0answers
73 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 = ...
1
vote
1answer
153 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 ...
1
vote
0answers
57 views

MySQL: What criteria should I base my table column order/arrangement/grouping on?

Does the order, arrangement or grouping of columns with large (such as long text descriptions) and small (such as bit and tinyint) field types have any noticeable impact on MySQL (InnoDB engine) ...