Greetings,
I’m having a tough time making some of my larger queries that sometimes takes 90 seconds to run on a table with only 60,000 rows. Here is my table with indexes:
CREATE TABLE `items` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`seller` varchar(30) NOT NULL,
`listtype` tinyint(1) unsigned NOT NULL,
`live` tinyint(1) unsigned NOT NULL,
`currentprice` decimal(10,2) unsigned NOT NULL,
`buyprice` decimal(10,2) unsigned NOT NULL,
`created` datetime NOT NULL,
`endtime` datetime NOT NULL,
`storelevel1` smallint(5) unsigned NOT NULL,
`storelevel2` smallint(5) unsigned NOT NULL,
`storelevel3` smallint(5) unsigned NOT NULL,
`storelevel4` smallint(5) unsigned NOT NULL,
`storelevel5` smallint(5) unsigned NOT NULL,
`storelevel6` smallint(5) unsigned NOT NULL,
PRIMARY KEY (`id`),
KEY `seller` (`seller`),
KEY `listtype` (`listtype`),
KEY `live` (`live`),
KEY `currentprice` (`currentprice`),
KEY `buyprice` (`buyprice`),
KEY `created` (`created`),
KEY `endtime` (`endtime`),
KEY `storelevel1` (`storelevel1`),
KEY `storelevel2` (`storelevel2`),
KEY `storelevel3` (`storelevel3`),
KEY `storelevel4` (`storelevel4`),
KEY `storelevel5` (`storelevel5`),
KEY `storelevel6` (`storelevel6`)
) ENGINE=MyISAM AUTO_INCREMENT=67979 DEFAULT CHARSET=utf8
Here is my general query:
SELECT * FROM items WHERE seller = 'johnson' AND storelevel1 = 2653 AND listtype <= 2 AND live = 1 ORDER BY created DESC LIMIT 25
Here is information about my columns - all of these columns have indexes on them:
“seller” is the username of the seller
“storelevel1” is a category id number - store category that item is located in.
“listype” is a number between 1 and 4 - signifying an auction-only or fixed-priced-only listing for 1 and 2 (50% of items are listtype = 1, 10% are listtype = 2 and 40% are something else)
“live” is a number between 0 and 4 - live = 1 means item is live, live = 2 means it’s sold out, etc (90% of items are listed as live = 1)
ORDER BY can be sorted by “created” (newest items), “endtime” (ending soonest) or “currentprice” ASC OR DESC" to order items by prices.
How do I optimize this query for scalability and so it can load in milliseconds (if possible)? Am I supposed to merge these indexes somehow? Use sub-queries or joins? I’m lost here, please help!
Thanks
Kind regards