New answers tagged

1

Yes and no. An "array" in a programming language does some trivial address arithmetic to quickly locate the Nth entry in the array. In MySQL, most indexes are built as B+Trees. (See Wikipedia) This structure is more complex than an array, but still the best available. WHERE id=2 requires drilling down a "tree" of nodes to locate the item with "2" in the ...


1

You don't need a separate index for the first column of your primary key (id_of_table_x in your example) because Postgres will happily use that index in every situation where it would use the single-column index on the first column. If you very often delete rows from the referenced table, then adding another index on the second column helps validating the ...


7

Also "reducing fragmentation" is not per se a performance goal. On many (most?) modern storage platforms there is little difference between sequential and random IO, which is a major historical reason for defragmenting. I've worked on systems where the difference in throughput between sequential and random IO was 10x or more. As SQL Server attempts to ...


2

danblack's post answers the main question about the best index strategy for you queries. However, I would add a sometimes forgotten optimization of the index strategy, which is implemented in most recent versions of RDBMs (MySQL, MariaDB, PostgreSQL...): covering indexes Definition of covering index: (from MySQL documentation) An index that includes all ...


1

MySQL reference (country=X) elements should be before range (attraction like 'asd%') in an index. To cover query4 a single attraction index is needed. An index that covers query1 and query2 would be (country, attraction). An ideal query3 index would be (country, state, city, attraction), however if the country, attraction query sufficiently narrows the ...


Top 50 recent answers are included