Jump to content


Member Since 06 May 2003
Offline Last Active Nov 22 2014 02:39 PM

#1496425 What's the fastest way to search INNODB tables?

Posted by gizmola on 12 November 2014 - 02:33 PM

MySQL is an odd database because it has pluggable engines that can work entirely differently.  MyISAM is the base engine and of course InnoDB was a pluggable engine that became very popular.  


Just about all DB's have some form of caching for queries.  In the case of MyISAM this extends to the parsing of the queries themselves but not the actual data.  With the buffer_pool in innodb -- the actual data retrieved is cached.  So, in terms of what you were asking, when you do a LIKE '%...' query, the table will have to be scanned starting at row 1 and continuing to the last row in order to return a result.  With Innodb and a large enough buffer pool, essentially the data will already be in memory and the tablescan process will be much faster than it would be reading the data from disk.


Unfortunately, with shared hosting, you are not in control of those parameters as you're sharing a mysql server your hosting company provides, either on your server or some secondary server they host.


In the shared host scenario, I would advise you to implement Barand's suggestion.  

#1495852 What's the fastest way to search INNODB tables?

Posted by gizmola on 05 November 2014 - 03:00 PM

There are other reasons to utilize innodb besides the ones mentioned.  I highly recommend you stay with it.  


You could use Barand's suggestion -- have a denormalized search table, where you simply replicate the values into a small myisam table for the benefit of the search.   The coding and interruption required would be minimal. You'd simply need to write the replication script and schedule it in cron.  


To your original question one of the benefits of innodb is that it has a true data cache.  It's called the "buffer pool".  If you have sufficient resources on your server, you could increase the size of the buffer pool to insure that data is coming from cache.  It is often possible with a small fairly stable database like the one you describe where you are almost entirely "READ/SELECT" based, to have a pool where the data and indexes for the entire table will be in the buffer pool.  


At that point a SELECT '%...' will be far less disruptive than normally because it will be coming from memory.  An 8-10k row table is tiny in the database world.


You would have to invest some time trying to figure out the size of your overall database and the tables in particular, and you'd need to understand your overall memory usage to determine if you could allocate more RAM to mysql that you currently do.


You can start by looking at http://dev.mysql.com...uffer-pool.html


The innodb_buffer_pool_size and innodb_buffer_pool_instances are the only params you really need to understand and possibly change.   Whatever you do, these are good params to understand.  Although it might be a stretch if you are a novice sysadmin, the free tool, innotop is fantastic for making it easy to monitor the effectiveness of your caching and figuring out your cache hit ratio.  I would think based on your description that you should be aiming for close to 100% cache hit with your innodb tables.

#1493606 Struggling To Get Vanity URL's To Work

Posted by gizmola on 15 October 2014 - 10:40 AM

Make sure all the static resources (css, javascript, images etc) are using a fully qualified url, rather than a relative path and that will go away.

#1488073 Got great help here, but having trouble implementing?

Posted by gizmola on 17 August 2014 - 04:57 PM

I just flat out love you guys.  :-*




I just cannot thank you enough.  This is the kindest forum, to date, I've sought any kind of help from. 


Glad you got things working, and the words of thanks for the members who tirelessly help people out, are much appreciated, and rarely acknowledged.

#1487922 Got great help here, but having trouble implementing?

Posted by gizmola on 15 August 2014 - 11:03 PM

Hi Izzy.

I don't want to sound like a wet blanket, especially since this isn't actually addressing your question, but at this point we really have to insist that EVERYONE needs to convert their mysql_ code to either mysqli_ or PDO/Mysql. The entire mysql_ library is deprecated and will be removed entirely from php in the near future.

There is no point in us helping you debug code that is already obsolete when you write it. You should be using either mysqli or PDO, and with bind variables it is a game changer in terms of escaping (you don't need to) and the elimination of SQL injection exploits.

Also FWIW, I don't see anyplace where you are doing a SQL INSERT or implementing a form, so I'm not sure what you mean by "nothing added".

#1485376 Invalid parameter number: number of bound variables does not match number of...

Posted by gizmola on 15 July 2014 - 04:14 PM

More mysql syntax nonsense. Even if it's valid let's pretend it isn't and just use the standard syntax that all other RDBMS's use. ;)

#1482881 Installing PHP on iMac

Posted by gizmola on 18 June 2014 - 09:59 PM

This pretty much covers the way I've upgraded php and other components of a MAMP stack under osx  -> https://trac.macport...wiki/howto/MAMP


As an alternative, let me suggest investigating a few vagrants.  Pretty much any lamp or nginx based distro of your choosing could be located, or there are some pre-packaged vagrants you could try, that popped up in google:






Vagrant is a great way to develop without having to mess with your host OS.  The only issue I have had in the past is that sometimes composer can be hinkey under virtual box, but I'd still recommend looking into it.

#1478489 select distinct variable and another variable

Posted by gizmola on 06 May 2014 - 04:04 PM

All I can guess is that you want a list of distinct area/location combinations?

SELECT DISTINCT area,location FROM property
You can also accomplish this with GROUP BY

SELECT area, location FROM property
GROUP BY area, location

#1478480 PHP Send Mail

Posted by gizmola on 06 May 2014 - 03:32 PM

I don't plan to beat a dead horse. You state you have code working, but what you actually are doing is sending 2 separate emails.

Here's some info for you:

Calling the mail function.

Calling the mail function, but SUPPRESSING errors (because the @ was used at the front of the function call name). Now if mail was to return an error, you will never see it.

