Hot answers tagged

5

This should work: SELECT id, name FROM data WHERE id <= 8 AND id >= ( SELECT MAX(d1.id) FROM data d1 LEFT JOIN data d2 ON d2.id = d1.id - 1 WHERE d2.id IS NULL ) ORDER BY id DESC ; The subquery look for the first gap (i.e. 5 to 6). See SQL Fiddle. Output: id | name 8 | Test 8 7 | Test 7 6 | Test 6


5

There is no difference: your two examples are completely equivalent but using different versions of SQL syntax. The database engine will handle them in exactly the same way. Your first example is using an explicit join and is the preferred syntax these days. It was introduced in the SQL-92 standard and is supported by pretty much every SQL-style query ...


2

The idea I wrote in the comment http://sqlfiddle.com/#!9/785bc/5 : select a.userid, date(a.CHECKTIME), timediff(max(b.CHECKTIME), min(a.CHECKTIME)) as diff from CHECKINOUT a join CHECKINOUT b on date(a.CHECKTIME) = date(b.CHECKTIME) and a.userid = b.userid where a.CHECKTYPE = 'I' AND b.CHECKTYPE = 'O' group by date(a.CHECKTIME), ...


2

I read the question as if you would like the min and max temperature for each day in the month. Something like: SELECT DATE(CREATED) AS date , MIN(ROUND(AMBIENT_TEMPERATURE,1)) AS min_temp , MAX(ROUND(AMBIENT_TEMPERATURE,1)) AS max_temp FROM WEATHER_MEASUREMENT WHERE (MONTH(DATE(CREATED)) = Month(NOW())) GROUP BY DATE(CREATED); should do. ...


2

Some of your premises are not necessarily true therefore could lead to false conclusion. The thing with segregation is that query time will surely reduce Properly sized and configured servers with the right indexes can still serve queries from tables in the TB scale in the milliseconds range. If you do point queries (single row lookups with primary key ...


2

There are a few problems here: First, as jkavalik says in the comments on the OP, the order of columns in an index matters. Basically, in your case for index_rqcd to be used for filtering on rq_date, t_id has to be used before it can "see" and filter on rq_date. Since usually only one range scan on an index can be done for a query and it has to be the ...


1

SUGGESTION #1 Here is a better WHERE clause for your query SELECT DATE(CREATED) date, ,MIN(ROUND(AMBIENT_TEMPERATURE,1)) min_temp ,MAX(ROUND(AMBIENT_TEMPERATURE,1)) max_temp FROM WEATHER_MEASUREMENT WHERE CREATED >= ( MAKEDATE(YEAR(NOW()),1) + INTERVAL (MONTH(NOW()) - 1) MONTH ) AND CREATED < ( MAKEDATE(YEAR(NOW()),1) + ...


1

The information_schema database is an all memory database made up of temporary tables. It is not stored in any of the InnoDB plumbing. I wrote about this 5 years ago : How is INFORMATION_SCHEMA implemented in MySQL? If you performed a mysqldump of the infomration_schema, reimporting it will do nothing. mysqld will correct and protect any outside manual ...


1

If both the servers showing same server-id, you can set this to some other value and check again. On Master: server-id = 1 On Slave: server-id = 2 Make sure that, while giving change master settings, give the host name as ip address of the master server. This should work, check again and see if the same issue exists. I hope this will help you.


1

Your [sub_tree] derived table is using an ORDER BY with no TOP operator. Try something like the following: SELECT TOP (1) node.name [...] ORDER BY node.lft ) AS sub_tree Granted that you are just following a MySQL book, I should probably mention the whole "no-no" on doing old style joins on SQL Server. Use INNER JOIN, LEFT JOIN, etc rather ...


1

Don't use offset - it has to read all the rows to skip them. If you order rows for pagination by created_at, then thats the column you need to use. First page: SELECT ... FROM table ORDER BY created_at, id LIMIT N + 1; The +1 gives you a peek to the next page - you can quickly decide if next page exists and where does it start. If created_at is not ...


1

Probably easiest to just do a self-join: SELECT x.tag FROM tagtable x JOIN tagtable y ON x.area_id = y.area_id WHERE y.tag = 'turkey' AND x.tag <> y.tag


1

if i'm reading your question properly, you know there is a 'turkey' tag and want your query to return every row that shares the same area_id but with a tag that is not 'turkey'. Right? Try... SELECT a.* FROM tagtable a, ( SELECT * FROM tagtable WHERE tag = 'turkey' ) As z WHERE a.area_id = z.area_id AND a.tag <> z.tag; hope that helps...


1

You may want to consider using a single JSON field on your posts table to store all the miscellaneous details in a JSON object. You can have as many as you want, including arrays and nested objects. At this time MySQL is not great at indexing and accessing/manipulating JSON data, but it does provide a few functions like JSON_OBJECT, JSON_ARRAY, JSON_SET, ...


1

The second option should be the fastest. It was made for this. Also it should have no bugs since it is used a lot and already for a long time. If you go for this then you do not even need to use a composite primary key. In my opinion the only reason to use the first option is if you need a numbering starting from 1 per client.


1

From the manual: An absolute path remains unchanged; in such a case, the index must be edited manually to enable the new path or paths to be used. Previous to MySQL 5.6.5, manual intervention was required whenever relocating the binary log or relay log files. (Bug #11745230, Bug #12133) So if your binlog index was written with the full paths, you need ...


1

Solution 1: After hormonizing all tables to MyISAM the Query-Cache of MySQL worked as expected again. Added some indexes for cd and parent to speedup the queries about 30%. Solution 2: Set up MySQL-Proxy and created LUA-Script to cache Result of query in MySQL-Proxy. Shop-Software was set to use the MySQL-Proxy on Port 3307 as MySQL-Server. apt-get install ...


1

The heavy hitters have Load balancer --> one of a set of web servers --> load balancer --> one of a set of MySQL slaves. That is, you are hitting at least 4 boxes. And none of them is "heavy" on CPU. Don't fear using dedicated servers. If the CPU is high in MySQL, then you are missing an index, or don't have a "composite index" or have a poorly written ...


1

For InnoDB, FULLTEXT(title,body) -- useful only for MATCH(title, body) AGAINST ... FULLTEXT(title) -- useful only for MATCH(title) AGAINST ... So, if you are doing both types of MATCH, you need both indexes.


1

Automating on the Derek's solution, this will change DEFINER to root@localhost and set SQL SECURITY INVOKER (make sure you want that first!) in all views in all databases: mysql -BNe 'show databases' | \ egrep -v '^(information_schema|performance_schema)$' | \ while read DB do mysql -BNe "SELECT CONCAT(\"ALTER DEFINER=\`root\`@\`localhost\` SQL ...



Only top voted, non community-wiki answers of a minimum length are eligible