Hi, I have a Huge mysql table (100M rows), and let's say the table have two columns, column A and column B. Column A is indexed, and Column B is not. So: SELECT * from table1 WHERE A='DATA_A' should be very fast. How about the performance of the following? (1) SELECT * from table1 WHERE A='DATA_A' AND B='DATA_B'; (2) SELECT * from table1 WHERE B='DATA_B' AND A='DATA_A'; Thank you
Not an SQL efficiency expert however my understanding is that conditions are looked up in sequential order and therefore 1 would be quicker as the initial look up is based on the indexed column.
Both the statements should be equally good in your case. Most of the databases are clever enought to use the indexes to the maximum to give us optimal performace so in both the cases the SQL engine will use the index of A to select the matching rows and then from this list will use B to select the final set of rows.
Hi hostingcoupon The optimizer will automagically rewrite your SQL to use any indexes it can to speed the query up so (1) and (2) should run in the same time. If you want to see the execution plan for a query then just add the command EXPLAIN before the SELECT: EXPLAIN SELECT * from table1 WHERE A='DATA_A' AND B='DATA_B'; If you build you database using bulk loading then sometimes the query optimizer will choose not to use indexes because it doesn't know the real size of the table. It is always good practice to run the command "OPTIMIZE TABLE table1;" (if you are using MySQL) right after a bulk load. Petey
I would also add some more indexes. Make some multiple unique fields bind to give a somewhat license plate or mac address if you will. SO fields 1+2+3 == Unique Index. This will allow for faster searching on full text.
Thank you very much for your suggestions, actually, Column A is a MD5 sum, so it's almost unique. Thanks.