Using PHP to store and call data in RAM

[gotornot]

Hi All

I have a site that holds over 900k of products. I am searching on my db and its taking 10 seconds to search products and bring back results. I wondered if i stored the data in the ram the results would be instantanious.

 

Does anyone have any experience or could point me in the right direction of how to do this.

 

In an ideal world i would like the rsults to come back in aroun1/2 seconds.

 

Thanks for the help and advice in advance

[joecooper]

How much data are you displaying? My DB is around 100,000 items big, and searching 100~ results takes me less than 0.1 second.

[gotornot]

well i am checking all the data every query as i have built a search engine. Currently there are 955000 rows and using pagination to sow 10  ordered by price

[gotornot]

you can see by going to nowcodes.com

[Psycho]

Most likely, your problem is due to the database design and the queries used. Have you indexed the relevant fields? Perhaps you can post the query as well.

 

EDIT: And I really, really hope you are not running any queries in loops.

Edited April 1, 2013 by Psycho

[gizmola]

Hi All

I have a site that holds over 900k of products. I am searching on my db and its taking 10 seconds to search products and bring back results. I wondered if i stored the data in the ram the results would be instantanious.

 

Does anyone have any experience or could point me in the right direction of how to do this.

 

In an ideal world i would like the rsults to come back in aroun1/2 seconds.

 

Thanks for the help and advice in advance

 

 

 

It sounds like a databse design/query issue.  Without some information about your design, and the queries you are doing, there is no way to help you but I can offer a few ideas to get you started:

 

-You didn't state what database you are using, but I'm going to assume it's mysql. Step 1 should be to run EXPLAIN EXTENDED on your queries from the mysql command line tool (or phpMyAdmin).

This will help you understand what your query is actually doing (and it's most likely tablescanning and looking at your whole table for every single query)

 

-If your tables are using myisam storage engine move to innodb. Innodb has a true data cache component, so if the server has sufficient memory, it can keep much if not all the dataset in memory all the time.

 

-If you have LIKE queries which include wildcard-something-wildcard, those are never going to use an index.  For example, I often see people doing this:

SELECT * FROM TABLE WHERE somecolumn LIKE "%criteria%";

Those types of queries will NEVER be performant, because a btree index can not be used.

 

Full text based searches really need to be done with a fulltext search system. Mysql does have a fulltext search index type, that might get you what you need, but most large sites use solr or sphinx, or if on aws, elastic search.

 

Most sites use some sort of caching system to store the results of queries in memory. Memcache and Redis are two of the most popular solutions, and there is also the option of using APC for a single server that has plenty of available RAM.

 

In all cases, you really need to understand the resources available to you, as far as server os, RAM, db configuration etc.

[jazzman1]

I wondered if i stored the data in the ram the results........

By the way, do you have any idea what RAM on the server is?

[gotornot]

Hi

 

this is the query

SELECT p.*, a.payout_level, a.buttonimage, a.smalldesc,a.companyname, v.* FROM products p left join advert a on p.advertId = a.id AND a.network='4' left join (  Select min(id), merchantid, code, value from vouchers group by merchantid ) as v on a.id = v.merchantid  WHERE p.prod_name LIKE '%glass%'   And  p.cat like 'Glassware'  order by p.fee asc LIMIT 0, 6

 

All the tables have been optimised for indexing

[jazzman1]

Could you explain this query ?

 

Example:

 

$query = "EXPLAIN SELECT p.*, a.payout_level, a.buttonimage, a.smalldesc,a.companyname, v.* FROM products p 
    left join advert a on p.advertId = a.id AND a.network='4' 
    left join ( Select min(id), merchantid, code, value from vouchers group by merchantid ) as v on a.id = v.merchantid 
    WHERE p.prod_name LIKE '%glass%'   And  p.cat like 'Glassware'  order by p.fee asc LIMIT 0, 6";

[Psycho]

1. Do you really need LEFT JOINS? They are less efficient than a normal INNER JOIN.

 

2. Instead of using LIKE here, use an equal comparison insead

 

  And p.cat like 'Glassware'

 

3. No need to use a sub-query, just JOIN on the other tables and use group BY on the results