Your code, sending 2 separate emails:

@mail($email_to, $email_subject, $email_message, $headers );
@mail($email_from, $email_subject, $email_message, $headers );
People advised you that all you really needed to do was provide a string that contained a comma separated list ("email1, email2").

What you should have done:

// send one email to both recipient and sender.

mail($email_to . ',' . $email_from,  $email_subject, $email_message, $headers);
Of course the best way to do this would probably be to send yourself a bcc: copy instead.

I also don't know if you understand this or not, but this form is basically an open door allowing anyone to spam anyone else using your site as the spam delivery mechanism. Writing a bot to take a database of people would be trivial. This is just an open invitation to getting yourself exploited for spam delivery, but something tells me your email may already be seen as spam only you just haven't figured that out yet. Sending email programmatically from a server requires quite a bit of knowledge about DNS, MTA's, SPF, DKIM and other things you probably aren't aware of.

#1478256 "Roster" Script

Posted by gizmola on 05 May 2014 - 12:03 PM

You might have more luck pinpointing specific classes/functions etc. that you think might have an issue or could be improved.

Speaking generally, it would be easier to maintain if you put the presentation code into "template" files that you include, or use a template engine for.

I'm not going to suggest any of the sophisticated compilation template engines like smarty or twig, due to the fact that you are integrating with bulletin, but take a look at these:

Blog post that shows a simple effective way could pull this off: http://chadminick.co...ate-engine.html

Some simple template classes that can be used with a simple include of their template class:


You might also look at breaking up a lot of the logic sections into functions, named appropriately to represent the specific purpose they fulfill.  That way if you decide to move things around or add or remove sections, it will be much easier for someone to understand the code and make changes.

#1475800 Banner ad Rotation

Posted by gizmola on 11 April 2014 - 02:39 PM

To make it database driven you simply need to create a banner table.

You'll want to have a structure like this:
id int (primary key)
url varchar(512)
image varchar(128)
active tinyint
As for rotation, so long as your banner ad table doesn't get larger than a thousand rows, there isn't much of a concern, however, I would highly recommend reading this blog post: http://jan.kneschke..../order-by-rand/

With that said, the simplest solution for you, that will certainly work fine for quite a while:
If you expect rotation over time without reloads, then cyberRobot's point should be taken into account. Often people will utilize an iframe and place the banner in the iframe, which can then have a simple meta refresh tag in the header of the code.

Ajax polling offers a more sophisticated option to cyberRobot's point, or you could alternatively fetch a number of ads initially and use a javascript timer to rotate them.

#1473739 New Moderator: mac_gyver

Posted by gizmola on 24 March 2014 - 03:00 PM

Congrats mac, agree with Phillip - you have really helped to keep phpf running, and your efforts are appreciated.

#1471661 Newbie Questions (EASY)

Posted by gizmola on 06 March 2014 - 12:47 PM

What have you tried?

I suggested that you:

Put the mail chimp code into a file named mailchimp.inc.php

There should NOT be a <?php at the top. Simply put that code exactly as is, in the file.

This file should be in the same directory as your template.

In the template add: include('mailchimp.inc.php');

#1471109 Hiring programmers, are there copyright laws?

Posted by gizmola on 28 February 2014 - 07:56 PM

right.. but the problem is with proving and enforcing it.

Agree 100%.

I've had these types of conversations with people probably 50x over the years, I have NEVER seen a case that didn't involve a patent.

Every other business person I talk to, who has an idea, is convinced their idea is amazing and original and worth a billion dollars.

#1468366 What's the use of abstract classes/methods?

Posted by gizmola on 10 February 2014 - 01:02 AM

There are times when you want to create a number of subclasses that all inherit from the same base class, but insure that the base class can not be instantiated itself.  A simple example would be a zoo simulation where you decide that you want to have a factory class that can "make" new animals.  When your application needs to create a new lion, for example, you don't want to have to write the same code over and over, when you can instead have completely generic code that creates animals of any type.


Once an animal object is created, you will probably need to call some standard methods, and again if you based all your specific animal classes off an abstract animal class, your non-abstract methods can be called.  Imagine you need some code that Will store an animal name, birthdate, gender and location in a table. You may decide that for each animal class you'll have a seperate table  with the same name as the class.  So in this example, you will have a lion table in the database.


In your abstract animal class you might have:

//Animal class.
public function saveNew() {
    $table = get_class($this);
    $this->db->$table->insert(array('name' => $this->name, 'birthdate' => $this->birthdate, 'location' => $this->location, 'gender' => $this->gender));

Needless to say, you will not have an 'animal' table, so making the baseclass abstract provides some protection against issues with saveNew(). 


It also provides a standard set of methods the factory class can depend on, so it can feature code like:

// Animal Factory class
public function makeAnimal($type, $location) {
    $animal = new $type($location);

This code can be depended upon to work for any animal class that inherits from 'Animal'.  setName() is an example where you might want to have an abstract method, assuming that the rules for animal names would be different for each different type of animal.  The factory class will call setName(), but it is up to you to actually write function setName() when you create each individual class. 


SetGender()_ is another example, where you might lookup the relative ratio of male to female animals in a table where each row is named by class.  While you would have a row for lion, elephant and crocodile, you'll have no row for animal.


When trying to really dig into these ideas, you'll quickly find yourself gravitating towards "Object oriented design patterns".  The books on this subject will provide specific examples that should help you understand why the oop features in php were implemented in ways that the manual does not. 

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime