I am using QSqlDatabase and QSqlQuery for MySQL/MariaDB database, but I guess my questions is more related to database. I don't know where to ask, so I will try here. Thank you if anyone can help.

1. In below two tables, I have UNIQUE KEY ( reporter_id, report_period ), I also need lookups based on reporter_id and report_period. Do I need to define KEY ( reporter_id ), and KEY ( report_period ) (line 11 and 12 in the code)?
2. When doing select, is there any difference between "WHERE report_table.id = report_summary.id" AND "WHERE report_summary.id = report_table.id". That is, is there any difference in #3 and #4 selects?

Qt Code:
  1. 1. Create report_table:
  2. CREATE TABLE report_table
  3. (
  4. id int(11) unsigned NOT NULL AUTO_INCREMENT,
  5. report_period date NOT NULL,
  6. reporter_id int(11) unsigned NOT NULL,
  7. dir varchar(20) NOT NULL,
  8. report_content mediumblob NOT NULL,
  9. PRIMARY KEY ( id ),
  10. UNIQUE KEY ( reporter_id, report_period )
  11. KEY ( reporter_id ),
  12. KEY ( report_period )
  13. );
  14.  
  15. 2. Create report_summary
  16. CREATE TABLE report_summary
  17. (
  18. id int(11) unsigned NOT NULL,
  19. buy_amount double DEFAULT NULL,
  20. sell_amount double DEFAULT NULL,
  21. PRIMARY KEY ( id ),
  22. CONSTRAINT FOREIGN KEY ( id ) REFERENCES report_table ( id )
  23. ON DELETE CASCADE ON UPDATE CASCADE
  24. );
  25.  
  26. 3. Select
  27. SELECT report_table.*, buy_amount, sell_amount
  28. FROM report_table, report_summary
  29. WHERE report_summary.id = report_table.id
  30. AND reporter_id = 12
  31. AND report_period >= '2000-01-01'
  32. AND report_period <= '2000-12-31';
  33.  
  34. 4. Select
  35. SELECT report_table.*, buy_amount, sell_amount
  36. FROM report_table, report_summary
  37. WHERE report_table.id = report_summary.id
  38. AND reporter_id = 12
  39. AND report_period >= '2000-01-01'
  40. AND report_period <= '2000-12-31';
To copy to clipboard, switch view to plain text mode