PHP PDO Huge database query speed optimization

Question

I'm building a website geekwiz.com. Test to load it, LONG.

I'm new to php and just changing from mysql_query to PDO. I have to do multiple queries in order to display the products on the first page and the table has over 1.5 million records.

How can I make this much faster?

Here is the code I've created:

#connection
$DBH = new PDO("mysql:host=$host;dbname=$dbname", $user, $pass);
$DBH->setAttribute(PDO::ATTR_EMULATE_PREPARES, 0);

Seperate file

#fetch data
require_once('db_config.php');
$STH = $DBH->query("
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('notebook pc - other') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Desktop PC') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Hard Drives - External') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Camcorders - Analog/Digital') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Monitors - LCD Flat Panel') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Software - PC Games') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('third party accessories') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE advertisercategory LIKE ('LED TV') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 3;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Motherboards - %') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE advertisercategory LIKE ('LED TV') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Hard Drives - External') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('CPU Cooling') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('CPU Thermal Paste / Grease') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Keyboards') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (USB Flash Drive)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (Desktop Memory)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Memory (Notebook Memory)') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Mouse') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Printer - Inkjet Printers') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Projectors') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;
SELECT name, buyurl, imageurl, price, manufacturer, manufacturerid FROM catalog WHERE MATCH advertisercategory AGAINST ('Processors - Desktops') AND special = 'YES' AND instock = 'YES' AND standardshippingcost LIKE '0' ORDER BY lastupdated DESC limit 9;

");

$STH->setFetchMode(PDO::FETCH_OBJ);

while ($row = $STH->fetch()) {
echo "<ul>";
echo "<li>" .$row->name ."</li>";
echo "<li>" .$row->buyurl ."</li>";
echo "<li>" .$row->imageurl ."</li>";
echo "<li>" .$row->price ."</li>";
echo "<li>" .$row->manufacturer ."</li>";
echo "<li>" .$row->manufacturerid ."</li>";
echo "</ul>";
}

Answer 1

A few issues, and you probably won't like what I have to say.

Sorting by text/varchar is hellaciously slow, and updating will take forever. What you should do instead is convert all of that text to integers. So advertisercategory should not have actual text in it. Instead, it should be a fixed length INT that links to a table where the actual text is stored. The same goes for special and instock. standardshippingcost looks like a varchar since it's in single quotes, and if it is as it looks, that really should not be the case. It should be numeric if it is numeric. I'm not too sure of your schema, so standardshippingcost may need to be in its' own table linked to catalog by FK. lastupdated should be an INT in unix time.

The reason why your data should be INT is because 1) it takes less space (thus less memory when sorting) than notebook pc - other and 2) updating will be faster (I'm assuming you're using InnoDB).

So, when you sort, SELECT all of your ids (which should be an auto-increment PRIMARY), advertisercategory, instock, standardshippingcost, standardshippingcost, and lastupdated into a PHP array. Find the most efficient way to get what you want (you'll find shortcuts as you go). Finally, SELECT the specific data by PRIMARY key.

I will go backwards to explain. The reason why this methodology will yield the highest performance is because if you SELECT off the PRIMARY on an InnoDB table without any other indexes, it doesn't matter how big your table is, it will return "immediately". Using huge, esoteric queries is slow, as you've experienced. This is how you should actually retrieve the data.

PHP is faster than MySQL with logic. It's what it's for. There are many Qs on stack showing this. That's why you want to sort on PHP. Plus, if you see some sort of statistical or other kind of pattern in your data, you can actually write better algos to make your sorting run even faster.

Aside from the unix date, your other columns shouldn't be too long. advertisercategory could get a little big, but it won't be anything compared to notebook pc - other which is already a length of 19. I see about 30 advertisercategorys in your Q, so that's only an INT LENGTH 2 because that's 100 potential values. This will save space and memory when sorting.

Also, using fixed length ints will be faster when updating because a clustered table will always keep itself tight and defragmented. If you UPDATE a row in the middle of a clustered InnoDB from 'Memory (USB Flash Drive)' to 'Projectors', you just caused your database to defrag your whole database to remove that gap you just made. If you change from one 1 to 10 in a fixed length int column, you did no such thing because both take the same amount of space, creating no gaps, necessitating no defrags of the cluster.

You'll be alarmed at the massive performance increase when you do it this way, but yes, this will involve more code. So what's your priority, performance or dev time?

---

Whoop! Forgot unix time explanation. You want to use this because you can store it in INT for seconds and DECIMAL for microseconds. Doing it this way allows for easiest manipulation in PHP since php is in unix.

Answer 2

Write a php script called saveCacheData.php that performs that group sql querys, which also dumps the data to a file called something like cacheData.php.

On the main page, replace the sql querys with require_once('cacheData.php').

Now, when ever the data in the mysql table catalog is updated, make sure you also perform file_get_contents('saveCacheData.php') (or perform some kind of cron job that will cache the data for you).

BTW, I do think your DB design could be a lot better, but caching DB data to disk is often a good idea as well.

Answer 3

Well, for starter, PDO has absolutely nothing to do with huge databases speed optimizations.
To make it a bit clearer, imagine you're asking a question similar to "How to drive my Ford Escort to make it from Brooklyn, NY to Tampa, FL". The car make is just irrelevant here. Same goes for the DB driver.

Now to your database.
You have a lot work to do.

First, it is absolutely no way to make categories using Mysql FULLTEXT. It should be just a numeric index, linked to the table with categories structure.

So, the query become like this one

SELECT * FROM catalog WHERE category=1 AND special = 'YES' AND instock = 'YES' 
AND standardshippingcost=0 ORDER BY lastupdated DESC limit 3;

And, if properly indexed, it can be pretty fast. Say, if number of special offers is sanely limited, an index on this field will make it fast, no matter how many records in the database.

However, the overall database architecture may vary. As well as there can be other way for the optimizations. But we don't know much of your project to be certain and, honestly, it is not the matter of simple Q&A format.