Stack Overflow
Stack Overflow

Questions tagged [composite-index]

Composite index: An index that contains more than one column.

A composite index is useful when there are multiple fields in the WHERE clause of an SQL statement and all fields combined will give significantly less rows than the first WHERE clause field.

81 questions
0
votes
2 answers

create index for select query with multiple OR conditions and AND conditions

I have a query like the one shown below: select count(test.id) from table1 inner join table2 on table1.id = table2.id where (table2.email = 'test@gmail.com' OR (table2.phone1 IS NOT NULL AND table2.phone1 in ('123456')) OR…
Shikhar Subedi
  • 606
  • 1
  • 9
  • 26
0
votes
3 answers

Does wildcard in left-most column of composite index mean remaining columns in index aren't used in index lookup (MySQL)?

Imagine you have a primary composite index of last_name,first_name. Then you performed a search of WHERE first_name LIKE 'joh%' AND last_name LIKE 'smi%'. Does the wildcard used in the last_name condition mean that the first_name condition will not…
prograhammer
  • 20,132
  • 13
  • 91
  • 118
0
votes
1 answer

Implementing a composite index on a table with rare updates/inserts

I plan a database schema to store Amazon product attributes and extra marketplace-specific values (such as title, artist, weight, etc.) So far there is a Products table with GTIN varchar(13) (PK) column. In my particular case GTIN may be an…
Interface Unknown
  • 713
  • 10
  • 26
0
votes
0 answers

SQL Server Multiple Index Solution?

Let's assume we have a table with name Tournament and columns of this table are Id, Name, GameId, TypeId, cityId, Status, Format. Except Id and Name columns I want to search this table with all of the combinations of these columns, I mean I can…
spartacus
  • 544
  • 2
  • 8
  • 16
0
votes
2 answers

MySql complicated join performance issue

Please consider the following query SELECT * FROM PC_SMS_OUTBOUND_MESSAGE AS OM JOIN MM_TEXTOUT_SERVICE AS TOS ON TOS.TEXTOUT_SERVICE_ID = OM.SERVICE_ID JOIN PC_SERVICE_NUMBER AS SN ON OM.TO_SERVICE_NUMBER_ID = SN.SERVICE_NUMBER_ID JOIN…
dre
  • 1,027
  • 1
  • 11
  • 31
0
votes
1 answer

mysql composite index thoery

my sql is:select id,name,city,type from company where city=? and type=?. so I created two indexes: one is on city field and another is on type field.So my question is:Is it necessary that I create a composite index on city and type?if need,why?how…
Jack
  • 5,540
  • 13
  • 65
  • 113
1 2 3 4 5
6