Tag Info

Hot answers tagged

8

The query results are correct, as your query finds all teams and results where a team played against itself (home_team = away_team). You probably want to use 2 joins to teams in you query: SELECT home.team_name AS home_team_name, away.team_name AS away_team_name, r.score FROM results AS r JOIN teams AS home ON r.home_team = ...


4

You missed closing bracket in line: user VARCHAR(100 NOT NULL, should be: user VARCHAR(100) NOT NULL,


4

Considering that MySQL is still a relation database management system an (RDBMS if you will), you should considering the following things: Your tables are defined by the data they contain and the relationships to other tables Your data should be as non-redundant as possible This last point leads to the process of normalization, which is a very important ...


3

You can't* combine multiple queries like this. You need to execute them separately. When you see the error ...the right syntax to use near... that's telling you that MySQL's parser (lexer?) can't come up with any valid reason why it found whatever it's complaining about in the specific place within the statement where it was encountered... in this case, ...


3

In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other ...


3

ASSESSMENT Your query is kind of dangerous to the MySQL Query Optimizer. I have an old post (Problem with MySQL subquery). The question posed involved this query DELETE FROM test WHERE id = (SELECT id FROM (SELECT * FROM test) temp ORDER BY RAND() LIMIT 1); Although the MySQL Query Parser will work with this query and accept it's syntax, the MySQL ...


2

You might laugh when you here this, by there is an aggregate clause that triggers summary between breaks in values. The aggregrate clause is WITH ROLLUP. (Look in MySQL Documentation under GROUP BY Modifiers) let's take your query and make the following changes Remove ORDER BY Substitute WITH ROLLUP Remove name You get this SELECT cdr.datefield AS ...


2

Your conditions are searching for ranges. Now imagine you're looking in a dictionary and looking up all words where the first letter is "greater" than A. Of how much use is the index? You want to narrow the search range down as much as possible. MySQL most of the time can only use one index per table. Combine those begin and end indexes. CREATE TABLE ...


2

If you are 100% sure that the intervals (begin, end) are never going to be overlapping, you can use this query, which only needs an index on (begin) or (begin, end) and will be much more efficient than what you have: SELECT t.* FROM ( SELECT g.* FROM geo_ip_city AS g WHERE g.begin <= 2523596988 ORDER BY g.begin DESC -- ORDER BY ...


2

Take a look here. It's a tool which genuinely tries to compare like with like in terms of database benchmarking!


2

SELECT DISTINCT a.id, a.etc FROM years y JOIN activitymapper am ON (y.targetyearid = am.targetyearid) JOIN activity a ON (a.id = am.activityid) WHERE y.targetyearname IN ('Year1', 'Year2') Should do it. We're going from years to activity through activitymapper, selecting each activity only once (DISTINCT).


1

You need to join all the teacher's last visit_time value against the whole visits table SELECT B.* FROM ( SELECT teacher,MAX(visit_time) visit_time FROM visits GROUP BY teacher ) A INNER JOIN visits B USING (teacher,visit_time); Based on this query, you should also add the following index ALTER TABLE visits ADD INDEX teacher_visit_time_ndx ...


1

I don't have experience of your exact situation, but could you use: auto_increment_increment=3 auto_increment_offset=1 [then 2 and then 3] on your different masters, so that each one generates a different set of id's. e.g. server 1: 1, 4, 7 server 2: 2, 5, 8 server 3: 3, 6, 9


1

I think you need a UNION or an OR with EXISTS: SELECT c.* FROM change AS c WHERE c.project_id = 10 OR c.project_id IS NULL AND EXISTS ( SELECT * FROM change AS x WHERE x.project_id = 10 AND x.object_id = c.object_id ) ;


1

in PostgreSQL you should use a tool called pgbench. it allows you to run custom scripts with an arbitrary number of concurrent requests and all that. it is also able to create random values for your script and all that - it is really powerful. for the test: make sure you got a proper setting for -j (number of threads used by pgbench) so that pgbench is able ...


1

What you need is partial backups. innodb_file_per_table=ON is a prerequisite to make it work. In your case procedure would be like following: Take a partial backup from the production server innobackupex --databases="mydatabase" /path/to/backup On the destination server prepare the backup copy for export: innobackupex --apply-log --export ...


1

I wrote some posts about RAM disk and temp tables Jan 04, 2013 : Is there a MySQL engine or trick to avoid writing so many temp tables to disk? Jan 17, 2012 : How can I optimize my mysql setup to create my index faster? Sep 23, 2011 : skip copying to tmp table on disk mysql The basic idea would be to set set tmp_table_size and max_heap_table_size to the ...


1

Combine the two tables with UNION and ORDER BY timestamp. You can also add an addition (type) column so the source of a row can be identified: SELECT 'comment' AS type, id, comment_content AS content, time_stamp FROM comments UNION ALL SELECT 'activity', id, activity_content, time_stamp FROM activities ORDER BY timestamp ;


1

Here FROM teams p , (SELECT @curRank := 0) r you are resetting the value for @curRank to 0 every time you execute the query. Make this dynamic as well, adjust it according to your LIMIT clause. On the second run change it to FROM teams p , (SELECT @curRank := 25) r


1

if you trying to use the same query then it could be optimized adding composite index to geo_ip_city the new index could be ALTER TABLE geo_ip_city ADD INDEX ind_begin_end (begin, end) USING BTREE with this you would need to remove you use index in the select statment to be SELECT * FROM geo_ip_city WHERE begin <= 2523596988 AND end >= ...


1

If I am not mistaken, the test Database is created by default. In addition, the mysql.db table contains rows that permit all accounts to access the test database and other databases with names that start with test_. This is true even for accounts that otherwise have no special privileges such as the default anonymous accounts. This is convenient for ...


1

Some might say depends how big the databases are; I think regardless of the size, accessing the databases directly instead of having a "local" mySQL database to query would be best, for the following reasons: No need for disk/memory/maintenance of the mySQL database If the updates to the various databases happen every few seconds, and the users may only ...


1

You have a syntax error This is not the right SQL for getting the count Select COUNT(*) from `user` into c where `mobile_no` = mobileNo; You do not put into c after the from clause. It goes before it. Therefore, this is correct SQL syntax Select COUNT(*) into c from `user` where `mobile_no` = mobileNo; Give it a Try !!!


1

DISKSPACE FOR EVERYTHING INNODB SELECT FORMAT(SUM(data_length+index_length)/POWER(1024,3),2) InnoDB_DiskSpace FROM information_schema.tables WHERE engine='InnoDB'; DISKSPACE FOR DATABASE mydb BY TABLE SELECT IFNULL(tbl,'Total') table_name, FORMAT(SUM(table_bytes)/POWER(1024,3),2) table_size FROM ( SELECT table_name ...


1

create table `certificates`( `id` INT NOT NULL AUTO_INCREMENT, `common_name` VARCHAR(100) NOT NULL, `csr` LONGTEXT NOT NULL, `type` VARCHAR(100) NOT NULL, `certificate` VARCHAR(100) NOT NULL, `user` VARCHAR(100) NOT NULL, `creation_date` DATE, PRIMARY KEY ( id ) )


1

First, have a look at the InnoDB Architecture (From Percona's CTO Vadim Tkachenko) When you update an indexed column, changes must migrate through this architecture as follows: From Insert Buffer section of the Buffer Pool to the Insert Buffer inside ibdata1 From Dirty Pages of the Buffer Pool to the Tables Physical File (.ibd file) If you are ...


1

This technique is possible. Here is the correct syntax: DELETE A.*,B.* FROM member A INNER JOIN subscriptions B USING (type_id) WHERE A.type_id = 1; I have another post from June 26, 2013 on this same DELETE syntax on multiple tables : Deleting Data From Multiple Tables As for the original question, @Michael-sqlbot has it right. The particular tool you ...


1

Using FULLTEXT indexes has to be handled with great care. Why ? While FULLTEXT index searches do work, the MySQL Query optimizer tends to suggest full table scans if you do not express the query properly. Let's take your query and look for 'tom' SELECT DISTINCT c.movieName, c.castName, c.movieImdbId, f.year, f.posterLink FROM cast_movie as c JOIN film_info ...


1

in ANSI SQL the DISTINCT is totally pointless here. UNION automatically filters duplicates: test=# SELECT 1 UNION SELECT 1; ?column? ---------- 1 (1 row) There is a sharp distinction between UNION and UNION ALL: test=# SELECT 1 UNION ALL SELECT 1; ?column? ---------- 1 1 (2 rows) in your case the second subselect can never ...


1

Please look back at my answer again. Issue #1 might be your one and only problem. Why ? FACT #1 : 5199 Qcache_total_blocks allocated FACT #2 : 622 Qcache_free_blocks FACT #3 : 2269 Qcache_queries_in_cache FACT #4 : query_cache_limit is 268435456 (256M) FACT #5 : query_cache_min_res_unit is 4096 (4K) What can said here ? There are 2269 queries in the ...



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