how can I create index for this SQL query?
-
table: CREATE TABLE `deal` ( `id` int(11) NOT NULL default '0', `site` int(11) NOT NULL default '0', `time` bigint(13) NOT NULL default '0', PRIMARY KEY (`id`), KEY `site` (`site`), KEY `time` (`time`,`site`) ) TYPE=MyISAM sql query: select * from `deal` where time>0 && site=8 I create index:time for this query, but why this query always using index: site? explain select * from `deal` where time>0 && site=8 output: table type possible_keys key key_len ref rows Extra deal ref site,time site 4 const 1 Using where
-
Answer:
You need to create composite index site + time (yes, order matters). So delete both indexes site and time now and create 2: KEY site (site, time) KEY time (time)
Zenofo at Stack Overflow Visit the source
Related Q & A:
- How can I create my own hosted svn server?Best solution by stackoverflow.com
- How can i create gift card in magento community edition?Best solution by Magento
- How can I get the count in SQL?Best solution by Stack Overflow
- How can I print index cards?Best solution by Yahoo! Answers
- How Can I Create an XML to Create a Menu?Best solution by Drupal Answers
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.