Tag Info

New answers tagged

1

If you can provide sqlfiddle with data sample that would help a lot. But so far test this query please: SELECT p . *, c.cTrained, c.aTrained FROM (`person` p) INNER JOIN (SELECT `ltc`.`person_id`, `c`.`class_id`, `c`.`course_id`, IF(sum(c.course_id = 2) > 0, 'Yes','No') as `cTrained`, IF(sum(c.course_id = 4) ...


3

First, get rid of the subquery. It is not needed and it can interfere with optimization. Second, you don't need a left join, because the having clause is requiring matches. SELECT p .*, IF(sum(c.course_id = 2) > 0, 'Yes', 'No') as cTrained, IF(sum(c.course_id = 4) > 0, 'Yes', 'No') as aTrained FROM person p JOIN ...


0

I'm avoiding using RAND() because it has been said it doesn't work for large tables. I wonder how large is large? As for "doesn't work", I say that is false. RAND() does not repeat after 64K, like some random number generators. As far as "how large", time the first query below -- if it is "too slow" then the table is "too large". Then skip to the ...


0

you can try something like delimiter $$ create procedure select_or_insert() begin IF EXISTS (select * from users where username = 'something') THEN update users set id= 'some' where username = 'something'; ELSE insert into users (username) values ('something'); END IF; end $$ delimiter ;


1

I think the problem might be VALUES and I would write the statement as the following: INSERT INTO `users` (`nUserId`, `vLoginName`, `vFirstName`, `vLastName`, `vEmail`) SELECT '$entity_id', '$name', '$firstname', '$lastname', '$email' FROM dual WHERE NOT EXISTS (SELECT * FROM `users` WHERE ...


1

You cannot use WHERE clause in an INSERT statement. However, you may use INSERT IGNORE if in your targer table you define nUserId or vEmail as unique keys. More info about INSERT syntax: https://dev.mysql.com/doc/refman/5.6/en/insert.html


0

600 inserts/sec is more than out-of-the-box MySQL can do. You have done some tuning; more is possible. innodb_flush_log_at_trx_commit = 1 is the default, but it incurs a write after every transaction. =2 can be significantly faster. Batching inserts, if feasible for your application, can speed up INSERTs 10-fold. How many secondary indexes are there? ...


0

MyISAM is faster than innoDB in insertion and that is because it relies on OS to write data to disk while innoDB insures final disk write (fsync()). moreover, innodb MVCC feature reduces the write speed. If the little delay in write is not a problem, stick to innoDB. you don't want to face lots of table crash or long table locks while your table face read ...


0

Another approach: LOAD DATA into a new (temp) table UPDATE real JOIN new ON ... SET real.x=... WHERE ... -- Update any existing rows that need updating INSERT INTO new SELECT new LEFT JOIN real ON ... WHERE real.id IS NULL -- Add any rows that do not already exist


2

Since there is a unique constraint/index on number, you can use ON DUPLICATE KEY UPDATE syntax - which can be combined with INSERT ... VALUES so all the rows are inserted/updated in one statement. The additional requirement that new null values do not update existing ones can be satisfied using COALESCE() function. Example: INSERT INTO Contacts (number, ...


0

Here's a model that I think would work for you problem. For assignments that take place for all week days, you can create one row in AssignmentDay per WeekDay for that Assignment. I'll create an example scenario and sample data to explain: Jim (idUser=1) is working on the American Express project (idProject=1) every day between Jan 1st and Feb 15th, ...


0

All of this could have been accomplished with the INFORMATION_SCHEMA database The table names are in the INFORMATION_SCHEMA SELECT table_name FROM information_schema.tables WHERE table_schema='conntrack'; You could then line up the tables that exist against the tabidx table SELECT B.table_name FROM (SELECT date FROM tabidx) A INNER JOIN (SELECT ...


0

Google "open source project management software" - I got 49 million hits. There appear to be interesting options here, here and here. There is very little that is truly original in software. What I would advise you to do is to download (a few of the) projects which look interesting, then check out their data models - that's the beauty of Open Source - look ...



Top 50 recent answers are included