php - Speed up query (~1 million rows) on a complex table -

first of time reading this.

i'm working on php web application deals following data, of right there noticeable time when trying retrieve data, when drilling down options. primary options right vehicletype, make, model, year, internetprice , mileage. there more columns being used. other columns list being used displaying data throughout page, 10 records displayed @ 1 instance.

table structure:

 create table `vehicles` (   `id` int(12) not null auto_increment,   `dealerid` int(6) default null,   `vin` varchar(17) default null,   `stocknumber` varchar(10) default null,   `status` varchar(1) default null,   `vehicletype` int(1) default null,   `year` int(4) default null,   `make` varchar(13) default null,   `model` varchar(24) default null,   `trim` varchar(35) default null,   `body` varchar(25) default null,   `vehicleclass` varchar(50) default null,   `vehiclecategory` varchar(6) default null,   `mileage` int(6) default null,   `transmission` varchar(24) default null,   `enginedisplacement` varchar(7) default null,   `enginesize` varchar(15) default null,   `induction` varchar(25) default null,   `drivetrain` varchar(3) default null,   `fueltype` varchar(9) default null,   `fueleconomycity` int(2) default null,   `fueleconomyhighway` int(2) default null,   `fueleconomycombined` int(1) default null,   `doors` int(1) default null,   `oemcolorcodeexterior` varchar(10) default null,   `oemcolorcodeinterior` varchar(10) default null,   `oemcolornameexterior` varchar(49) default null,   `oemcolornameinterior` varchar(10) default null,   `genericcolorexterior` varchar(35) default null,   `genericcolorinterior` varchar(38) default null,   `internetprice` int(6) default null,   `comparisonprice` int(6) default null,   `wholesaleprice` varchar(10) default null,   `msrp` varchar(10) default null,   `internetspecial` varchar(1) default null,   `oemmodelcode` varchar(12) default null,   `haswarranty` varchar(1) default null,   `certificationwarranty` int(3) default null,   `warrantymonth` int(1) default null,   `warrantymiles` int(1) default null,   `certificationnumber` varchar(7) default null,   `servicecontract` varchar(1) default null,   `inservicedate` varchar(19) default null,   `certificationdate` varchar(19) default null,   `datemanufactured` varchar(19) default null,   `datecreated` varchar(19) default null,   `dateupdated` varchar(19) default null,   `dateremoved` varchar(19) default null,   `datephotosupdated` varchar(19) default null,   `photos` int(2) default null,   `supersizephotos` int(2) default null,   `addendumdetails` varchar(10) default null,   `departmentcomments` varchar(239) default null,   `vehiclecomments` varchar(1987) default null,   `options` varchar(2264) default null,   `purchasepayment` decimal(5,2) default null,   `purchasedownpayment` decimal(6,2) default null,   `purchaseterm` int(2) default null,   `purchasedisclosure` varchar(10) default null,   `purchaserate` decimal(3,2) default null,   `leasepayment` decimal(2,2) default null,   `leasedownpayment` decimal(2,2) default null,   `leaseterm` int(1) default null,   `leasedisclosure` varchar(10) default null,   `leaserate` decimal(2,2) default null,   `leaseresidual` decimal(2,2) default null,   `reserved1` varchar(10) default null,   `reserved2` varchar(10) default null,   `reserved3` varchar(10) default null,   `reserved4` varchar(10) default null,   `reserved5` varchar(10) default null,   `reserved6` varchar(10) default null,   `sitecert` int(11) default null,   primary key (`id`),   key `vin` (`vin`),   key `make` (`make`),   key `stocknumber` (`stocknumber`),   key `model` (`model`),   key `trim` (`trim`),   key `body` (`body`),   key `vehicleclass` (`vehicleclass`),   key `transmission` (`transmission`),   key `dealerid` (`dealerid`) ) engine=myisam  default charset=utf8 auto_increment=46527428 ;

and example query:

select * vehicles make = 'audi'     , vehicletype = '0'     , model = 'a4'     , internetprice between 0 , 999999     , year between 1983 , 2015     , mileage > 0     , dealerid in ('aa'         ,'156553'         ,'229602'         ,'160483'         ,'2953'         ,'161712'         ,'228576'         ,'228921'         ,'7590'         ,'20813'         ,'158105'         ,'160286'         ,'164479'         ,'164487'         ,'182543'         ,'158860'         ,'186479'         ,'227170'         ,'226327'         ) order sitecert desc     ,internetprice desc  limit 0, 10 

would have advice optimize query or table? of course there other code throughout page i've timed down query causing delay.

thanks help!

update:

mysql explain:

id select_type table   type    possible_keys   key key_len ref rows    1   simple  vehicles    ref make,model,dealerid model   75  const   1675    using where; using filesort

year updated int

do have use asterisk on select statement? try calling 1 or 2 fields.

also year field should integer. between operator won't work using text.