A connection with “unauthenticated user” in the User column has initiated a connection but hasn’t sent his/her credentials yet, so the server doesn’t know who exactly is connecting. If such connections only showed up in the list when they were authenticated, it could potentially run the server out of available sockets and you wouldn’t even … Read more
I believe so. After replication is working, you can drop the indexes on the slave and create the indexes you want and that should do it. Since MySQL replicates statements and not data (at least by default), as long as the SQL necessary to insert or update or select from the table doesn’t need to … Read more
You can declare a session variable in this way : SET @myvarname := ‘value’; or a local variable in this way : DECLARE my_variable varchar(30) also: DECLARE my_variable varchar(30) DEFAULT ‘value’
MySQL documentation is quite clear on this: As a general rule, you should never assign a value to a user variable and read the value within the same statement. You might get the results you expect, but this is not guaranteed. The order of evaluation for expressions involving user variables is undefined and may change … Read more
Yes, index will help you, when using ORDER BY. Because INDEX is a sorted data structure, so the request will be executed faster. Look at this example: table test2 with 3 rows. I used LIMIT after order by to show the difference in execution. DROP TABLE IF EXISTS `test2`; CREATE TABLE `test2` ( `id` int(10) … Read more
SELECT a.class, ( SELECT b.age FROM users b WHERE b.class = a.class ORDER BY age LIMIT 1,1 ) as age FROM users a GROUP BY a.class Would get the 2nd youngest in each class. If you wanted the 10th youngest, you’d do LIMIT 9,1 and if you wanted the 10th oldest, you’d do ORDER BY … Read more
Here is what i ended up doing. I hope it helps someone. INSERT INTO persons (firstname,lastname) VALUES (‘John’,’Doe’); SET @person_id = LAST_INSERT_ID(); INSERT IGNORE INTO properties (property) VALUES (‘property_A’); SET @property_id = LAST_INSERT_ID(); INSERT INTO has_property (person_id,property_id) VALUES(@person_id, @property_id); INSERT IGNORE INTO properties (property) VALUES (‘property_B’); SET @property_id = LAST_INSERT_ID(); INSERT INTO has_property (person_id,property_id) VALUES(@person_id, … Read more
According to the documentation, the difference is: REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted. So what it does: Try to match … Read more
They are speaking the same thing and it has to do with tuples (relational algebra) or rows (layman’s term). When it says high-cardinality are possible values of particular attribute (or field) that are unique and therefore the number of rows or tuples are higher: Example: StudentID Lastname Firstname Gender 101 Smith John M 102 Jones … Read more